Up a script for that we're going to loop through all our worksheets here apply the proper formatting and then add a cool button so it's super easy for me and my colleagues to run this.
let me quickly show you our end result so this is what i want to achieve i want to have a button to apply formatting to each single sheet here and if i add more.
Sheets it should automatically take them into account and i've also added a button to clear all formats now there is something important here that will surprise you this file.
Is excel on desktop it's an xlsx file so not a macro enables file and yet i have these two buttons with macros behind them but these are not the standard macros they're office scripts if i click.
And when i go to the other sheets my formatting is automatically applied so i get a proper date here i get the pound sign for the sales value and i've added a border underneath the first row i have.
Another script here to clear all the formats now how do we know that this is a script well if we right mouse click this we get the option here to view the script and.
When we do the code editor pops up and we can see the name of the script hint hint we have the ability to edit the script and run the script from here so if i click on.
Run it's going to run this but i don't need to bother and come to that view i can automatically run these by clicking on these buttons this clear the formatting from each single sheet there.
Is something important to also point out here that even though i'm in desktop this file is on sharepoint so you'll need to have your files on sharepoint or on onedrive to be able to access the.
Posts Related:
Scripts if i save this on my desktop and
Open this from there then i'm not going to have clickable buttons these are just going to be images now wouldn't it be fun to create this.From scratch let's set it up together okay so i've opened a previous version of this file without the buttons on excel on the web because it's on excel in the web that we get to use the.
Automate tab for now let's jump here and we get to see all our scripts here we get to create a new script and record our actions so i have a separate video explaining this in more detail check it.
Out if you're interested to find out more in this case we want to record our actions and add formatting to this so notice i'm just on the first tab i'm going to do the actions for the first.
Tab and then we're going to add a button to this and then we're going to update our code to move through all of these sheets so let's start by recording the actions we get our pop-up here every.
Move we make on the grid is going to get recorded so first of all i want to highlight that date column and i want to format this as a date so let's go to home formatting here.
I don't want any of these two dates i'm going to go to more number formats date and select this one with the year month and a day click on ok now for sales value.
Let's go it's going to be accounting but again let's go to more number formats under accounting change the symbol to british pounds.
That's what i want to use in this report and i want to have zero decimal places click on ok it would also be nice to have a bottom border here.
And to make the first row bold so all
The changes have been applied to the first sheet before i do anything else here we're going to stop the script now this script is being saved on my.Onedrive so you can save scripts on your onedrive or on the sharepoint folder we're going to give this a name let's just click here and call this update formatting.
Okay so here we have the ability to edit the script and on the side here we get more options when you click on this you get the ability to add a button now i want to.
Add a button actually on another page here so let's jump to the february tab just to make sure that our script works let's add a button here so i'm going to go to more options here and add that.
Button right here it adds this default score button with the name of my script i have the ability to update this i can change the fill color let's go with this color i can edit the.
Text as well i can right mouse click and edit the text i can expand this and treat this like a shape in fact you can change the shape if you want to by going to shape options here click on this drop.
Down and adjust it to something else so let's say i want this one and we're going to make that adjustment now let's run this on february data and see if everything works i'm just going.
To click on that script is running it was successful and we can see our formatting was applied obviously we don't want to drag this button and put it on every single sheet.
We just want to have it on the first page here and then apply it to all the sheets this means we need to update our scripts i just ran it because i clicked on here but i can edit it by right mouse.
Clicking here and going to view script you can also go to the automate tab and view your scripts from here but let's use this option here now let's go ahead and edit the script so what i want to.
Happen is instead of it going to that selected sheet here i want it to go through all the sheets and that just requires a minor tweak that's to change the statement here we created this.
Object called selected sheet equals workbook dot get active worksheet but we don't want the get active worksheet instead we want to get all the worksheets so if you just start typing.
For worksheets we can see get worksheets that's what we want i also want to update this selected sheet.
And just change that to ws or just something else that represents more than one sheet so this is going to be the worksheets collection now that i've defined my worksheets i need to loop.
Through each single member of that collection we're going to need f4 i'm going to open bracket let now here we need to define a member of that collection so whatever name you.
Want to choose here is fine i'm going to go with w so let w off ws so now all we need is the curly bracket here and we need to close that at the end here.
Okay so so far so good but notice that the office scripts interface here is smart enough to say that there is something being mentioned that doesn't really exist and that's because i.
Changed selected sheet to ws here where i defined a collection now when i'm looping through each member of the collection i need to replace this with that member so that's going to be w.
And not ws i want to replace all of them in one go so i'm going to select that first one right mouse click and just scroll carefully down.
Change all occurrences you can use the shortcut key ctrl f2 as well and change that to w okay so that's that.
We are all done i'm going to save the script and now notice that march april may don't have that formatting when i click on update.
Formatting now and descript once it should successfully update all my formatting in these sheets as well and it does okay so now you might be wondering how on earth do i know how to.
Update the script well i don't i just know how to read the documentation from microsoft so you can find all that information right here on docs.microsoft.com.
You have a lot of sections about office scripts now this is being updated quite a bit if you scroll down on scripting basics you're going to get to collections iterate over collections and.
This is basically what i did here if you want to learn office scripts this is a great place to get started now in a similar way i created the button to clear the formats i just cleared the.
Formatting on one sheet and then i used the same technique we used here to apply it to loop through all the sheets now before we wrap up let's open the same sheet on excel for desktop.
Okay so i have my workbook here that's the one i created this is me on excel in the website i can see that button this is clickable and if i apply it it should apply to every single sheet.
As well so i'm just going to go here and remove the formatting let's clear formats on this sheet and actually let's bring this over to the first sheet so i'm going to right.
Mouse click and cut this go here to the first tab and press ctrl v now on desktop this process works fine i can cut this object bring it here and.