We can add CheckBox on Worksheet or UserForm is using VBA with checkbox control and is used to specify or indicate boolean choice. In this section we will see how to add single checkbox or multiple checkboxes on the worksheet or userform using VBA. Please find the more details about add checkbox control using VBA in the following chapter.
In this Topic:
- How to add a CheckBox on the Worksheet Using VBA Code
- How to add multiple CheckBoxs on the Worksheet using VBA code
Add a CheckBox on the Worksheet Using VBA Code
Please find the below two example macros, it will show you how to add checkbox on the Worksheet using VBA code.
- Click Developer Tab in the Excel menu bar.
- On the Code group, Click Visual Basic. Now, VBA Editor window will be displayed.
- Click Insert from the menu, Click Module and then add the below example macros into the module.
- Click run button or ‘F5’ to execute the above macro code.
- Go to Sheet3 to see the output for the above mentioned examples.
Example 1: Add Form Check Box Control
The below example show you how to add form checkbox control on the worksheet.
Private Sub ActX_Add_CheckBox_Ex1() Sheets("Sheet3").CheckBoxes.Add(Left:=Range("E1").Left, Top:=Range("E1").Top, Width:=Range("G1").Width, Height:=Range("E1").Height).Select With Selection .Caption = "Adding Checkbox" End With End Sub
Example 2: Add ActiveX Check Box Control
The below example show you how to add ActiveX checkbox control on the worksheet.
Private Sub ActX_Add_CheckBox_Ex2() Sheets("Sheet3").OLEObjects.Add "Forms.CheckBox.1", Left:=Range("A1").Left, Top:=Range("A1").Top, Width:=Range("A1").Width, Height:=Range("A1").Height End Sub
Add multiple CheckBoxs on the Worksheet using VBA code
Please find the below two example macros, it will show you how to add multiple checkboxs on the Worksheet using VBA code.
- Click Developer Tab in the Excel menu bar.
- On the Code group, Click Visual Basic. Now, It will display VBA Editor window.
- Click Insert from the menu, Click Module and then add the below example codes into the module.
- Click run button or ‘F5’ to execute the above macro code.
- Go to active sheet to see the output for the above mentioned examples.
Example 1: Add Form Check Box Control
The below example show you how to add multiple form checkbox control on the worksheet.
Sub ActX_Add_Multiple_CheckBox_Ex1() 'Disable Screen Update Application.ScreenUpdating = False 'Variable Declaration Dim Rng As Range Dim ShtRng As Range Dim WrkSht As Worksheet Dim i As Integer 'Variable Initialization i = 1 Set ShtRng = Application.Selection Set ShtRng = Application.InputBox("Range", "Analysistabs", ShtRng.Address, Type:=8) Set WrkSht = Sheets(“Sheet3”) For Each Rng In ShtRng With WrkSht.CheckBoxes.Add(Left:=Rng.Left, Top:=Rng.Top, Width:=Rng.Width, Height:=Rng.Height).Select With Selection .Characters.Text = Rng.Value .Caption = "" .Caption = "Check Box " & i i = i + 1 End With End With Next ShtRng.ClearContents ShtRng.Select 'Enable Screen Update Application.ScreenUpdating = True End Sub
Example 2: Add ActiveX Check Box Control
The below example show you how to add multiple ActiveX checkbox control on the worksheet.
Sub ActX_Add_Multiple_CheckBox_Ex2() 'Disable Screen Update Application.ScreenUpdating = False 'Variable Declaration Dim Rng As Range Dim ShtRng As Range Dim WrkSht As Worksheet Set ShtRng = Application.Selection Set ShtRng = Application.InputBox("Range", "Analysistabs", ShtRng.Address, Type:=8) Set WrkSht = Application.ActiveSheet For Each Rng In ShtRng WrkSht.OLEObjects.Add "Forms.CheckBox.1", Left:=Rng.Left, Top:=Rng.Top, Width:=Rng.Width, Height:=Rng.Height Next ShtRng.ClearContents ShtRng.Select 'Enable Screen Update Application.ScreenUpdating = True End Sub
Hi
I am a new VBA learner and I need your support to get E-mail when checkbox in tick.
We have E-mail subject line Example- 012 EBN4615 CA, so I need vba help to get this mailo according to last two corrector (CA)
and if in checkbox CA is unchecked then user will not get this mail. please help me I am in big need of your help?
.
Hello, Thank you for the tutorial. I am new to VBA and have a question on how to code the checkboxes I’m working on. I have sheet 1 with checkboxes on it and on sheet 2 I have a list of sentences in different cells. what I want to happen is when a checkbox is checked on sheet 1 I want it to take a specific sentence from sheet 2 and put it in a “Notes” box on sheet 1. That way I can compile notes with a couple checks of some checkboxes. Any help would be greatly appreciated. Thanks