ComboBox selection want populate my listbox

[JohnnyBQue]

I know I’m overlooking something but can’t get my combo box selection to populate my list box…

Also while your taking a look…how can I replace the “Array” section of the line below

With code that would allow me to choose anything entry I may put under Sheet 1, column “C” and populate the list box with the associated row data?

“Me.comboBox1.list = Array(“Copper”, “Brass”) (Copper & Brass come from Sheet 1 column “C” those are the only two entries at this time in the column)

I use a simple 5 column data range (A-E) about 100 rows deep with headings; the form has 1 label, 1 combobox, 2 command buttons (only 1 used at this time) and 1 listbox

haven't posted here in a while not sure if an excel file cam be uploaded

//
Private Sub ComboBox1_Change()
Me.ListBox1.Clear
Me.ListBox1.AddItem
For x = 0 To 4
Me.ListBox1.List(0, x) = Sheet1.Cells(1, x + 1)
Next x
Me.ListBox1.Selected(0) = True

For i = 1 To Sheet1.Range("A1000000").End(xlUp).Row
If Sheet1.Cells(1, "C") = Me.ComboBox1 Then
Me.ListBox1.AddItem
For x = 0 To 4
Me.ListBox1.List(ListBox1.ListCount - 1, x) = Sheet1.Cells(i, x + 1)
Next x
End If
Next i
End Sub

Private Sub CommandButton2_Click()
Call UserForm_Initialize
End Sub

Private Sub UserForm_Initialize()
Me.Label8.Caption = Date
Me.ComboBox1.List = Array("Copper", "Brass")
For i = 1 To Sheet1.Range("A1000000").End(xlUp).Row
Me.ListBox1.AddItem
For x = 0 To 4
Me.ListBox1.List(i - 1, x) = Sheet1.Cells(i, x + 1)
Next x
Next i
Me.ListBox1.Selected(0) = True
End Sub

 

[DanteAmor]

Hi @JohnnyBQue
Welcome back to the forum, I hope you are well.

Replace all your code with the following:

Private Sub ComboBox1_Change()
  Dim i As Long, j As Long
  
  Me.ListBox1.Clear
  If ComboBox1.ListIndex = -1 Then Exit Sub
  
  For i = 1 To Sheet1.Range("A" & Rows.Count).End(xlUp).Row
    If Sheet1.Range("C" & i).Value = ComboBox1.Value Then
      With ListBox1
        .AddItem
        For j = 0 To 4
          .List(.ListCount - 1, j) = Sheet1.Cells(i, j + 1).Value
        Next
      End With
    End If
  Next i
End Sub

Private Sub UserForm_Initialize()
  Dim lr As Long, i As Long
  Dim dic As Object
  
  Set dic = CreateObject("Scripting.Dictionary")
  
  Label8.Caption = Date
  
  lr = Sheet1.Range("A" & Rows.Count).End(3).Row
  For i = 1 To lr
    dic(Sheet1.Range("C" & i).Value) = Empty
  Next i
  ComboBox1.List = dic.keys
  
  With ListBox1
    .ColumnCount = 5
    .List = Sheet1.Range("A1:E" & lr).Value
  End With
End Sub

Private Sub CommandButton2_Click()
  Call UserForm_Initialize
End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​

 

[JohnnyBQue]

Hi @JohnnyBQue
Welcome back to the forum, I hope you are well.

Replace all your code with the following:

Private Sub ComboBox1_Change()
  Dim i As Long, j As Long
 
  Me.ListBox1.Clear
  If ComboBox1.ListIndex = -1 Then Exit Sub
 
  For i = 1 To Sheet1.Range("A" & Rows.Count).End(xlUp).Row
    If Sheet1.Range("C" & i).Value = ComboBox1.Value Then
      With ListBox1
        .AddItem
        For j = 0 To 4
          .List(.ListCount - 1, j) = Sheet1.Cells(i, j + 1).Value
        Next
      End With
    End If
  Next i
End Sub

Private Sub UserForm_Initialize()
  Dim lr As Long, i As Long
  Dim dic As Object
 
  Set dic = CreateObject("Scripting.Dictionary")
 
  Label8.Caption = Date
 
  lr = Sheet1.Range("A" & Rows.Count).End(3).Row
  For i = 1 To lr
    dic(Sheet1.Range("C" & i).Value) = Empty
  Next i
  ComboBox1.List = dic.keys
 
  With ListBox1
    .ColumnCount = 5
    .List = Sheet1.Range("A1:E" & lr).Value
  End With
End Sub

Private Sub CommandButton2_Click()
  Call UserForm_Initialize
End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​

Dante the code worked great...I do have one question the "C" in the line of code
// if Sheet1.Range('C" &i).Value = Combobox1.Value Then //this represents the column ID "C" ? if so then I can change this column ID to any Column ID to be the values that will be displayed in the combo box when clicking and making a selection...
also if i changed the "C" to another column ID such as "B" i would need to change the "C" to a "B" where it may be reference further in the code correct?
just looking into the future where i may need to reference different columns

thanks so much for you help very appreciated!

 

[JohnnyBQue]

Never mine i did what i should have done before asking those questions....I just went ahead and tried it and it work....!...changing the column ID that is....

So again thanks for your help

 

[JohnnyBQue]

I did run into one issue that's not really an issue but would like to know for learning purposes...I need the first row "A1" to show up in my list box as the "Headers" (remain constant no matter the choice of the combo box) ....In the properties for the list box I have "ColumnHead:" set as TRUE but when the list box results are displayed there is nothing in the header locations

now i used RowSource which will grab the headers from the row above the DATA but there are times I get a error 70 due to the data being reference by two different sources the code and the property settings...

look forward to you response...

 

[DanteAmor]

Review the following answers to understand a bit more about the RowSource, List properties, and the .AddItem method:

populate listbox

Hi, i try to populate a listbox from a table (table25) i want headers but i only want the listbox populated from the 3th row so in my search on the big www i found that i can add a second listbox and use this as a header and the 1st one only populating with table items without the header i...

www.mrexcel.com

Combo Box Fill in Header

Hi, I have a problem with my combo box. In the screenshot that I have provided, you can see "Contact Person", "Basic Wage" and "Time Wage". I want these items to fill the header in the combo box. Can someone help me? Thank you. Here is the code that I use: Public Sub cmFillWorker1(cmName As...

www.mrexcel.com