I am dynamically generating a VBA form. One of the items on the form is a list box, when attempting to set the width on the list box
never works regardless of where I try. The code looks like
Public Sub BuildForm()
Dim ws As Worksheet
Dim row As Integer
Dim topSpaceAccum As Integer
Dim colIndex As Integer
Dim dropAns() As String
Dim ctl As control
Dim lbl(0 To SimpleQuestionCount) As control
Dim usrCntl(0 To SimpleQuestionCount) As control
Dim helper As LabelEventHelper
Dim val As Integer
Set ws = Sheets(SimpleQuestionWorkSheetIndex)
topSpaceAccum = TopStartPos
For row = SimpleQuestionDataRowStart To SimpleQuestionCount + 1
colIndex = SimpleQuestionDataColStart
Set lbl(row - 2) = Frame1.controls.Add("Forms.Label.1", "lbl" & row)
Set helpers(row - 2) = New LabelEventHelper
Set helpers(row - 2).eLabel = lbl(row - 2)
helpers(row - 2).message = ws.Cells(row, SimpleQuestionsTooTipTextCol).Value
helpers(row - 2).showTip = ws.Cells(row, SimpleQuestionsShowToolTip).Value
lbl(row - 2).Caption = ws.Cells(row, SimpleQuestionsLabelQuestionColIndex).Value
lbl(row - 2).Left = LeftStartPos
lbl(row - 2).Top = topSpaceAccum
lbl(row - 2).Width = QuestionWidth
If ws.Cells(row, ControlTypeColumnIndex) = ControlTypeComboBoxIndexValue Then
Set usrCntl(row - 2) = Frame1.controls.Add("Forms.ComboBox.1", "ctl" & row)
usrCntl(row - 2).Width = SelectCntrlWidth
ElseIf ws.Cells(row, ControlTypeColumnIndex) = ControlTypeListBoxIndexValue Then
Set usrCntl(row - 2) = Frame1.controls.Add("Forms.ListBox.1", "ctl" & row)
usrCntl(row - 2).MultiSelect = 2
usrCntl(row - 2).Width = SelectCntrlWidth
ElseIf ws.Cells(row, ControlTypeColumnIndex) = ControlTypeTextBoxIndexValue Then
Set usrCntl(row - 2) = Frame1.controls.Add("Forms.TextBox.1", "ctl" & row)
usrCntl(row - 2).Width = SelectCntrlWidth
End If
With usrCntl(row - 2)
If ws.Cells(row, ControlTypeColumnIndex) <> ControlTypeTextBoxIndexValue Then
dropAns = Split(ws.Cells(row, SimpleQuestionsAnswerColIndex).Value, ",")
For Each ans In dropAns
.AddItem (CStr(ans))
Next ans
End If
.Left = LeftLeftStartPos
.Top = topSpaceAccum
.Width = SelectCntrlWidth
End With
topSpaceAccum = (topSpaceAccum + TopSpacer)
Next row
End Sub
If you consider:
Set usrCntl(row - 2) = Frame1.controls.Add("Forms.ListBox.1", "ctl" & row)
you'll see I create a list box and add it to the form. Further down you'll see I populate the list box, after populating the list box, I attempt to set the width again. The width
always reverts back to the default. The only time I can successfully set the width is if I step through the code, Or if I place a messagebox immediately after I set the width.
Me.Repaint, does not work, nor does, Requery. Any suggestion would be helpful.