Learn Excel – 200 Checkboxes in Excel – Podcast 1871

Learn Excel – 200 Checkboxes in Excel – Podcast 1871


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.

35 thoughts on “Learn Excel – 200 Checkboxes in Excel – Podcast 1871

  1. Another option is to use the "follow hyperlink" event then a single click does the work:

    Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
        Application.PreviousSelections(1).Select
        If ActiveCell.Value = Chr(254) Then
            ActiveCell.Value = Chr(168)
        ElseIf ActiveCell.Value = Chr(168) Then
            ActiveCell.Value = Chr(254)
        End If
    End Sub

  2. Hi Mr. Jelen. This is great and I was able to make it work for the one column as noted above. However I would like to do the same for multiple columns on the same worksheet. I keep getting errors when i try to copy the formula down and change the column identifier. Any suggestions? Ideally I would like to have this check box practive over about 6 columns on the same worksheet. 

  3. Just an FYI, You can copy and paste the check boxes to where you would like them to be or you can also highlight the corner and drag down the row and it will fill in each space. The same way you would number a bunch of rows etc.  :o)

  4. When I double Click the check box doesn't change to the blank box. It has multiple wingding pop up as if it doesn't know what to change to. How do I fix this? I've gone back 10x to make sure I didn't miss a step…

  5. @Bill Jelen, do you have any objection to using right click instead of double? It would be a lot easier on the hand for a long list.

  6. im getting an error message
    " Compile error:"
    "Procedure declaration does not match description of even or procedure having the same name"

  7. How to reset your checklist and clear all of the boxes? Simple! Set the first checkbox to FALSE (or unchecked) and then do the autofill thing all the way down column A. Can you do that as easily when using form controls? No.

  8. thank you for ur valuable tutorials u share with us..
    would u plz clarify how we color the same cell that has the checkbox when it is checked? !!

  9. @MrExcel.com I'm using Excel 2016 and have followed the tutorial. I'm still getting the check boxes to show up in edit mode. Can you help with this? Here's my code:

    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Target.Column > 0 Then
    If Target.Value = Chr(168) Then
    Target.Value = Chr(254)
    Cancel = True
    ElseIf Target.Value = Chr(254) Then
    Target.Value = Chr(168)
    Cancel = True
    End If
    End If
    End Sub

    I want the boxes to show up empty if it's a "no" and checked if it's a "yes" so I switched the code around. Also, it spans from columns E – S.

    Thanks for your help!

  10. Not sure if you still follow these comments, however, I love this technique. Is there a way to conditionally format to highlight/color fill boxes based on chr"254" (checked box)? Thanks.

  11. how to make vba to custom own addins……….. please we requested by indian followers……..
    please make. it step by step in Microsoft excel 2016…….

  12. How does it link or actually know how to replace Char 254 with Char 168? @1:18 You have showing in the formula bar – =Code(C260) how does this play into the solution? Cheers

  13. Help please… first off great video. Second, if i wanted multiple check boxes in colums on a sheet, how should the VBR code look? Row number followed by Then after each column number? Example, If Target.Value = 13 Then 14 Then 15. thanks

Leave a Reply

Your email address will not be published. Required fields are marked *