Collaborate on Billing Sheet in Excel for Procurement with Ease Using airSlate SignNow

Billing sheet in excel for Procurement

let's see how we can use Microsoft's newly released checkbox feature to create a select all option in a checklist this new checkbox feature was widely released in July of 2024 so if you got Office 365 this is located on the insert ribbon now let's see what we're going to build and then I'll show you all the steps here we have a list of pizza toppings that we want the patron to pick from and so we want the user to be able to just go in here and check check or check whichever toing they're interested in but what if they want all the toppings wouldn't it be nice if they could just hit select all and check every box or maybe if they don't want any toppings they could hit check all and turn them all off but we also want the flexibility to say I want most of the topping so I'll go ahead and select the whole list but then I'll just exempt the few that I don't want or clear the whole list and just pick the few that I do to do this will require just a little bit of VBA coding but I've already written all the code for you I just need to show you where to copy paste it so let's build this select option we'll Begin by adding the checkboxes to our list so here I have just typed in text let's highlight all of the cells directly to the left of those words because that's where we want to place our checkboxes so with those cells highlighted we'll go to insert and I'll click checkbox and that adds the checkboxes now while I'm here I'd like a deselected checkbox to be sort of a lighter gray in color and then when the user checks it I want it to turn green so we'll go up to home and we'll change the font color to sort of a medium to darker gray now to test the checkboxes we can click click click and of course they work as expected now I have a whole video I created on the implementation and use of these checkboxes and if you want to see that video just click the link in the video description or the card in the upper right but the select all checkbox is just by itself it's no different than any other checkbox so how do we get this to basically talk to the other checkboxes well this is where the little bit of VA code comes in I'm going to go up to view and turn my headings back on we can see that the select all checkboxes in B5 and all the other checkboxes go from B6 to B15 so let's just make a mental note of that B5 is the select all 6 through 15 are the other checkboxes we need to go into the Visual Basic editor one way to do that and what I think is one of the easier ways is to rightclick on any sheet Tab and select view code alt f11 will also activate this window in the upper left corner you're seeing a list of all of your open Excel files and so here we have our select all checkbox demo file we only have one sheet called select all test and we need to doubleclick that this will switch us to that Sheet's code sheet now from the supplied text file from the link in the video description we want to take this code here and copy paste it into sheet one's code sheet so I'll select all this text copy it contrl C go back to the code sheet for Sheet one and paste it now you don't have to understand VBA code but if you're curious about just what a little bit of this does this invokes what's called a worksheet change event it's an event driven macro in other words the macro automatically executes when a certain event occurs in this case we're monitoring cell B5 and if anything changes in B5 we want to execute the macro now remember B5 is our select all checkbox so if that changes do something well if B5 changes I will want to check to see if the value of B5 is a true in other words there's a check well if there is set all of the cells from B6 to B15 to True otherwise check to see if B5 was set to false in other words the check was removed well if that's the case then set all of the cells from B6 to B15 to false now one thing about this code that I discovered was that if you use the code without out this enable events equals false trigger it sends Excel into some type of loop that ends up cracking in Excel so we have to add this application enable events equals false before we perform this logic this way the endless loop won't trigger and crash Excel now once that code is done we need to reactivate enable events because enable events does other things besides just endless loop detection so the only thing you'll need to change in here for your world is the cell that holds the select all checkbox testing for that cell here and here and then which other cells do you want to change either when that checkbox is applied or when that checkbox is removed so we can close this window now let's give it a test I'll select all every one of those toppings gets checked if I deselect the select all option every one of the other checkboxes is cleared so we can select all but still go back and deselect specific items we don't want or clear the whole list and then just add the ones that we do now the last thing I'd like to do is have these check boxes turn green when a check is present but maintain it sort of medium dark gray when not checked well this is all going to be the results of conditional formatting since underneath these checkboxes are nothing but a series of true and false statements it's going to be very easy to create conditional formatting logic so I'll highlight all these checkbox cells go up to Home conditional formatting and I'm going to create a new rule I'll use a formula to create this Rule and this is going to be a very simple formula so again with B5 through B15 highlighted the rule will just be equals B5 that's it in other words is B5 a true well if the checkbox is checked it's a true we want some sort of new color if it's not checked it's a false we leave everything alone now by leaving B5 as a relative reference this rule will check B5 for true it will also check B6 7 8 9 all the way to 15 so the only thing we'll do is we'll go to format and we'll change our font color to Green hit okay hit okay and we can already see it in action we hit select all everything's green we clear select all we're all back to medium gray if I select all and then start deselecting those deselected items go back to medium gray clear the list start selecting individual items the color still works oh I almost forgot because you are putting code into this file you will have to save the file as an xlsm file an Excel spreadsheet with macros if you save it as an xlsx file that code will be discarded so that's how we can use excel's new checkbox feature to create a select all option it does require a little bit of VBA coding but the coding is very simple I really like EX new checkbox feature it's much better than the old forms control checkboxes so if you haven't tried it out give it a whirl and if you'd like to make it fancier like with a select all try this code don't forget to download the sample files from the link in the video description and let me know what you think about this in the comments thanks for watching and remember at bcti the learning never stops