Excel: Adding Checkboxes the Easy Way

Apparently I have trespassed against The Big Programmer In the Sky and I’m currently suffering in Coder’s Purgatory where they make me code in VBA. It could be worse. I could be in Programmer Hell (also known as the infamous 0xf th Circle of Hell) where you don’t get to code at all but instead attend meetings and team building events. I figured that since I’m suffering, I will make you suffer as well by exposing you to some VBA.

For example, how about we create something like this in Excel:

excel1.png

You know, like a checklist. The users would go down, the list, hit the appropriate checkboxes and then we would have a script that extracts this data, and builds an exception list. How many questions? Oh, not many – maybe like a 100 in total, spread across 4-5 worksheets. And the good news is that the questionnaire already exists in Word document so you can just like copy and paste it.

Each checkbox is linked to a cell and toggling it will toggle the value of the cell between TRUE and FALSE. Extracting data is trivial. The problem here is creating the checklist itself – it is a tedious data entry job you give to your intern after he/she finishes filing, and does the Starbucks run. There is no easy way to copy these checkboxes. Or rather you can copy them, but their cell references do not change. So you have to change the linked cell property for every single checkbox manually.

So I said “fuck this shit” and wrote a macro to do this for me. The following script will prompt the user for 3 things: the Cell Range which should be given like so A1:A10 which is the range where the checkboxes will be inserted, a Linked Column to be used for linked cells (the row number of the linked cell will be the same as the row number of where the checkbox was inserted) and a Checkbox Label (the text to be displayed next to the checkbox). Then it will generate the right number of checkboxes. Here is the code:

Option Explicit
Sub insertCheckboxes()
 
  Dim myBox As CheckBox
  Dim myCell As Range
 
  Dim cellRange As String
  Dim cboxLabel As String
  Dim linkedColumn As String
 
  cellRange = InputBox(Prompt:="Cell Range", _
    Title:="Cell Range")
 
  linkedColumn = InputBox(Prompt:="Linked Column", _
    Title:="Linked Column")
 
  cboxLabel = InputBox(Prompt:="Checkbox Label", _
    Title:="Checkbox Label")
 
  With ActiveSheet
    For Each myCell In .Range(cellRange).Cells
      With myCell
        Set myBox = .Parent.CheckBoxes.Add(Top:=.Top, _ 
          Width:=.Width, Left:=.Left, Height:=.Height)
 
        With myBox
          .LinkedCell = linkedColumn & myCell.Row
          .Caption = cboxLabel
          .Name = "checkbox_" & myCell.Address(0, 0)
        End With
 
        .NumberFormat = ";;;"
      End With
 
    Next myCell
  End With
End Sub

So here you go, inserting checkboxes the lazy way. Welcome to my purgatory, I hope you can feel my opain. Now excuse me while I go shoot myself in the face with a shotgun.

End With.

This entry was posted in programming. Bookmark the permalink.



