Results 1 to 5 of 5
  1. #1
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    fill listbox and conbobox (2000 sr-1)

    i have this sheet and form...
    how to fill the combobox with data in column K of sheet, and when i select one item in combobox fill the list box with related value in L, M and O...
    Example:
    I select from cmbobox=CORPORATE
    Fill the list box with:
    13/11/2005 31/12/2006 20/10/2006
    11/11/2005 31/12/2006 20/10/2006

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: fill listbox and conbobox (2000 sr-1)

    Get some ideas from here:

    <!post=ComboBox Selection,587054>ComboBox Selection<!/post>
    Jerry

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: fill listbox and conbobox (2000 sr-1)

    You can fill the combo box in the UserForm_Initialize event or in the UserForm_Activate event.
    Loop through the filled cells in column K, checking for unique items (you know how to do that!) and use the AddItem method of the combo box to add the cell values.

    Write code in the AfterUpdate event of the combo box to clear the list box, then loop through the filled cells in column K again, and when you find the value from the combo box, use AddItem to add the value from column L in the same row to the list box, and use the List property to fill the second and third column of the list box with the values from column M and O in the worksheet.

  4. #4
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: fill listbox and conbobox (2000 sr-1)

    Hans, test me please.... listbox non is filled!!!

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: fill listbox and conbobox (2000 sr-1)

    You're treating the list box as if it is a text box. As I mentioned in my previous reply, you should use the AddItem method.

    At the beginning of ComboBox1_Change:

    Dim n As Integer

    In the part where you fill the list box:

    Me.ListBox1.Clear
    For Each cell In rng
    If cell = MainString Then
    Me.ListBox1.AddItem cell.Offset(0, 1)
    n = Me.ListBox1.ListCount - 1
    Me.ListBox1.List(n, 1) = cell.Offset(0, 2)
    Me.ListBox1.List(n, 2) = cell.Offset(0, 4)
    End If
    Next cell

    Note: you should set the Column Count property of the list box to 3 instead of 6, and change the column widths, for example to [coee]96;96;96[/code]

Posting Permissions

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