Add CheckBox on Worksheet or UserForm using VBA

Home/VBA/Add CheckBox on Worksheet or UserForm using VBA

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.

PREMIUM TEMPLATES LIMITED TIME OFFER

ON SALE80% OFF

50+ Project Management Templates Pack
Excel PowerPoint Word

Advanced Project Plan & Portfolio Template
Excel Template

Business Presentations Templates Pack
PowerPoint Slides

20+ Excel Project Management Pack
Excel Templates

20+ PowerPoint Project Management Pack
PowerPoint Templates

10+ MS Word Project Management Pack
Word Templates


In this Topic:

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.

  1. Click Developer Tab in the Excel menu bar.
  2. On the Code group, Click Visual Basic. Now, VBA Editor window will be displayed.
  3. Click Insert from the menu, Click Module and then add the below example macros into the module.
  4. 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
    
  5. Click run button or ‘F5’ to execute the above macro code.
  6. Go to Sheet3 to see the output for the above mentioned examples.

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.

  1. Click Developer Tab in the Excel menu bar.
  2. On the Code group, Click Visual Basic. Now, It will display VBA Editor window.
  3. Click Insert from the menu, Click Module and then add the below example codes into the module.
  4. 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
    
  5. Click run button or ‘F5’ to execute the above macro code.
  6. Go to active sheet to see the output for the above mentioned examples.


Related Resource External VBA Reference
Excel VBA Reference Project Management Reference
VBA Reference:
Excel Reference:
By |April 12th, 2015|VBA|2 Comments

About the Author:

Excel VBA Developer having around 8 years of experience in using Excel and VBA for automating the daily tasks, reports generation and dashboards preparation. Valli is sharing to helps us automating daily tasks.

2 Comments

  1. Bhupender May 22, 2016 at 2:01 AM - Reply

    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?
    .

  2. Eric March 1, 2017 at 3:03 AM - Reply

    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

Leave A Comment


Related pages


vbcriticalexcel 2007 formulas tutorialexcel 2010 advanced filterexcel graphing tutorialhiding a column in excelvba saveas methodvba dashboardexcel meeting minutes templateexcel vba dirvba formatexcel vba rgbcharting in excelexcel vba create folderexcel vba array lengthxml to excel conversionalphabetical order in excel 2007vba multiplicationnested if excelunprotecting an excel worksheetms excel vlookup formulavba range clearunprotect sheet excelvlookup and hlookup in excelvba istexthow to create pivot chart in excel 2007merge cells in excel 2013cells in a spreadsheetsample project plan template excel2007 excel templatesadd developer tab to ribbon word 2013unshare workbookexcel 2010 userform templatesexcel workbook exampleshow to cut and paste in excelhow to unprotect an excel sheetexcel macro sheetsactivecell rangeexcel vba string manipulationhow to create check box in excelinsert tick box into exceladvance excel tutorialexcel vba savevba blank cellvba chartshow to adjust column width in excelvb6 carriage returnexcel protect workbookconversion of excel to xmlexcel 2010 multiple if statementsvb command buttonvba codes excelgreater than formula exceldelete duplicate lines in excelvlookupfunctionadvanced excel macrovisual basic gotoprogram in vbaiferror function exceldcl databasehow to build dashboards in excelcustomer management excel templateactivesheet unprotect password vbadynamic range vbafso vbamultiple conditions in excelmacro enabled excel filehow to expand rows in excelado excel vbaautofit excel 2010how do you refresh a pivot tablepassword lock excel