53 Responses to Excel: Adding Checkboxes the Easy Way

  1. Another reason to not use Excel… (while i dont think the alternatives like OpenOffice or google docs are better at this)

    Maybe this is why spreadsheets are not used often for things like this. This and.. oh yeah.. everybody can edit the whole file, so if someone clicks the wrong field instead of another checkbox and types anything, there could have been something important which is now lost.

    Reply  |  Quote
  2. Luke Maciak UNITED STATES Mozilla Firefox Ubuntu Linux Terminalist says:

    My sentiment exactly – this should not be done in excel, but tell that to my boss. If he could browse internet from within excel he probably would. :P

    Reply  |  Quote
  3. Bureaucrats! -.-
    I hate them… they are the reason for the success of things like ms office or worse like this SAP/Abap-Crap i once had to deal with for short time. -.-

    Reply  |  Quote
  4. jenn AUSTRALIA Mozilla Firefox Windows says:

    Thank you very much! This was just what I was looking for for my quest list. ^^

    Reply  |  Quote
  5. Georg UNITED STATES Internet Explorer Windows says:

    This code is great!
    I was surfing the net for hours to find something like this!
    Thanks a lot.

    Reply  |  Quote
  6. Sven GERMANY Mozilla Firefox Windows says:

    Oh my God. Exactly what I ve been looking for for years!

    THANK YOU.

    Reply  |  Quote
  7. prepre SPAIN Mozilla Firefox Windows says:

    Thanks a lot from an unexperienced user. You saved my life.

    Reply  |  Quote
  8. David R UNITED STATES Mozilla Firefox Windows says:

    Your a life saver I was just doing this and I had to make some where near 50 checkboxes per sheet and was doing them 1 by 1 for the first 20 and thought OMG there has to be a better way…. YOU ARE AWESOME! and saved me from hurting my hand *click* edit *click* edit

    THANK YOU SO MUCH

    Reply  |  Quote
  9. Brian Mozilla Firefox Windows says:

    Is there a way to have one checkbox named ‘All’ and when checked or unchecked, checks or unchecks a range of checkboxes?

    Reply  |  Quote
  10. JimW UNITED STATES Internet Explorer Windows says:

    I wonder… is there a way to reverse this process and remove all of the boxes as well? Yeah, I know, it can be done manually, but… HAHA!

    I have a scenario in which I’m returning a table to Excel that includes all of our product families, and then every line in each family, and the checkbox thing here then adds boxes to each of those lines. Intended use is to put together a price quote sheet, the user can select which of those family/lines to prepare the quote using, and then those selections are used as parameters in another query that goes out and pulls in all of the item numbers associated with the selected family/lines.

    Problem is that this list changes as new product lines are rolled out – meaning when I refresh the first table (to which I’ve added check boxes), some subset of the boxes are no longer in the right place, not linked to the right cell that corresponds with the new line, etc.

    So something that removes the boxes – then re-runs the initial process to put *new* boxes in… perfect! ;)

    Reply  |  Quote
  11. Tim AUSTRALIA Google Chrome Windows says:

    Your Purgatory is my Heaven – thank you, much appreciated

    Reply  |  Quote
  12. gnomo567 SPAIN Google Chrome Windows says:

    To delete: :-)

    Sub deleteCheckboxes()

    Dim myBox As CheckBox
    Dim myCell As Range

    Dim cellRange As String

    cellRange = InputBox(Prompt:=”Cell Range”, _
    Title:=”Cell Range”)

    With ActiveSheet
    For Each myCell In .Range(cellRange).Cells
    With myCell
    ActiveSheet.Shapes(“checkbox_” & myCell.Address(0, 0)).Select
    Selection.Delete
    End With
    Next myCell
    End With
    End Sub

    Reply  |  Quote
  13. AusSteelMan AUSTRALIA Internet Explorer Windows says:

    Thanks for the insert and delete codes

    On the delete code, I changed the”.Select” to “.Delete” and deleted the “Selection.Delete” as there is no need to select it to delete it.
    I tested the code and it works ok

    Thanks again.
    ASM

    Reply  |  Quote
  14. Erika UNITED STATES Internet Explorer Windows says:

    This is absolutely brilliant–exactly what I was looking for! Forgive my lack of knowledge, but is there any way to center the box in the cell? The traditional centering does not work, nor does trying to circle it with the drawing tool. Thanks so much!

    Reply  |  Quote
  15. JimW UNITED STATES Internet Explorer Windows says:

    @ Erika:

    Erika, I’m controlling the placement of the check boxes in the “Set myBox = .Parent.CheckBoxes.Add” section of code, like this:


    With ActiveSheet
    For Each myCell In .Range(cellRange).Cells
    With myCell
    Set myBox = .Parent.CheckBoxes.Add(Top:=.Top - (.Height / 6), _
    Width:=.Width, Left:=.Left + (.Width / 4), Height:=.Height / 6)

    (code not retaining indents for some reason, sorry)

    I had to play with the settings a bit to nudge the thing to where I wanted it, so you may have to use different values. Basically this tells it that the top of the box shouldn’t be at the top of the cell, but at the ‘top minus 1/6 of the height of the box’, and that the left of the box shouldn’t be at the left of the cell, but at the ‘left + 1/4 the width of the box’, etc. Hope that makes sense.

    Reply  |  Quote
  16. Marc UNITED KINGDOM Internet Explorer Windows says:

    can someone help me i am losing the will to live lol
    I am trying to create a user form to alloww easy inputting everything is working apart from the check box. All i want it to do is display the word YES in the cell (and the new cell each time a new row of data is inputted) when checked and NO when unchecked.

    Please any help would be grately appreciated.

    Thanks

    Marc

    Reply  |  Quote
  17. Dan UNITED STATES Mozilla Firefox Windows says:

    God bless you man.

    Reply  |  Quote
  18. brandon UNITED STATES Internet Explorer Windows says:

    Thank you very much for posting this :)

    Reply  |  Quote
  19. mj AUSTRALIA Mozilla Firefox Windows says:

    thanks for posting this.. it was a great help

    Reply  |  Quote
  20. Andre UNITED KINGDOM Mozilla Firefox Windows says:

    Thank you, this is going yo make my life sooo simple!!1

    Reply  |  Quote
  21. Barry UNITED KINGDOM Google Chrome Windows says:

    Can’t program, tried to learn many times but I have no real reason in my work life to work at it so it falls by the wayside….

    Just created 1500 check boxes on a spreadsheet with this….took a few minutes but that is so much better than the alternative….thanks

    Reply  |  Quote
  22. Bryan UNITED STATES Mozilla Firefox Windows says:

    While we all feel Excel is not the best way to do it… what are other ways to make a check list? What are the other options?

    Reply  |  Quote
  23. Jomme BELGIUM Mozilla Firefox Windows says:

    Thank you very much, just what I needed

    Reply  |  Quote
  24. Pumba UNITED KINGDOM Internet Explorer Windows says:

    Works excellent thank you – however – when i select a check box it ticks the whole collumn – am i doing something wrong??? please help!

    Reply  |  Quote
  25. Alex UNITED STATES Internet Explorer Windows says:

    Worked perfectly thanks.

    Reply  |  Quote
  26. BiLLy NETHERLANDS Internet Explorer Windows says:

    Verry nice! thanx a lot!
    saves me houres of work!

    but maybe you can save me more houres?
    i would like to do the same with radio/option buttons.
    3 buttons next to each other where one can be selected at the time
    and that on 250 rows
    as wel with cell link selected.

    help wouth be verry nice!
    thanx allready!

    Reply  |  Quote
  27. eZ E UNITED STATES Internet Explorer Windows says:

    @ Brian:
    is there a code for this? check all? uncheck all?

    Reply  |  Quote
  28. Brittany SINGAPORE Internet Explorer Windows says:

    @ JimW: I seem to be having trouble with the placement of the checkboxes as well. I am completely new to VBA. When I run the macro as shown above without editing anything, I get an error message “Run-time error ‘1004: Unable to get the Add property of the CheckBoxes class”. I thought maybe changing the code to the one you just provided. But when I run the macro with the code you provided I get the same error. When I click ‘debug’ it highlights this part of the code:

    Set myBox = .Parent.CheckBoxes.Add(Top:=.Top, _
    Width:=.Width, Left:=.Left, Height:=.Height)

    Ultimately, I would just like to center the checkbox within the cells. Any advice or tips you could provide would be greatly appreciated. Thank you!

    Reply  |  Quote
  29. Shelley Google Chrome Windows says:

    @ Pumba: I’m having the same problem…. did you ever figure it out?

    Reply  |  Quote
  30. Victor Mozilla Firefox Windows says:

    Thank you! Thank you!

    Reply  |  Quote
  31. Sam UNITED STATES Internet Explorer Windows says:

    Why does my formula not work with the linked column?

    =IF(B3=”TRUE”,D3,””)

    I just get a blank return value when the box is checked and B3=”TRUE”

    Reply  |  Quote
  32. Gary UNITED STATES Internet Explorer Windows says:

    Sam wrote:

    Why does my formula not work with the linked column?
    =IF(B3=”TRUE”,D3,””)
    I just get a blank return value when the box is checked and B3=”TRUE”

    Try removing the quotes around TRUE.

    Reply  |  Quote
  33. Juan Google Chrome Windows says:

    THANK YOU!!!! <3

    Reply  |  Quote
  34. Matt UNITED STATES Internet Explorer Windows says:

    @ Shelley:

    I’m having the same problem as you and Pumba. Did either ever figure it out?

    Reply  |  Quote
  35. Matt UNITED STATES Internet Explorer Windows says:

    @ Pumba:
    @ Pumba:
    I had the same problem, and the advice I was given was for the second prompt box, just put the letter of the column in, no numbers.

    So for the first prompt box I would put something like F5:F10, and for the second prompt box I just put F.

    Reply  |  Quote
  36. Mark UNITED STATES Internet Explorer Windows says:

    This macro worked like a dream for what I needed it for but does anyone have any idea how to alter the code so that the check boxes are “centered” instead of left justified.

    Thanks

    Reply  |  Quote
  37. AusSteelMan AUSTRALIA Internet Explorer Windows says:

    Mark,

    I made a few mods to the code including positioning of the checkbox.

    I found that it doesn’t appear you can simply pick to position the checkbox on centre in every case. unfortunately, it is a bit of trial and error with respect to column width and checkbox insertion point.

    I replaced this:

    Set myBox = .Parent.CheckBoxes.Add(Top:=.Top, _
    Width:=.Width, Left:=.Left, Height:=.Height)

    with


    Set myBox = .Parent.CheckBoxes.Add(Top:=.Top - (.Height / 6), _
    Width:=.Width, Left:=.Left + (.Width / 5.5), Height:=.Height / 4)

    I have a comment in my code that says this is for a column width of 35 pixels and height of 17 pixels (which I think was standard height in Excel 2000 that I had at the time, 2007 new sheet defaults to 22 pixels high (in mine anyway) so a bit more fiddling would be required)
    So change the factors and see what happens, and keep adjusting until it suits your purpose this time.

    Additionally, I have posted the VBA module that I used here. (let me know if it doesn’t work)

    http://www.4shared.com/file/rQnQPfth/file.html?refurl=d1url

    There are several modifications, including an altered delete and inserting boxes in ROWs not COLUMNs.

    Please note:
    * supplied as is, It has been a long time since I modified and played around with it, and can’t guarantee every sub works properly.
    * Make sure you take a copy of your workbook before you insert and try this, and it is probably best to play and modify in a new workbook until you are satisfied it is the right answer for your book.
    * The mods I made around positioing was based on info I got out off the net, it is not all my work

    Goodluck,
    ASM

    Reply  |  Quote
  38. Buchli Internet Explorer Windows says:

    I must be missing something. How do you get the labels names from a list?
    What ever I enter in the label prompt is the name of ALL the checkboxes.
    If my list of labels is in Column C what would I put in the label prompt?

    Reply  |  Quote
  39. Andrew Mozilla Firefox Windows says:

    Thanks a ton. This is a lifesaver.

    How would I edit this code to create an array or grid of check boxes, rather than just a single column?

    Reply  |  Quote
  40. Natalie AUSTRALIA Safari Mac OS says:

    Totally awesome. Thanks for publishing this!

    I spent a while trying to add multiple columns at once (i.e. using a cell range of A2:C20) before realising it only works on a column by column basis. But still saved me stacks of time.

    Reply  |  Quote
  41. Tom UNITED STATES Google Chrome Windows says:

    Did I do something wrong?

    I have not done code before in Excel (considering learning to do this in the future) but, I copied and pasted the script, ran it, what happens is when I click any of the boxes, ALL of the boxes get a check mark. What did I do wrong?

    Reply  |  Quote
  42. Tom UNITED STATES Google Chrome Windows says:

    I see what I did wrong. Never mind. In the second Option, I was entering not the column, but the range. Figured it out and all os good. Exactly what I was looking for.

    Reply  |  Quote
  43. Jonathan Internet Explorer Windows says:

    Dude…you rock!!!!

    Reply  |  Quote
  44. mike CANADA Internet Explorer Windows says:

    Is there any way to change the “True/False” return to provide the exact time. say using the NOW() function?

    Reply  |  Quote
  45. Nate UNITED STATES Google Chrome Windows says:

    If this hsan’t died alltogether.

    Is there a way to use auto-number the labels? (1,2,3,…)

    Ascending and descending if possible.

    Thanks for the macros :)

    Reply  |  Quote
  46. Trevor Mullins Mozilla Firefox Windows says:

    Thanks, you saved me a ton of work. :)

    Reply  |  Quote
  47. Ken UNITED STATES Internet Explorer Windows says:

    Everything works for me but my checkboxes disappear when I save, close and then reopen the file. The checkboxes are still there but it’s like they are hidden behind the cell or something. If I put my cursor at a certain position I can select the very edge of the box but they have moved position. It’s so frustrating because I’ll do a bunch of rows and then save it just to find that they all disappeared. Is there any solution to this? I’m using Excel 2010.

    Thanks!

    Reply  |  Quote
  48. Ken UNITED STATES Internet Explorer Windows says:

    Well, I was able to find a solution to my problem of disappearing checkboxes above. I have Excel 2010 but decided to try out saving it as Excel 97-2003 Workbook and that seemed to solve the issue. Or at least I think that’s what’s making it stable. Whatever it is, it’s working. If I’m going about it the wrong way, please let me know!

    Thanks!

    Reply  |  Quote
  49. nowIC AUSTRALIA Mozilla Firefox Windows says:

    Thanks man!
    Helped me in dozens of ways, eggs-actely wot I needed.
    No yoke!

    Reply  |  Quote
  50. Ori Google Chrome Windows says:

    Thank you very much!

    This is what I needed, after deleting the first and last rows my excel ran it and it worked!
    thanks from making it available

    Ori Yaffe

    Reply  |  Quote
  51. Cathy UNITED STATES Google Chrome Windows says:

    Thank you so much for this post. It worked perfectly and saved me tons of time!!!! I tried other macros and they didn’t work. You are a genius.

    Reply  |  Quote
  52. Ivan UNITED STATES Google Chrome Mac OS says:

    We’ve tried to address this very use-case when we built Obvibase (https://www.obvibase.com). The idea that features like a column of checkboxes are so basic, and could be useful to so many people, that they should not require coding, or using a relational database and sacrificing ease-of-use of a spreadsheet.

    Reply  |  Quote
  53. Brian CANADA Google Chrome Windows says:

    Just what I needed , thanks for posting the code!

    Reply  |  Quote

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>