CODEWITHSUNDEEP
excelvba
mongoose00318
mongoose00318

Reputation: 131

Creating a dynamic function for creating checkboxes

I'm trying to create a dynamic function that I can pass a range to and it will generate the checkbox in the cells in that range. I have it generating checkboxes but I can't figure out how to change it to use a dynamic range that is passed as a parameter when I call the sub.

How would I change my sub to be able to do this? Here is my code so far...

Public Sub chkPopulateFields(R As Range)
    Dim chk As Variant
    Dim t As Range
    Dim i As Integer

    Application.ScreenUpdating = False

    With Me.Worksheets(1)
        .CheckBoxes.Delete
        For i = 4 To 10
            Set t = .Range(Cells(i, 3), Cells(i, 3))
            Set chk = .CheckBoxes.Add(t.Left, t.Top, t.Width, t.Height)
        Next
    End With

    Application.ScreenUpdating = True
End Sub

Upvotes: 0

Views: 33

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149277

I'm trying to create a dynamic function that I can pass a range to and it will generate the checkbox in the cells in that range.

You can use For Each to loop through each cell in a range. Also I added one more paramenter ws As Worksheet so that you can target the relevant worksheet.

Is this what you are trying?

Option Explicit

Sub Sample()
    chkPopulateFields Range("A1:C4"), Sheet1
End Sub

Public Sub chkPopulateFields(R As Range, ws As Worksheet)
    Dim rng As Range
    Dim chk As Object

    With ws
        .CheckBoxes.Delete
        For Each rng In R
            Set chk = .CheckBoxes.Add(rng.Left, rng.Top, rng.Width, rng.Height)
        Next
    End With
End Sub

enter image description here

Upvotes: 2

Related Questions