Add multiple checkbox in vb form on runtime

I have a spreadsheet with a list of values in column A that I want to
show up as checkboxes on a userform. The current code crashes when it
tries to add a caption to the checkbox. I get a runtime error "Could
not find the specified object"
The actual code would call a function to determine how many rows in
column A which will vary, and I'll add code to size the form and
manipulate the checkboxes to fit.

Private Sub UserForm_Initialize()
Dim iRow As Integer
Dim iLeft As Integer
Dim ctlCheckBox As Control
Dim sName As String
Dim iNumRows As Integer
Dim iTop As Integer

Worksheets("Hour Per Equipment").Activate
iTop = 10
For iRow = 3 To iNumRows
If Cells(iRow, "A") <> "" Then
Set ctlCheckBox = frmEqpDetails.Controls.Add("frmEqpDetails.VisChkbox.1", "cb" & iRow)
sName = "cb" & iRow

'I tried using ctlCheckBox sub for sName-same result

frmEqpDetails.Controls(sName).Caption = Cells(iRow, "A")
Me.Controls(sName).Left = iLeft
ctlCheckBox.Top = iTop
iTop = iTop + 10
End If
Next iRow
End Sub

Hi there,

Try the following code and see if it does what you need:

Please Login or Register  to view this content.

The highlighted values can be changed to suit your own worksheet and UserForm layout requirements.

Hope this helps - please let me know how you get on.

Regards,

Greg M

Hi MOHAMMESALMAN,

See the attached sample file that creates UserForm CheckBoxes dynamically. It also uses a Class Event Handler that can process all CheckBox Events, instead of having to add one for each CheckBox in the UserForm module.

Instructions for creating a Class Event Handler:
a. To prevent a compile error, in the VBA Editor add the following library reference:
Tools -> References -> Microsoft Forms 2.0 Object Library (Excel 2003 version)

b. Create Class ClassCheckBox
The following line is required at the top of the Class:
Public WithEvents CheckBoxGroup As MSForms.CheckBox
There are several built in events that can be selected.

c. Put the following line at the top of an ordinary Code module:
Public myCheckBoxes() As New ClassCheckBox

d. Put the following calls in UserForm_Initialize:
(1) - Call CreateUserForm1DynamicCheckBoxes 'To CREATE Dynamic (created at runtime) controls
(2) - Call GenerateUserForm1CheckBoxControlArray 'To assign CheckBoxes to the CheckBoxGroup
Controls in the CheckBoxGroup will have events handled by ClassCheckBox
NOTE: If a UserForm Event exists, it will still be activated before the Class Event.

Class ClassCheckBox code. NOTE if the Class name is changed, other items in the code must be changed also.

Please Login or Register  to view this content.

UserForm1 module code:

Please Login or Register  to view this content.

In an ordinary code module such as Module1:

Please Login or Register  to view this content.

Lewis

Hi Greg,

How can I use this code in multipage form.

Please find the attached file.

The problem is that The code which you had shared is working properly. But, with multipage form its not as per the requirement.

Hi again,

Take a look at the attached workbook - it shows how to implement your requirements when a Multipage is involved. It takes Test Categories stored in a named range and assigns them to the Multipage tabs:

Please Login or Register  to view this content.

Hope this helps - please let me know how you get on.

Regards,

Greg M