fill listbox and combobox

sal21

Active Member
Joined
Apr 1, 2002
Messages
250
i have this sheet and form...(use the form in wbook)
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
here the file:
http://www.mytempdir.com/1005159
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

agihcam

Well-known Member
Joined
Jan 16, 2006
Messages
1,624
Hi -
I have this dic object code from one of my spreadsheet coming from Jindon ( Thanks to Jindon ), just modified a little to suit your needs. This code will display only the unique items in combobox.
add these codes from userform activate event;
Code:
'populate combobox
Dim i As Long
Dim a As Object, w()
Set a = CreateObject("scripting.dictionary")
For i = 2 To Sheets("STATISTICA").Range("k" & Rows.Count).End(xlUp).Row
    With Sheets("STATISTICA")
       If Not IsEmpty(.Cells(i, "k").Value) Then
            If Not a.exists(.Cells(i, "k").Value) Then
                ReDim w(0)
                    a.Add .Cells(i, "k").Value, w(0)
                    w(0) = .Cells(i, "k").Value
                    Me.ComboBox1.AddItem w(0)
            End If
        End If
    End With
Next

to display values on listbox depending on combobox selection, double click combobox1 and paste these codes from combobox change event;
Code:
Dim i As Long, f As String
Dim c As Object
i = Sheets("STATISTICA").Range("k" & Rows.Count).End(xlUp).Row
With Sheets("STATISTICA").Range("k2:k" & i)
    Set c = .Find(Me.ComboBox1.Text, , , xlWhole)
        If Not c Is Nothing Then
        f = c.Address
        Do
            Me.ListBox1.AddItem c.Offset(, 1).Value & vbTab & vbTab & c.Offset(, 2).Value & vbTab & vbTab & c.Offset(, 4).Value
        Set c = .FindNext(c)
        Loop Until f = c.Address
        End If
End With
hope it helps.
 

Forum statistics

Threads
1,136,649
Messages
5,676,991
Members
419,667
Latest member
MegEri

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top