Creating a dynamic function for creating checkboxes

Question

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

Siddharth Rout · Accepted Answer · 2020-01-18 15:37:41Z

2

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

edited Jan 18, 2020 at 15:37

answered Jan 18, 2020 at 15:31

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

EEM Over a year ago

You could have used the Worksheet property of the range object (i.e. R.Worksheet)

Siddharth Rout Over a year ago

True. I could have. But then you would have to fully qualify Range("A1:C4") else it will refer to the activesheet. But honestly R.Worksheet did not even cross my mind :)

EEM Over a year ago

It's very convenient as it would have allowed for something like: With rng ` Set chk = .Worksheet.CheckBoxes.Add(.Left, .Top, .Width, .Height)` End With. Anyhow, your answer is very efficient, as always.

Siddharth Rout Over a year ago

Yes it would have been. but unfortunately it also can cause lot of confusion. If you want to use R.Worksheet then fully qualify the range. For example, chkPopulateFields Sheet1.Range("A1:C4") In this case we can get rid of ws As Worksheet and then replace With ws with With R.Worksheet :)

EEM Over a year ago

Sorry forgot to mention that the 1With ws` would be replaced by With R (excluding the .Worksheet)

Collectives™ on Stack Overflow

Creating a dynamic function for creating checkboxes

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related