combo/Listbox population using VBA in excel (office 97)

Topic

New Reply

I need to add a list box or combo list to a form in excel but I need to populate it with data from the an excel sheet using VBA.
I have created a listbox on my form but I can not reference the listbox from by vb code. I suspect I can only reference it from the userform itself but I have tried creating the userform_initialize() function and put my code to populate the the list box inside, but it doesn’t seem to execute the initialize function…what am I doing wrong?

Bootlegger

Info:
Office 97

Reply | Quote

Replies

Okay… I’m no expert with Excel yet but … did you create the listbox using the Forms toolbar or the Control ToolBox toolbar?? … If you create the listbox using the Control Toolbox you’ll be able to reference it in code… I’ve done it quite a few times…

Reply | Quote

Okay… I’m no expert with Excel yet but … did you create the listbox using the Forms toolbar or the Control ToolBox toolbar?? … If you create the listbox using the Control Toolbox you’ll be able to reference it in code… I’ve done it quite a few times…

Reply | Quote

UserForm_Initialize() should run when your code module issues a UserForm_name.Show method call.

Then, there are two potential issues: (1) successfully accessing the data from the worksheet from inside the form’s code module, and (2) successfully moving that data into the listbox. Can you play around with the code and confirm which part is working/not working?

Reply | Quote

UserForm_Initialize() should run when your code module issues a UserForm_name.Show method call.

Then, there are two potential issues: (1) successfully accessing the data from the worksheet from inside the form’s code module, and (2) successfully moving that data into the listbox. Can you play around with the code and confirm which part is working/not working?

Reply | Quote

