Creating a Dynamic CheckBox Variable

Question

I created a userform that is populated with checkboxes based off of the cell values on multiple worksheets. I named the checkboxes based off of the cell values (none of them are the same) but the number of cells will change over time so I wanted to have a code populate the userform rather than manually put each checkbox in. I want to be able to call on specific cell value information if its checkbox is clicked but my code isn't working. I think I need to create a dynamic checkbox variable that changes each time. I'm currently unable to call on any of the checkboxes except the very last one.

    Public chkBox As MSForms.CheckBox

    Public Sub UserForm_Initialize()

    MemNumCombo.Clear

    Dim o As Long
    Dim chkL As Double
    Dim chkT As Double
    Dim chkH As Double
    Dim chkW As Double

    chkL = 125
    chkT = 5
    chkH = 15
    chkW = 80

    o = 2
    Do Until Worksheets("Operations").Cells(o, 1).Value = "Division:"

        Set chkBox = Me.Controls.Add("Forms.CheckBox.1", Worksheets("Operations").Cells(o, 1).Value & Worksheets("Operations").Cells(o, 3).Value & "Check")

        chkBox.Caption = Worksheets("Operations").Cells(o, 1).Value & " " & Worksheets("Operations").Cells(o, 3).Value

        chkBox.Left = chkL
        chkBox.Top = chkT + (o - 1) * 20
        chkBox.Height = chkH
        chkBox.Width = chkW

        o = o + 1
    Loop

'...
   End Sub

I know when the code runs the chkBox's name is correct but I can't call on the chkBox by it's name later on. When the code is done running, I can only call on the last chkBox.

score 0 · Accepted Answer · answered Aug 01 '19 at 17:28

(Can't) call the chkBox by it's name...

You set the chkBox object to memory in a loop thus overwriting one check box by the next one; referring to the variable alone via chkBox or chkBox.Value somewhere after in code will return (the set object itself or e.g. the value of) the last checkbox.

Calling CheckBoxes via the Controls Collection

Using the Controls.Add method via Set chkBox = Me.Controls.Add("Forms.CheckBox.1", Worksheets("Operations").Cells(o, 1).Value & Worksheets("Operations").Cells(o, 3).Value & "Check") you already defined names (concatenated from two columns in worksheet "Operations" plus the Suffix "Check").

You can call a checkbox by one of these names via the Controls collection; assuming one of your check boxes actually got named "XY47Check" you address it via Me.Controls("XY47Check").

Listing Checkbox names

A very simple and helpful approach consists in assigning all checkbox names to an Array you can address by indices in the predefined sheet order.

Simply [1] declare a variant array Dim ChkNames() in the declaration head of your userform code module, [2] add the calling code line

    FillChkNames

to your Userform_Initialize procedure as well as [3] the following helper procedure Sub FillChkNames() to your Userform module:

Private Sub FillChkNames()
' Purpose: populate array ChkNames at userform module level
ReDim ChkNames(Me.Controls.Count - 1)
Dim ctl As MSForms.Control, i&
For Each ctl In Me.Controls
    If TypeName(ctl) = "CheckBox" Then ChkNames(i) = ctl.Name: i = i + 1
    Debug.Print ctl.Name, ctl.Value, TypeName(ctl)
Next ctl
ReDim Preserve ChkNames(i - 1)

End Sub

This allows you to get all checkboxes by name

Some example calls anywhere within the userform code module

' ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
' List all combo names in array
' ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  Debug.Print Join(ChkNames, "|")
' List all combo names plus values
  Dim chkName As Variant
  For Each chkName In ChkNames
      Debug.Print chkName, Me.Controls(chkName)
  Next chkName

' ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
' List single value - e.g. the third check box in array
' ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  Dim myNum As Long: myNum = 2                          ' zero-based index 2 refers to third item in array
  If myNum <= UBound(ChkNames) Then
      Debug.Print "Index no " & myNum & " ~> Value: " & Me.Controls(ChkNames(myNum))
  Else
      Debug.Print "Invalid index :-(" & myNum & ")"
  End If

Advanced methods

Alternatively you can use a collection of objects instead of a single object and study other numerous examples at SO using classes, e.g. at assign event handlers to controls on userform created dynamically

Creating a Dynamic CheckBox Variable

1 Answers1