add multiple checkboxes to userform at runtime

[gkelle]

I have a spreadsheet with a list of values in column Y 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 Y 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

Worksheets("ModelList").Activate
iTop = 10
For iRow = 2 To iNumRows
If Cells(iRow, "Y") <> "" Then
Set ctlCheckBox =
frmEditBom.Controls.Add("Forms.CheckBox.1", "cb" & iRow)
sName = "cb" & iRow

'I tried using ctlCheckBox sub for sName-same result

frmEditBom! sName.Caption = Cells(iRow, "Y")
frmEditBom!sName.Left = iLeft
frmEditBom!sName.Top = iTop
iTop = iTop + 10
End If
Next iRow
End Sub

 

[Tom Ogilvy]

Put as many checkboxes on the form as you will need in design mode. Then,
when run, when the function determines the number of rows, make the
remaining checkboxes invisible and adjust the height of the userform as you
would do now. This is much simpler and will avoid the problems you are
having.

 

[Andy Pope]

Hi gkelle,

You can do one of the following;
use the control object ctlCheckbox directly
use the .Controls property of frmEditBom
or use the Me object, which will refer to the userform.

Also you need to have a value in the variable iNumRows that is at least
2 otherwise the loop will not be processed.

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("Sheet1").Activate
iTop = 10
For iRow = 2 To iNumRows
If Cells(iRow, "Y") <> "" Then
Set ctlCheckBox = frmEditBom.Controls.Add("Forms.CheckBox.1",
"cb" & iRow)
sName = "cb" & iRow

'I tried using ctlCheckBox sub for sName-same result

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

I have a spreadsheet with a list of values in column Y 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 Y 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

Worksheets("ModelList").Activate
iTop = 10
For iRow = 2 To iNumRows
If Cells(iRow, "Y") <> "" Then
Set ctlCheckBox =
frmEditBom.Controls.Add("Forms.CheckBox.1", "cb" & iRow)
sName = "cb" & iRow

'I tried using ctlCheckBox sub for sName-same result

frmEditBom! sName.Caption = Cells(iRow, "Y")
frmEditBom!sName.Left = iLeft
frmEditBom!sName.Top = iTop
iTop = iTop + 10
End If
Next iRow
End Sub

--

Cheers
Andy

http://www.andypope.info