Celebrate 'The VLOOKUP Book' birthday with us. Last day to get 50% discount on the e-book (31 October only).

Click here for details

Excel Check Boxes, Even Simpler Way

Posted on September 15th, 2009 in Excel Howtos , Learn Excel - 25 comments

Add check-boxes to Excel - Form ControlsIn how to get tickmarks in excel, Jon (the Peltier, not the Stewart) commented,

[...] Better yet, use real checkboxes, so the user can change them with the mouse.

That got me thinking,

  • Who is this user we keep talking about :-?
  • Why not ?!?

In excel, you can add a check-box to spreadsheet using developer tools. See to the right:

But what if you needed a whole bunch of check-boxes?

Well, you could add one check box and copy paste the same a bunch of times. Only problem will be, all of them will refer to the same cell. Thus you check one, you check all. Checking out all might be a good option if you are in a party without a date. But, in excel, you need to be a bit more specific, no?

excel-check-boxesSo, I created a bunch of check boxes, each linked to one separate cell. And now, being the good, caring and lovable person I am, I have placed them for download. So go ahead and get your bunch of check boxes, while the supplies last.

Once you have the file, all you need to do is, copy paste as many check boxes to your workbook as you want (there are a total of 30 in there). Change the display text to whatever fancies you at the moment. And you are good to go.

Download the excel check boxes here.

*

I can imagine 2 colleagues in a cube farm talking,

Colleague 1: Wtf, the checkboxes don’t work.
Colleague 2: Didnt you get them from Chandoo.org?
Colleague 1: No, I added them myself.
Colleague 2: No wonder :P

Your email address is safe with us. Our policies

Written by Chandoo
Tags: , , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

