Catherine is building a risk assessment tool in Excel. She needs to add 200 checkboxes to a worksheet, but this is a daunting task to do manually. Today, episode 1871 shows a way to do checkboxes without using Controls. A little Wingdings and 8 lines of VBA are all you need.
Transcript of the video:
MrExcel Podcast is sponsored by Easy-XL.
Learn Excel from MrExcel Podcast Episode 1871: 200 Checkboxes on a Worksheet.
Hey, everyone, welcome back to the MrExcel netcast, I’m Bill Jelen.
Today’s question is sent in by Catherine.
Catherine is designing a Risk Assessment.
She was really happy. Check this out - File options, and Customize the Ribbon, come over here to the Developer tab. There’s lots of good stuff on that Developer tab including under Insert.
There are Form Controls with Check Boxes and ActiveX Controls with the Check Boxes so you can draw a little check box in there and we will have it, you know, it’s a Risk Assessment.
There’s different things like, Do you smoke or something?
And we can right click and Format the Control and say that this is tied to cell B2.
Click OK and then here’s how it works.
So, you check the box, you get a True; uncheck the box, you get a False.
But wait, we have to do 200 of those, that is going to be a nightmare to go through instead of each one and so that is associated with the item to the right.
So, I have an alternate way to go.
Catherine’s question was, “Hey, how do I make 200 of these?” My suggestion is you’re not going to make any of them.
So here’ what I did.
This is a trick from Sylivia Huas.
She did it on the podcast earlier this year.
We have formulas here that =CHAR(ROW()).
And that gives us all of the characters so this is Character 2, this is Character 23, Character 44 and so on.
And I change the various columns, you’ll see on the Home tab here that this column is Webdings, this column is Wingdings, Wingdings 2, Wingdings 3 and then Calibri is so we can see what the actual letter is.
Every computer going back to Excel ’97, at least Windows, has these and I was heading down to the Rs because I remember here in the Wingdings 2 this X and the Check Mark, all right.
Those are really cool.
But I think we probably want an Unchecked box and then a Check box so what I ended up finding was down here at 254 in Wingdings and also character 168 in Wingdings.
It gives me the box and the uncheck box.
All right, let’s just insert a new worksheet here and here, we’re going to do =CHAR(254) and we’re going to change that to Wingdings.
All right, I’m going to center vertically, center horizontally and make the columns smaller, and maybe I need to increase the size there, just choose a size that you think looks good.
Now, when we were using the Forms control, I will grant that both the Check box and the text were allowed to live in the same cell but not here.
We’re going to have the Check boxes in column A and the text over there in column B and maybe we need to vertically align that.
But you get it, it’s going to work.
So let’s just come up with Risk Item #2.
I’m not going to type all the items; I’m sure that Catherine already has those.
I’ll use the fill handle to go down 200 cells...
Oops, too far.
And then we’ll copy this Check box down.
But it’s not a control, it’s just a character when you click in the cell nothing happens, so we need a tiny bit of VBA.
So here is my sheet, I’m going to call this New Check Box.
I’m going to make sure that this file is saved with an .xlsm file type so File, Save As.
By default, a lot of computers are set to Save As .xlsx which is the worst file type in the world.
It’s the only file type that’s not allowed to have macros so you want to go to your .xlsm or .xlsb or heck, even .xls.
That’s like one of these two though, make sure that you’re saved that way and then we’ll press Alt+F11.
If you’ve never done macros before, Alt+F11 takes you to the VBA window here – just a big, gray, ugly screen.
You want to go to View, Project Explorer.
You want to find that sheet – that sheet was called NewCheckbox, double click the sheet in the project explorer, top left drop down, choose Worksheet and then top right drop down.
I’m going to say that anytime we double click one of the check boxes, it’s going to change.
So, we're going to use the BeforeDoubleClick so everytime that we double click a cell, this macro is going to run and just like on the cooking shows, I already had the cake put in the oven an hour ago, so here is the code.
They’re passing as a variable called Target – that’s the cell that was just double clicked.
If that column is =1, if they click something in column A, then go see if we’re currently at Character 254; if we are, change it to Character 168 and then if we were currently at Character 168, change it back to 254.
The really important part here is they give us a second argument called Cancel.
Cancel = True.
That will cancel the double click so we’re going to put that cell in Edit Mode.
Let’s switch back to Excel with Alt+Q and now, check this out.
If I come here and double click, it will change to Uncheck, change to Uncheck, change to Uncheck.
Or if I double click, it will change back to Check.
And those two characters, they look the same, so it looks like the check mark is coming and going.
Now, what if we need to count the number of items that are checked?
That’s going to be =COUNTIF(A:A,CHAR(254)) and we’ll get a count of how many items are checked.
Now, the only complaint that we’re going to get from the person using this form is they want to be able to just click once to check and the downside to this.
So back here in the sheet, I have one set-up where instead of it’s the double click macro, I used both the double click macro and the selection change macro.
Only difference is we don’t have to the =CANCEL=TRUE.
And while this works if the person using the spreadsheet is a mouse person, so it adds the check boxes and unadds the check boxes, it will be really dangerous if you ran into a keyboard person and they sat here.
I’m going to press the down arrow keys, everyone of these things is going to toggle to the opposite state – a great way to ruin all of your check boxes.
You really, really hack the people off.
But if you’re designing this for mouse people, then using the Selection Change might be a better way to go.
This would be in an ordinary amount of work to try out 200 of these and set each one of them up.
Maybe you could do that with VBA, but I’m even going to argue that each one of these little controls is a foreign object and you’re taking a spreadsheet and putting 200 foreign objects on it is an immense amount of overheard.
Lots of extra stuffs that happens to go on there and none of that stuff is happening here because this is just simple, it’s changing from one letter to another letter using the Wingdings font.
To me, a much, much better way to go.
Hey, I want to thank Catherine for sending that question in.
I want to thank you for stopping by, we’ll see you next time for another netcast with MrExcel.
Learn Excel from MrExcel Podcast Episode 1871: 200 Checkboxes on a Worksheet.
Hey, everyone, welcome back to the MrExcel netcast, I’m Bill Jelen.
Today’s question is sent in by Catherine.
Catherine is designing a Risk Assessment.
She was really happy. Check this out - File options, and Customize the Ribbon, come over here to the Developer tab. There’s lots of good stuff on that Developer tab including under Insert.
There are Form Controls with Check Boxes and ActiveX Controls with the Check Boxes so you can draw a little check box in there and we will have it, you know, it’s a Risk Assessment.
There’s different things like, Do you smoke or something?
And we can right click and Format the Control and say that this is tied to cell B2.
Click OK and then here’s how it works.
So, you check the box, you get a True; uncheck the box, you get a False.
But wait, we have to do 200 of those, that is going to be a nightmare to go through instead of each one and so that is associated with the item to the right.
So, I have an alternate way to go.
Catherine’s question was, “Hey, how do I make 200 of these?” My suggestion is you’re not going to make any of them.
So here’ what I did.
This is a trick from Sylivia Huas.
She did it on the podcast earlier this year.
We have formulas here that =CHAR(ROW()).
And that gives us all of the characters so this is Character 2, this is Character 23, Character 44 and so on.
And I change the various columns, you’ll see on the Home tab here that this column is Webdings, this column is Wingdings, Wingdings 2, Wingdings 3 and then Calibri is so we can see what the actual letter is.
Every computer going back to Excel ’97, at least Windows, has these and I was heading down to the Rs because I remember here in the Wingdings 2 this X and the Check Mark, all right.
Those are really cool.
But I think we probably want an Unchecked box and then a Check box so what I ended up finding was down here at 254 in Wingdings and also character 168 in Wingdings.
It gives me the box and the uncheck box.
All right, let’s just insert a new worksheet here and here, we’re going to do =CHAR(254) and we’re going to change that to Wingdings.
All right, I’m going to center vertically, center horizontally and make the columns smaller, and maybe I need to increase the size there, just choose a size that you think looks good.
Now, when we were using the Forms control, I will grant that both the Check box and the text were allowed to live in the same cell but not here.
We’re going to have the Check boxes in column A and the text over there in column B and maybe we need to vertically align that.
But you get it, it’s going to work.
So let’s just come up with Risk Item #2.
I’m not going to type all the items; I’m sure that Catherine already has those.
I’ll use the fill handle to go down 200 cells...
Oops, too far.
And then we’ll copy this Check box down.
But it’s not a control, it’s just a character when you click in the cell nothing happens, so we need a tiny bit of VBA.
So here is my sheet, I’m going to call this New Check Box.
I’m going to make sure that this file is saved with an .xlsm file type so File, Save As.
By default, a lot of computers are set to Save As .xlsx which is the worst file type in the world.
It’s the only file type that’s not allowed to have macros so you want to go to your .xlsm or .xlsb or heck, even .xls.
That’s like one of these two though, make sure that you’re saved that way and then we’ll press Alt+F11.
If you’ve never done macros before, Alt+F11 takes you to the VBA window here – just a big, gray, ugly screen.
You want to go to View, Project Explorer.
You want to find that sheet – that sheet was called NewCheckbox, double click the sheet in the project explorer, top left drop down, choose Worksheet and then top right drop down.
I’m going to say that anytime we double click one of the check boxes, it’s going to change.
So, we're going to use the BeforeDoubleClick so everytime that we double click a cell, this macro is going to run and just like on the cooking shows, I already had the cake put in the oven an hour ago, so here is the code.
They’re passing as a variable called Target – that’s the cell that was just double clicked.
If that column is =1, if they click something in column A, then go see if we’re currently at Character 254; if we are, change it to Character 168 and then if we were currently at Character 168, change it back to 254.
The really important part here is they give us a second argument called Cancel.
Cancel = True.
That will cancel the double click so we’re going to put that cell in Edit Mode.
Let’s switch back to Excel with Alt+Q and now, check this out.
If I come here and double click, it will change to Uncheck, change to Uncheck, change to Uncheck.
Or if I double click, it will change back to Check.
And those two characters, they look the same, so it looks like the check mark is coming and going.
Now, what if we need to count the number of items that are checked?
That’s going to be =COUNTIF(A:A,CHAR(254)) and we’ll get a count of how many items are checked.
Now, the only complaint that we’re going to get from the person using this form is they want to be able to just click once to check and the downside to this.
So back here in the sheet, I have one set-up where instead of it’s the double click macro, I used both the double click macro and the selection change macro.
Only difference is we don’t have to the =CANCEL=TRUE.
And while this works if the person using the spreadsheet is a mouse person, so it adds the check boxes and unadds the check boxes, it will be really dangerous if you ran into a keyboard person and they sat here.
I’m going to press the down arrow keys, everyone of these things is going to toggle to the opposite state – a great way to ruin all of your check boxes.
You really, really hack the people off.
But if you’re designing this for mouse people, then using the Selection Change might be a better way to go.
This would be in an ordinary amount of work to try out 200 of these and set each one of them up.
Maybe you could do that with VBA, but I’m even going to argue that each one of these little controls is a foreign object and you’re taking a spreadsheet and putting 200 foreign objects on it is an immense amount of overheard.
Lots of extra stuffs that happens to go on there and none of that stuff is happening here because this is just simple, it’s changing from one letter to another letter using the Wingdings font.
To me, a much, much better way to go.
Hey, I want to thank Catherine for sending that question in.
I want to thank you for stopping by, we’ll see you next time for another netcast with MrExcel.
