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 in excel
- Insert a module
- Paste code into code window
- Return to excel
I have assigned the macros to two buttons: Add Checkboxes and Remove Checkboxes.
- Go to Developer tab
- Click "Insert Controls" button
- Click "Button" button ;-)
- Click and drag on worksheet to create the button on a sheet
- Select a macro
- Click OK!
In the next post I will describe how to copy selected rows to another sheet.
Download Excel file
Enter your email to receive the workbook.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 excel creates […]
This article demonstrates a macro that copies values between sheets. I am using the invoice template workbook. This macro copies […]
Open Excel files in a folder [VBA]
This tutorial shows you how to list excel files in a specified folder and create adjacent checkboxes, using vba. The […]
Split data across multiple sheets [VBA]
In this post I am going to show how to create a new sheet for each airplane using vba. The […]
Working with COMBO BOXES [Form Controls]
This blog post demonstrates how to create, populate and change comboboxes (form control) programmatically. Form controls are not as flexible […]
Identify missing numbers in a column
The image above shows an array formula in cell D6 that extracts missing numbers i cell range B3:B7, the lower […]
3 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
Use the img tag, like this: <img src="Insert pic link here">
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