3 ways to create nice looking Office Scripts Buttons | Excel Off The Grid

when office scripts were first released there was really only two ways that we could run those scripts either we had to go into the automate Tab and then select the script and then click run or we had to use power automate online but user said we want a better method why can't we have a nice button to click to create nice user interfaces and Microsoft gave us a button however it's quite an ugly looking button wouldn't it be nice if instead we could create buttons that look nice that were fun that people might actually want to click and that's what we're looking at in this video how we can create some better buttons for running our office scripts so if you're ready let's get started let's start by adding that default ugly office scripts button from the automate tab you can see that I have my scripts Gallery here and if I can see my script in there I can select it I can also go to all scripts and then I'll list all the scripts that we have I can see my script here but an example so I will select that and that opens up the code editor now there's two places I can add a button I can click on the more options section here we can see add button or I can go to edit the script and then we have the more options menu there and we can also add a button there I'm just going to select cell B2 on my worksheet because that's the place I want to add my button and then I'll click add button so that's inserted that into my worksheet now when I click this button all it's going to do is to insert the words button clicked into cell A1 of sheet1 so it's not the most exciting script in the world I admit but it's all about the buttons today rather than about the scripts so if I click this button you can see the script that's running so there we go it now says button click in cell A1 but let's face it this button is kind of ugly isn't it it's not a button that anyone would want to click so we're going to look at a few ways that we can make these buttons a bit more exciting a really useful technique is actually to be able to rename our buttons so if I rename this button to my default button press return so now I know that this shape is called my default button so if I want to edit that shape for any reason it means I can easily find it and easily refer to it okay now let's move on and look at how we can format this button in different ways the button that we created earlier is just a shape and that means that we can edit that shape as we can with any other shape inside Excel now here on the screen I've created five examples just to give you an idea of what kind of shapes or buttons we can create so example one we've changed the font we've got this darker green fill there's no line but there is a shadow on that button so that gives it a nice slightly raised effect example two is intended to look like those traditional VBA buttons what we've done is we've given this a bevel and given it that same light gray shade that we find with VBA buttons example three we've given our button this gradient fill so it goes from a light pink to a slightly darker pink it's also got a shadow we've used ink free as the font and also we've used some emojis to give this button some real Pizzazz so if you want to add an emoji you press the windows and then the period key and then I can just type the word star for example and that gives me the chance to select a star from that if I select that and copy it I can then add it into the text of my button so just paste that in there maybe I'll take away my unicorn there we go so that's how we can add emojis into these buttons so that we get this kind of graphical element in example four we've changed the font we've gone for this purpley color and we've also added a glow to the outside the other thing we've done is change this shape so to change the shape I selected it and then went to shape format I edited the shape change shape and then down here I selected this call out option all I do is drag this element here so it looked more like a speech bubble example five it just looks like a save icon well how do we do this well actually if I select that shape come to my fill options we can select picture or text fill I went to insert and then from the Icon section I searched for a save icon I selected that and then clicked insert and that changed the background of my button to this save icon then all I did was to remove any text any outline from that button and if I click any one of these so let's click example three it will run that script and it should say button click there in cell A1 so even with these five examples you can see there's lots of variety as to how we can create our buttons so we've seen that we can make buttons look almost however we like but all of those are manual processes to create the button in that way wouldn't it be great if we could create a default button style that we could then apply over and over again well we can do that with another script so let's find out how shall we and then right click on my button copy it and then just paste it down here then in my script Gallery I have a script called format button I'll select that and then click edit to view the code so here it runs on a shape called my shape so I'm just going to rename my button my shape and now when I run this script what it should do is change the fill color change the height and the width change the font change the line transparency so there isn't a line on the outside and then it should change the shape using this set geometric shape type and it should change it to a striped right arrow okay so let's see if this will work got my script I'll click run perfect you can see it's changed my button to this rather funky Arrow style that means that if we can create a script that formats the buttons how we want it takes away a lot of that manual work of having to reformat different buttons over and over again now let's suggest that actually what we want to do is to use this button but in a way that we can't blatantly have an object so as an example here I've used the image function and this is looking at the value in f17 so this is just the image from the Excel of the grid website so it uses that image function what if I want to run a script by somebody clicking on this cell with the image function if I place a button over the top of it it won't work because that button will be in the way of that image function well actually that's not entirely true I'm going to right click on the shape copy it then I'll select my cell with the image function and I'll paste my button over the top then I'll change this into a square shape and now I want to remove all the formatting that we have on this shape from the right click format shape and say I don't want a line it's currently got a shadow on it I'll remove that now for the fill I don't want to select no fill because if there's no fill it's not a clickable area instead I want to put the transparency up to 100 percent then on this left is my button text I'll delete that and now what I have is a transparent shape that is on top of my worksheet so anyone that hovers over that it looks like that they can click this image function so I click that it will run the button and then cell A1 it should say button clicked there we go so we've now created a button from that transparent shape well that's it that's how we can turn that ugly little scripts button into something that might actually create a nice user interface hopefully you enjoyed this video it was quite fun to make to play around with all these shapes and how we can make this work with office grips so don't forget to subscribe and like and I'll catch you next time

Channel: Excel Off The Grid

Views: 3,310

Keywords:

Id: Frx0SCAS3rA

Length: 9min 7sec (547 seconds)

Published: Thu Mar 02 2023