CODEWITHSUNDEEP
excelvba
user2457968
user2457968

Reputation: 109

insert check box to a particular cell through vba macro

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

Upvotes: 3

Views: 69904

Answers (3)

Suobig
Suobig

Reputation: 61

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

Upvotes: 4

raybiss
raybiss

Reputation: 401

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.

Upvotes: 0

Kazimierz Jawor
Kazimierz Jawor

Reputation: 19067

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.

Upvotes: 11

Related Questions