Results 1 to 11 of 11
  1. #1
    New Lounger
    Join Date
    Nov 2003
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  2. #2
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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...

  3. #3
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

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

    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?

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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).
    Legare Coleman

  5. #5
    New Lounger
    Join Date
    Nov 2003
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  6. #6
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

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

    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
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  7. #7
    New Lounger
    Join Date
    Nov 2003
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  8. #8
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

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

    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
    <pre>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</pre>

    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  9. #9
    New Lounger
    Join Date
    Nov 2003
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    hmm....interesting but heres how I did it......
    <pre>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
    </pre>

    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

  10. #10
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

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

    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.
    <pre>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</pre>

    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  11. #11
    New Lounger
    Join Date
    Nov 2003
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •