1

Here is a bit of background on what I'm trying to do: I'm creating a userform to track Inventory items and prices, using checkboxes in a multipage object. The clerk checks off everything put into an order and uses a submit button, which will take some actions.

In order for the project not to require a coding person every time Inventory items change, the checkboxes are being dynamically generated when the userform is activated, from cell values on an Inventory sheet. The clerks just adjust the Inventory sheet and the form automatically adjusts for them.

This is my code to dynamically create all the checkboxes (currently this form can accommodate up to 160 possible checkboxes), in case this is effecting my issue (side note, each tab on the multipage has a frame on it, and all checkboxes are within the frame, so I could change background colors, the frame in this example being titled "frmreg"):

    Sub StoreFrmRegCheckboxGenerator()

    'Works with the store userform

    Dim curColumn   As Long
    Dim LastRow     As Long
    Dim i           As Long
    Dim chkBox      As msforms.CheckBox

    'This sub dynamically creates checkboxes on the Regular Items tab based 
    'on values in Column A of the Inventory sheet

    curColumn = 1 'Set your column index here
    LastRow = Worksheets("Inventory").Cells(Rows.Count, curColumn).End(xlUp).Row

    For i = 2 To 9
        If Worksheets("Inventory").Cells(i, curColumn).Value <> "" Then
            Set chkBox = store.frmreg.Controls.Add("Forms.CheckBox.1", "CheckBox_" & i)
            chkBox.Caption = Worksheets("Inventory").Cells(i,         curColumn).Value & " - $" & Worksheets("Inventory").Cells(i, curColumn).Offset(0, 1).Value
            chkBox.AutoSize = True
            chkBox.WordWrap = True
            chkBox.Left = 5
            chkBox.Top = 1 + ((i - 1) * 25)
        End If
    Next i

    'Cut some code out here identical to this previous section, but for the rest of the cells in column A up to Row 33, in blocks of 8

    End Sub

The above code is in the Userform_Initialize sub, and it works perfectly.

However, since the number of checkboxes is not static, and can be as many as 160, I'm trying to write one sub to take the same set of actions any time any checkbox is clicked.

The closest solution I've found is from this question: Excel Macro Userform - single code handling multiple checkboxes, from sous2817.

Here is his code that I'm trying to use:

In a new class module:

    Option Explicit

    Public WithEvents aCheckBox As msforms.CheckBox

    Private Sub aCheckBox_Click()
         MsgBox aCheckBox.Name & " was clicked" & vbCrLf & vbCrLf & _
            "Its Checked State is currently " & aCheckBox.Value, vbInformation + vbOKOnly, _
            "Check Box # & State"
    End Sub

The "store" userform, at the top, right under Option Explicit:

    Dim myCheckBoxes() As clsUFCheckBox

At the bottom of the Userform_Initialize sub, AFTER I call the all the subs that dynamically create all the checkboxes:

Dim ctl As Object, pointer As Long
ReDim myCheckBoxes(1 To Me.Controls.Count)

For Each ctl In Me.Controls
    If TypeName(ctl) = "CheckBox" Then
        pointer = pointer + 1
        Set myCheckBoxes(pointer) = New clsUFCheckBox
        Set myCheckBoxes(pointer).aCheckBox = ctl
    End If
Next ctl

ReDim Preserve myCheckBoxes(1 To pointer)

When I try to open the userform I get this error:

"Compile Error: User-defined type not defined"

Pointing to this line:

Dim myCheckBoxes() As clsUFCheckBox

Am I missing a library reference? I haven't been able to figure this out.

2
  • 2
    Is clsUFCheckBox then name of your class ? Your code works for me. Commented Jan 19, 2017 at 22:21
  • Holy moly that did it. I cannot believe I didn't realize that, it was still called Class1. Thank you so much! Commented Jan 19, 2017 at 22:27

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.