insert check box to a particular cell through vba macro

Question

I would like to insert the check box in particular cell through macro. For example: On click of a command button i should be able to add the check box to A1 cell.

Sheets("Pipeline Products").Range("O" & i & ":AG" & i).Select 
ActiveSheet.CheckBoxes.Add(4, 14.5, 72, 17.25).Select 

With Selection 
    .Caption = "" 
    .Value = xlOff '
    .LinkedCell = "C" & ToRow     
    .Display3DShading = False 
End With

Kazimierz Jawor · Accepted Answer · 2013-06-07T21:12:17.940

9

This simple line allows you to add CheckBox to cell A1 and set width and height accordingly:

ActiveSheet.OLEObjects.Add "Forms.CheckBox.1", Left:=Range("A1").Left, Top:=Range("A1").Top, Width:=Range("A1").Width, Height:=Range("A1").Height

You can easily add it to CommandButton this way:

Private Sub CommandButton1_Click()

    ActiveSheet.OLEObjects.Add "Forms.CheckBox.1", Left:=Range("A1").Left, Top:=Range("A1").Top, Width:=Range("A1").Width, Height:=Range("A1").Height

End Sub

Edit Your code improved...

You simply need to add loop to insert checkboxes into several cells:

Sub YourCode_Improvment()

    Dim i
    '
    For i = 1 To 10 'cells from 1st to 10th

    ActiveSheet.CheckBoxes.Add(Cells(i, "A").Left, _
                                Cells(i, "A").Top, _
                                72, 17.25).Select
    With Selection
        .Caption = ""
        .Value = xlOff '
        .LinkedCell = "C" & i
        .Display3DShading = False
    End With
    Next
End Sub

Change this code accordingly, if needed.

edited Jun 07 '13 at 21:12

answered Jun 07 '13 at 20:25

Kazimierz Jawor

18,861
7
35
55

Thank you, However, if I would want to add these check boxes in a series of cells then how to go about. I have written the below code. It is not throwing any error but we are not getting the desired result. Please advice – user2457968 Jun 07 '13 at 20:52
Sheets("Pipeline Products").Range("O" & i & ":AG" & i).Select ActiveSheet.CheckBoxes.Add(4, 14.5, 72, 17.25).Select With Selection .Caption = "" .Value = xlOff '.LinkedCell = "C" & ToRow .Display3DShading = False End With – user2457968 Jun 07 '13 at 20:53
add this code to your question while it's difficult to read from comment... I proposed you different code. So, what is 'desired result' then? – Kazimierz Jawor Jun 07 '13 at 21:01
We are getting the check box only in cell "A1". I wnat these check box to appear in the given cell range. For Example: From A1 to A10. – user2457968 Jun 07 '13 at 21:07
ok. Could you please help me in doing that? Also, only the check box name should not appear. – user2457968 Jun 07 '13 at 21:18

score 2 · Answer 2 · answered Mar 06 '20 at 13:54

Slightly upgraded code in the top comment. Simply select a range and run it, it'll fill all selected cells with checkboxes:

Sub InsertCheckboxes()
    Dim c As Range

    For Each c In Selection
        Dim cb As CheckBox
        Set cb = ActiveSheet.CheckBoxes.Add(c.Left, _
                                    c.Top, _
                                    c.Width, _
                                    c.Height)
        With cb
            .Caption = ""
            .Value = xlOff
            .LinkedCell = c.Address
            .Display3DShading = False
        End With
    Next
End Sub

+ Adding, if required, replace line #4 with an actual range i.e: `For Each c In Range("P20:P21")` - this worked for what I needed to do. — Neil P., Sep 07 '22 at 13:22

score 0 · Answer 3 · answered Jun 07 '13 at 21:20

You can use a For Each loop to add the check boxes.

Dim i as Integer
Dim cel As Range
i = 10
For Each cel In Sheets("Pipeline Products").Range("O" & i & ":AG" & i)
    ActiveSheet.OLEObjects.Add "Forms.CheckBox.1", Left:=cel.Left, Top:=cel.Top, Width:=cel.Width, Height:=cel.Height
Next

Hope this helps.

insert check box to a particular cell through vba macro

3 Answers3

Linked