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:
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.
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.
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
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. -.-
Thank you very much! This was just what I was looking for for my quest list. ^^
This code is great!
I was surfing the net for hours to find something like this!
Thanks a lot.
Oh my God. Exactly what I ve been looking for for years!
THANK YOU.
Thanks a lot from an unexperienced user. You saved my life.
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
Is there a way to have one checkbox named ‘All’ and when checked or unchecked, checks or unchecks a range of checkboxes?
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! ;)
Your Purgatory is my Heaven – thank you, much appreciated
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
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
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!
@ 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.
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
God bless you man.
Thank you very much for posting this :)
thanks for posting this.. it was a great help
Thank you, this is going yo make my life sooo simple!!1
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
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?
Thank you very much, just what I needed
Works excellent thank you – however – when i select a check box it ticks the whole collumn – am i doing something wrong??? please help!
Worked perfectly thanks.
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!
@ Brian:
is there a code for this? check all? uncheck all?
@ 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!
@ Pumba: I’m having the same problem…. did you ever figure it out?
Thank you! Thank you!
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”
Sam wrote:
Try removing the quotes around TRUE.
THANK YOU!!!! <3
@ Shelley:
I’m having the same problem as you and Pumba. Did either ever figure it out?
@ 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.
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
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
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?
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?
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.
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?
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.
Dude…you rock!!!!
Is there any way to change the “True/False” return to provide the exact time. say using the NOW() function?
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 :)
Thanks, you saved me a ton of work. :)
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!
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!
Thanks man!
Helped me in dozens of ways, eggs-actely wot I needed.
No yoke!
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
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.
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.
Just what I needed , thanks for posting the code!
Don’t Shoot!!!!!!
You are too awesome! I was pulling my hair out trying to figure out an easier way to do a work checklist- then I finally found this. YOU ROCK!!!!!!!!!!! THANK YOU!! :)
Help!!! I know this will work but I’m blowing it up! when I click on the first check box they all become checked and it only says True on the first cell. So basically they are all linked. Thanks!!!! Jane
Hey programmer,
Thanks for the code it worked. But I am facing one small problem and it is that the Linked cell shows the result only in the formula bar, whereas the Cell value doesn’t change and because of which I am unable to process whether the checkboxes are selected or not. Please help me so that I can give the Linked cell its value.
@ Luke Maciak:
Have a question. How would I form the syntax to uncheck the box if the value in another cell was than say 1?
I tried this
Code:
If Range (“G” & .Column).Value = “0” Then
Range(“sheet1″).chexkbox1.Value =”False”
and tell the script to continue to the rest of the sub?