Populate a list box on the fly

[socomfort]

Hello experts,

I am trying to build a form on the fly and populate list box control on the form with the sheets names I store in an array as follows (partial code segment):

Dim objTempForm as Object
Dim ctlListBox As MSForms.ListBox
Dim intNumWS as Integer
Dim arrWSList() As Variant
Dim xlWB as Object
Dim strFQFN as string

'Store target workbook (fully qualified path).
Set xlWB = Application.Workbooks.Open(strFQFN)

'Create a blank form
   Set objTempForm = ThisWorkbook.VBProject. _
   VBComponents.Add(vbext_ct_MSForm)
           
    With objTempForm
        .Properties("Caption") = "User Selection Form"
        .Properties("Width") = 190

    End With

'Create a listbox on the form
    Set ctlListBox = objTempForm.Designer. _
    Controls.Add("Forms.ListBox.1")
    
    With ctlListBox
        .Top = 18
        .Left = 18
        .Width = 150
        .Height = 100

    End With
 
'Count number of sheets in target workbook.
intNumWS = xlWB.Worksheets.Count
   
'Store sheet names in sheets collection in array.
ReDim arrWSList(intNumWS, 0)

For intCounter = 1 To (intNumWS)      
[tab] arrWSList(intCounter - 1, 0) =  xlWB.Worksheets
[tab] (intCounter).Name   

Next intCounter

With ctlListBox
       .List = arrWSList

End With

My code also builds two command buttons on the fly using the .Designer class member. But when it comes time to show the form:

    VBA.UserForms.Add(strFormName).Show

The form is not populated with the list items. I am new to working with the VBProject, so bear with me. I am stumped . Any feedback you can provide is most appreciated.

thanks,

Ben

 

[Andrzejek]

Try:

For intCounter = 1 To (intNumWS)      
     arrWSList(intCounter - 1, 0) =  xlWB.Worksheets
     (intCounter).Name   
    [blue]ctlListBox.AddItem arrWSList(intCounter - 1, 0)[/blue]
Next intCounter

Have fun.

---- Andy

 

[SkipVought]

FYI, in Excel it is rarely necessary to use the AddItem method to control a Combobox or Listbox list fiil range.

Reference the list as a Named Range, and contol the size of the named range.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[combo]

There is no way to have predefined list (except of linking to range, as Skip pointed). If you decided to create Userform by code, add code by code too:

Dim txtFormOpenProc As String
Dim txtControlName As String
txtControlName = "CustomListBox"
txtFormOpenProc = "Private Sub UserForm_Initialize()"
txtFormOpenProc = txtFormOpenProc & vbCr & "For i = 1 To 10"
txtFormOpenProc = txtFormOpenProc & vbCr & "Me." & txtControlName & ".AddItem ""Item"" & i"
txtFormOpenProc = txtFormOpenProc & vbCr & "Next i"
txtFormOpenProc = txtFormOpenProc & vbCr & "End Sub"
With ThisWorkbook.VBProject.VBComponents.Add(vbext_ct_MSForm)
    With .Designer.Controls.Add("Forms.ListBox.1")
        .Name = txtControlName
    End With
    .CodeModule.InsertLines 1, txtFormOpenProc
End With

It will fill the list when the form is instantiated. BTW, I hope that you have a good reason to proceed this way.

combo