25 Responses to “Excel Check Boxes, Even Simpler Way”

  1. Martin says:

    Be- a- utiful !!.

    I believe though, the while purpose of the thread was to programatically add as many checkboxes as you need. I believe there are 2 approaches (at least) to do so:
    1.- ask first “how many checkboxes do you need?”, and then add them using a macro.
    2.- evaulate the content of a cell within a range, and determine if it’s empty or not, and if not (assuming there you put the expression you want to evaluate, which eventually can be used as the checkbox caption), then add the corresponding checkbox with its relative format.

    Rgds.

  2. dhammer says:

    I didn’t write this, but this bit of code works to add as many check boxes as you’d like. Here’s the link: http://www.terminally-incoherent.com/blog/2008/09/04/excel-adding-checkboxes-the-easy-way/

    I used it for a project where I had to insert hundreds of check boxes – it even names the check box by the cell it resides in, quite helpful.

    When you run the macro, it pops up three dialog boxes, the first you enter the cells you want the check boxes (A1:A12); in the second you enter the column you want the corresponding control to go (“A” if you want them to sit under the check boxes, “B” if you want them next to them etc.); and the third is where you’d type a label. There may be a way to have each label be different (or correspond to a cell, but I never use labels…)

    And here’s the code:

    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

  3. Les says:

    I don’t know how he did it ….. but I’d like to know because it’s a neat trick and that’s why people limit them to just a few at a time.
    Puzzled.

  4. Yogesh Gupta says:

    There is another way to use Wingding chek boxes and change them with the double click with the help of macro

    You can add as many of them by just defining the range in your macro. Values can be changed by just a double click.

    You will furhter need to do data validation for that range so that user does not enter any other value by mistake.

    Add following code to Sheet1 through VB Editor under microsoft excel obejects.

    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

    If Not Intersect(Target, Range(“G4:G14″)) Is Nothing Then
    Cancel = True
    If Target.Value = “þ” Then
    Target.Value = “ý”

    Else
    Target.Value = “þ”
    End If
    End If

    End Sub

    Range G4:G14 will need to be formated with wingdings font.

    You can download file containing this trick.
    Change Value by double click.xls

    This trick can be used to choose any values you want change with the double click and is not limited only to check boxes in wingdings.

  5. Chandoo says:

    @Dhammer and Yogesh.. very good additions. I am too lazy to write the macro. But I know that is the best way to do it.

    @Martin: thanks. I am sure you will more happy to see what Dhammer and Yogesh came up with.. :)

  6. [...] stuff we are used to Excel. If MS implements this idea, we dont need to resort to sneaky tricks to get a bunch of checkboxes in excel or use wingdings [...]

  7. [...] file uses lots of excel goodness like conditional formatting and excel check-boxes. As soon as you mark a goal (or resolution) as completed, it is highlighted in a different [...]

  8. TrumanHW says:

    If this was supposed to be helpful – its not. I can’t edit it. I can’t undo changes without affecting a DIFFERENT workbook, and I can’t modify it. If I have to go and manually do this myself, it defeats the purpose – and requires I go back to your other page where you define what and how – of course, with an EFFING PICTURE! Why not have it to where its COPY PASTE able? lol. If you’re trying to make this efficient and versatile, you failed. It doesn’t shock me – people who can create code tend to ACTUALLY be the STUPIDEST people I ever meet – because they are mental specialists and always lack the ability to see something from anothers prospective. Off to search the web for someone who deviates.

  9. Chandoo says:

    @Truman… what is it that you were referring to… ? (also, you have choice to visit any other website. Pls. refrain from using foul language… You only show your incompetence with that…)

  10. Renee Miller says:

    Hello friends!

    Tis I the secretary with the lazy doctors who didn’t want to have to actually sign their names! You all were kind enough to help me then I’m hoping that perhaps you will take pity on me and do so again?

    This time they want one box with many boxes to check in it. I figured out how to put the box in via view, toolbars, control toolbox and then list box. I was very proud of myself until I couldn’t figure out how to get the check boxes in it :(

    I apologize if I haven’t provided enough information and I would be very grateful for any assistance.

    Thank you for your time and patience!
    Renee

  11. Hi All,

    I have used check boxes for an employee survey and would now like to bring all this information (from 50 responses) into one document that can they be used to produced slides in powerpoint for feedback – how do I do this please?????

  12. Ramnath says:

    Hi,

    I have to search for matching records in a database and the result can be any numer of records. Normally the number of records will not exceed 5-10. Since, I don’t know the number of records which will be displayed, I have to create as many Checkboxes for users to check what is n=required and what is not required.

    Is this possible?

    Regards,
    Ramnath

  13. dina says:

    you are my new BFF ! thanks so much

  14. Rana says:

    Hi,

    I am trying to rename some of the check boxes by editing the text, but unfortunately I cannot edit the text. Am I doing something wrong. I managed to edit it for couple of boxes but cant for most of them.

  15. Hui... says:

    @Rana
    Select the Check Box
    Rename it in the Name Box just above Cell A1, where it will show you the current name

  16. Rana says:

    Hi Hui,

    Hope you had a good Easter break. Unfortunately the solution did not work for me. I then tried right click and changed the name. However each time I change the name by the right click option, the cell gets linked to another cell and all boxes get checked when I click any one of them.

    Its so not happening for me :(

    Regards,
    Rana

  17. Hui... says:

    @Rana
    Goto the Selection Pane
    Which is on the Page Layout, Selection Pane tab

    Your Check Boxes should all be shown in the window, there maybe other objects there as well
    Double click or select and F2 the Check Box you want to rename, enter the new name
    Note That Excel allows you to have 2 Check Boxes named the same so be careful

  18. Assaf says:

    Hi Chandoo
    You’ve shown in your picture a the start how to add a single check box in v2007, (or is it 2010?). Can you please point me to some similar instruction for 2003? (that is unfortunately what I am forced to use). I just want to add one check box, that’s all. I’ll work out the rest (I think).

    I can’t download and use anything prewritten as my work is on a different network with no cut n paste between them.

    Many thanks
    Assaf

  19. Assaf says:

    Sorry to have bothered you.
    I finally found something.
    http://excel.tips.net/T003246_Using_Check_Boxes.html

  20. Nicola Mayo says:

    Great XSL template

    i use it now in my excel sheet
    can yuo explain to me how the False and True apear nest to the check box
    Thanks & Regards
    Nicola

  21. SHASHWAT says:

    Hi chandoo,
    i got to know bot ur site few day ago since now i got found of it………….
    loved it so much man, this is great..
    plz help me to know how to put true and false which changes accordingly upon tick and un tick………….

  22. One of the better characteristics that have been added throughout edition
    3 could be the automated updates. Because of this totally free sauna unique codes are generally up to date automatically along with whenever you
    run this software. As these types of unique codes are usually dynamically becoming additional and decrypted, which
    means that one could receive one rule then when you restart
    this course right after few minutes the particular databases should recharge using completely new requirements.
    It is great simply because just before version 3. 0 people needed to acquire fresh edition on the compromise everyday which
    often was comprised of brand-new codes.

  23. Nikhil says:

    Hi Chandoo,

    I need to add checkboxes to an Excel Chart. The Chart is created by another macro and is not on top of a Worksheet (no rows or columns).

    When I position the checkboxes at the required location and record a macro I get this: “ActiveSheet.CheckBoxes.Add(5742337.5, 388789.5, 685730.25, 686298.75).Select”

    Since I cannot link the checkbox to a cell how do I position the checkbox? Thank you in advance.

  24. Trevor says:

    I have MS Windows 7 and cannot find any of the panels that you show at the top of this site in my Excel. I really need to get going with check boxes or radial buttons but can’t even find the panel to do that.

    Under my insert tab none of the above appear.

    • Hui... says:

      @Trevor
      You are looking in the wrong place
      This Insert Menu is on the Developers Tab
      To add the Developers tab,
      Right Click on the Tabs,
      Customise the Ribbon,
      Enable the Developers Tab.

      Then you will see the Insert Menu discussed

      ps: Excel is mostly a Part of Office, although it can be purchased separately.
      Windows 7 is an operating system
      There are many versions of Office and you most likely have Excel 2010 or Excel 2013
      Both of these can run under a number of Windows versions.
      So it is more relevant to quote the Office / Excel version you are running.
      In Excel goto File, Help and look there for the version Number

Leave a Reply