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.