Where did you put the UserForm_Initilize? It does not go into a normal module, it must be in the module that is behind the userform (right click on the form and select View Code to see this module. Also, the initilize event routing must be a Sub, not a Function.

If the above does not solve your problem, could you upload a sample workbook that shows what you are trying to do (just containing the user form, the initilize routine, and some dummy data that would be loaded into the listbox).

Reply | Quote

Thanks for everyones help.
After reading everyones responses I double checked where my initialize routine was – it was under the General section, not my userform so I moved it to the correct location and added my populate code in and it worked.
Thanks again.
Bootlegger

Reply | Quote

Probably too late now, but you really don’t need any code: try using the RowSource and the ControlSource property of the combo/list box. Set RowSource to a range & the box will populate with these values. Set ControlSource to a single cell and it will be used for the initial value of the box & will be set to the final selection after the form is closed. For example, if A1:A3 = {A,B,C}, then if you enter A1:A3 as the value for the RowSource property, you will see A, B and C as the choices in the box. Since this is an important range, I always use a named range and enter the range name in the RowSource property. HTH –Sam

Reply | Quote

Thanks for the update however what I needed to achieve was a populated drop down list from unique contents from a list of 39000 records. Your idea would have produced a very long drop down list!!
With the code I have managed to create a unique list first by sorting the column in aphabetical order then scanning through for changes between row and row+1 and if different then add row contents into the drop down list. There may also be a way of doing this using the row/control source method you mention, but I don’t know what it is!
Bootlegger

Reply | Quote

It is a little easier to use an Advanced Filter to create a list of unique items. Here is the code that I would use. I also have attached a workbook with the code/form. I did this in XL/XP but saved it as XL97. I cannot test it on XL97 for a couple of days: getting a new server for that machine. Let me know if you have problems. HTH –Sam

Option Explicit

Private Sub UserForm_Initialize()
' List used to populate combobox is in boxCOLUMN
Const boxSHEET As String = "Sheet1"
Const boxCOLUMN As String = "C:C"
Dim lngUnique As Long

Dim c As Range
    With Worksheets(boxSHEET)
'       Find a spare column
        With .UsedRange
            lngUnique = .Column + .Columns.Count + 2
        End With
'       Get Big List
        With .Columns(boxCOLUMN)
'           Filter for Unique
            .AdvancedFilter Action:=xlFilterCopy, _
                CopyToRange:=Cells(1, lngUnique), Unique:=True
        End With
'       Sort unique list
        .Columns(lngUnique).Sort key1:=.Cells(2, lngUnique), Header:=xlYes
'       Populate with unique list
        ComboBox1.Clear
        For Each c In .Range(.Cells(2, lngUnique), _
                        .Cells(2, lngUnique).End(xlDown)).Cells
            ComboBox1.AddItem c.Text
        Next c
        .Columns(lngUnique).Clear
    End With
End Sub

Reply | Quote

hmm….interesting but heres how I did it……

Private Sub UserForm_Initialize()
    ComboBox1.AddItem ""
    Cells.Select ' sort array by base, a/c, crew type
    Selection.Sort Key1:=Range("E1"), Order1:=xlAscending, Header:= _
        xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    match1 = Range("E1").Text
    For f = 1 To 65535
        If Range("E" & f).Text  match1 Then
            ComboBox1.AddItem match1
            match1 = Range("E" & f).Text
        End If
    Next f

Quick and dirty and probably likely to crash somewhere along the line but it seems to work and appears to be a little less complex.
Thanks all the same.
Bootlegger

Reply | Quote

Yours is less code, so it’s better. Less to debug. Mine solution might be better if you didn’t want to change the table order. Just three suggestions to make yours faster and easier to debug: define the variables, don’t use select/selection, and just cover the actual size of the table.

Option Explicit

Private Sub UserForm_Initialize()
Dim match1 As String, f As Long
    ComboBox1.Clear
    ' sort array by base, a/c, crew type
    ActiveSheet.UsedRange.Sort Key1:=Range("E1"), Order1:=xlAscending, Header:= _
        xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    match1 = Range("E1").Text
    For f = 1 To ActiveSheet.UsedRange.Rows.Count + 1
        If Cells(f, 5).Text  match1 Then
            ComboBox1.AddItem match1
            match1 = Cells(f, 5).Text
        End If
    Next f
End Sub

Reply | Quote

Thanks once again for the update – very interesting.
As you can tell I am still learning VBA – this is only the 3rd thing i’ve ever written in VBA so I am unsure on all the keywords and their functions, but you’ve just taught me quite a bit in that little snippet!
Regards
Bootlegger

Reply | Quote

Thanks once again for the update – very interesting.
As you can tell I am still learning VBA – this is only the 3rd thing i’ve ever written in VBA so I am unsure on all the keywords and their functions, but you’ve just taught me quite a bit in that little snippet!
Regards
Bootlegger

Reply | Quote

Yours is less code, so it’s better. Less to debug. Mine solution might be better if you didn’t want to change the table order. Just three suggestions to make yours faster and easier to debug: define the variables, don’t use select/selection, and just cover the actual size of the table.

Option Explicit

Private Sub UserForm_Initialize()
Dim match1 As String, f As Long
    ComboBox1.Clear
    ' sort array by base, a/c, crew type
    ActiveSheet.UsedRange.Sort Key1:=Range("E1"), Order1:=xlAscending, Header:= _
        xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    match1 = Range("E1").Text
    For f = 1 To ActiveSheet.UsedRange.Rows.Count + 1
        If Cells(f, 5).Text  match1 Then
            ComboBox1.AddItem match1
            match1 = Cells(f, 5).Text
        End If
    Next f
End Sub

Reply | Quote

hmm….interesting but heres how I did it……

Private Sub UserForm_Initialize()
    ComboBox1.AddItem ""
    Cells.Select ' sort array by base, a/c, crew type
    Selection.Sort Key1:=Range("E1"), Order1:=xlAscending, Header:= _
        xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    match1 = Range("E1").Text
    For f = 1 To 65535
        If Range("E" & f).Text  match1 Then
            ComboBox1.AddItem match1
            match1 = Range("E" & f).Text
        End If
    Next f

Quick and dirty and probably likely to crash somewhere along the line but it seems to work and appears to be a little less complex.
Thanks all the same.
Bootlegger

Reply | Quote

It is a little easier to use an Advanced Filter to create a list of unique items. Here is the code that I would use. I also have attached a workbook with the code/form. I did this in XL/XP but saved it as XL97. I cannot test it on XL97 for a couple of days: getting a new server for that machine. Let me know if you have problems. HTH –Sam

Option Explicit

Private Sub UserForm_Initialize()
' List used to populate combobox is in boxCOLUMN
Const boxSHEET As String = "Sheet1"
Const boxCOLUMN As String = "C:C"
Dim lngUnique As Long

Dim c As Range
    With Worksheets(boxSHEET)
'       Find a spare column
        With .UsedRange
            lngUnique = .Column + .Columns.Count + 2
        End With
'       Get Big List
        With .Columns(boxCOLUMN)
'           Filter for Unique
            .AdvancedFilter Action:=xlFilterCopy, _
                CopyToRange:=Cells(1, lngUnique), Unique:=True
        End With
'       Sort unique list
        .Columns(lngUnique).Sort key1:=.Cells(2, lngUnique), Header:=xlYes
'       Populate with unique list
        ComboBox1.Clear
        For Each c In .Range(.Cells(2, lngUnique), _
                        .Cells(2, lngUnique).End(xlDown)).Cells
            ComboBox1.AddItem c.Text
        Next c
        .Columns(lngUnique).Clear
    End With
End Sub

Reply | Quote

Thanks for the update however what I needed to achieve was a populated drop down list from unique contents from a list of 39000 records. Your idea would have produced a very long drop down list!!
With the code I have managed to create a unique list first by sorting the column in aphabetical order then scanning through for changes between row and row+1 and if different then add row contents into the drop down list. There may also be a way of doing this using the row/control source method you mention, but I don’t know what it is!
Bootlegger

Reply | Quote

Probably too late now, but you really don’t need any code: try using the RowSource and the ControlSource property of the combo/list box. Set RowSource to a range & the box will populate with these values. Set ControlSource to a single cell and it will be used for the initial value of the box & will be set to the final selection after the form is closed. For example, if A1:A3 = {A,B,C}, then if you enter A1:A3 as the value for the RowSource property, you will see A, B and C as the choices in the box. Since this is an important range, I always use a named range and enter the range name in the RowSource property. HTH –Sam

Reply | Quote

Thanks for everyones help.
After reading everyones responses I double checked where my initialize routine was – it was under the General section, not my userform so I moved it to the correct location and added my populate code in and it worked.
Thanks again.
Bootlegger

Reply | Quote

Where did you put the UserForm_Initilize? It does not go into a normal module, it must be in the module that is behind the userform (right click on the form and select View Code to see this module. Also, the initilize event routing must be a Sub, not a Function.

If the above does not solve your problem, could you upload a sample workbook that shows what you are trying to do (just containing the user form, the initilize routine, and some dummy data that would be loaded into the listbox).

Reply | Quote