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(), click the button and the macro will be executed.
The same thing goes with the button "Remove Checkboxes" next to column E is assigned to macro RemoveCheckboxes(), click the button and that macro will be executed.
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.
- Right-click on your workbook in the Project Explorer, see image above.
- Click "Insert".
- Click "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.
- Click "Insert" button.
- Click "Button" button ;-)
- Click and drag on the worksheet and then release mouse button to create the button.
- Select a macro.
- Click OK!
In the next post I will describe how to copy selected rows to another sheet.
Open Excel files in a folder [VBA]
This tutorial shows you how to list excel files in a specific folder and create adjacent checkboxes, using VBA. The […]
Copy selected rows (checkboxes) (2/2)
This article demonstrates a macro that copies selected rows based on enabled check boxes. The image above shows data on […]
Multi-level To-Do list template
Today I will share a To-do list excel template with you. You can add text to the sheet and an […]
Toggle a macro on/off using a button
This article demonstrates how the user can run a macro by clicking on a button, the text on the button […]
I will now demonstrate with the following table how to add check-boxes and sum enabled check-boxes using a formula. Add […]
5 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?