Add checkboxes to a sheet (1/2) [VBA]
In this post, I will demonstrate a macro that creates checkboxes in nonempty rows, see checkboxes in column E in image above.
The button "Add Checkboxes" next to column E is assigned to macro Addcheckboxes(), press with left mouse button on the button and the macro will be rund.
The same thing goes with the button "Remove Checkboxes" next to column E is assigned to macro RemoveCheckboxes(), press with left mouse button on the button and that macro will be rund.
VBA code - Add checkboxes
'Name macro Sub Addcheckboxes() 'Declare variables and data types Dim cell, LRow As Single Dim chkbx As CheckBox Dim CLeft, CTop, CHeight, CWidth As Double 'Don't refresh or update screen while processing macro, this will make the macro quicker. Application.ScreenUpdating = False 'Find last non empty cell in column A LRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row 'Iterate through 2 to last non empty cell For cell = 2 To LRow 'Check if cell in column A is not equal to nothing If Cells(cell, "A").Value <> "" Then 'Save cell dimensions and coordinates of corresponding cell in column E to variables CLeft = Cells(cell, "E").Left CTop = Cells(cell, "E").Top CHeight = Cells(cell, "E").Height CWidth = Cells(cell, "E").Width 'Create checkbox based on dimension and coordinates data from variables ActiveSheet.CheckBoxes.Add(CLeft, CTop, CWidth, CHeight).Select With Selection .Caption = "" .Value = xlOff .Display3DShading = False End With End If Next cell 'Turn on screen refresh Application.ScreenUpdating = True End Sub
VBA code - Remove checkboxes
'Name macro Sub RemoveCheckboxes() 'Declare variables and data types Dim chkbx As CheckBox 'Iterate through all check boxes on active sheet For Each chkbx In ActiveSheet.CheckBoxes 'Remove checkbox chkbx.Delete 'Continue with next checkbox Next End Sub
Where to copy VBA code?
- Copy above code.
- Press Alt+F11 to open the Visual Basic Editor.
- Press with right mouse button on on your workbook in the Project Explorer, see image above.
- Press with left mouse button on "Insert".
- Press with left mouse button on "Module".
- Paste VBA code to the module.
- Exit VBE and return to Excel.
I have assigned the macros to two buttons: "Add Checkboxes" and "Remove Checkboxes", the top image shows these buttons.
- Go to Developer tab.
- Press with left mouse button on "Insert" button.
- Press with left mouse button on "Button" button ;-)
- Press with left mouse button on and drag on the worksheet and then release mouse button to create the button.
- Select a macro.
- Press with left mouse button on OK!
In the next post I will describe how to copy selected rows to another sheet.
Next: Copy selected rows (checkboxes) (2/2)
Check boxes category
This article demonstrates a macro that copies selected rows based on enabled check boxes. The image above shows data on […]
Today I will share a To-do list excel template with you. You can add text to the sheet and an […]
This tutorial shows you how to list excel files in a specific folder and create adjacent checkboxes, using VBA. The […]
Excel categories
8 Responses to “Add checkboxes to a sheet (1/2) [VBA]”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
You can reduce your RemoveCheckboxes macro to a one-liner...
Sub RemoveCheckboxes()
ActiveSheet.CheckBoxes.Delete
End Sub
Rick Rothstein (MVP - Excel),
Thanks!!
Hi Oscar thanks for posting the Addcheckboxes code, it's just what I have been looking for!
I need to modify it to search through a named range ( as opposed to the last row with data. I am not proficient in VBA at all , so when I tried to alter the code it was rejected.
thanks
Thank you I was looking for this but I am having an issue with the
LRow = ActiveSheet.Range("A" & Rows.Count).End(x1Up).Row
Giving me a Run-time error '1004':
Application-define or object-defined error
Nole Sheets,
Does this work?
Thanks for this very useful example! My VBA creates a table with N rows, where N is determined by the value in a named range (RowCount). I'd like to use your framework for populating one of the named columns in my table with checkboxes, but I'm not sure how to reference a table column using VBA. I'd appreciate any suggestions and thanks again for this helpful resource.
Hi,
I kinda new to vba code and I like it.
I am trying to create a function that would create a checkbox into the cell that I am in.
Any clues?
Thx
Thanks for this. I've been looking fpr something like it for a while. The only issue I have is that if the check boxes are "ticked" and then more data is added below the last line, when you run the code again it resets all the previous check boxes back to "off". Is there anyway to not change the checkboxes above the last one created?