Populate three columns in list box from many columns based on selected date in combobox

[tubrak]

Hi
I have data in SS sheet the headers in row1 in column A is ITEM(1,2,3...) column B is ID the column C,D,E...... contains dates DD/MM/YYYY
and I have userform contains listbox1 , combobox1 . the combobox1 should populate dates from column C : last column (every time add new column will contain date in header)
every column contain date will contain QTY so what I want when select date from combobox1 should populate three columns ITEM, ID , DATE based on selected date from combobox1
I have about 5000 rows in SS sheet
thanks

 

[DanteAmor]

Put the following code in your form. Even if you already have a code to load your combobox, replace it with the following.

The approch of the code is to load all the sheet data in the listbox when activating the userform.
Later when you select a date, it will only show you columns 1, 2 and the column of the selected date. This will make displaying the data very fast.

Private Sub ComboBox1_Change()
  Dim j As Long
  Dim wds As String
 
  With ComboBox1
    If .ListIndex = -1 Then Exit Sub
    For j = 2 To ListBox1.ColumnCount - 1
      wds = wds & IIf(j = .ListIndex + 2, ";", "0;")
    Next
    ListBox1.ColumnWidths = ";;" & wds
  End With
End Sub

Private Sub UserForm_Activate()
  Dim lr As Long, lc As Long, j As Long
  Dim wds As String
 
  lc = Cells(1, Columns.Count).End(1).Column
  lr = Range("A" & Rows.Count).End(3).Row
  ComboBox1.List = Application.Transpose(Range("C1", Cells(1, lc)).Value)
  With ListBox1
    .ColumnCount = lc
    .List = Range("A2", Cells(lr, lc)).Value
    For j = 0 To ListBox1.ColumnCount - 1
      wds = wds & "0;"
    Next
    .ColumnWidths = wds
  End With
End Sub

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

 

[tubrak]

Hi,
your code does exactly what I described in OP .
much appreciated for your help .