Multiple columns to populate data on userform based on header in combobox

abdo meghari · Saturday at 6:31 AM

Hi,
I search for code to populate data on form based on combo box , but my project will be more More expansive
here is what I got it so far wit I try populate items in combobox1 for sheets names and combobox2 for headers should search for it .

VBA Code:

Option Explicit
Dim a As Variant

Private Sub UserForm_Activate()
Dim ws As Variant
For Each ws In Sheets(Array("SV", "SR", "VS", "RS"))
ComboBox1.AddItem ws.Name
Next ws
Dim arr As Variant
Dim sh As Worksheet
Set sh = Sheets("SV")
For Each arr In Array("CUSTOMER", "INV.NO", "BRAND", "QTY", "PRICE", "TOTAL")
ComboBox2.AddItem arr
Next arr
 a = Sheets(ComboBox1).Range("A2:H" & Sheets("ComboBox1").Range("D" & Rows.Count).End(3).Row).Value
End Sub



Private Sub TextBox3_Change()
  Call FilterData
End Sub



Sub FilterData()
  Dim txt1 As String, txt2 As String, txt3 As String
  Dim i As Long, j As Long, k As Long
  
  ListBox1.Clear
  ReDim b(1 To UBound(a, 1), 1 To UBound(a, 2))
  For i = 1 To UBound(a)
    If TextBox3 = "" Then txt1 = a(i, 4) Else txt1 = TextBox3
    If LCase(a(i, 4)) Like LCase(txt1) Then
      k = k + 1
      For j = 1 To 8
        b(k, j) = a(i, j)
      Next
    End If
  Next
  If k > 0 Then ListBox1.List = b
End Sub

here is data in four sheets.

COL.xlsm

A

B

C

D

E

F

G

H

1

ITEM

DATE

CUSTOMER

INV.NO

BRAND

QTY

PRICE

TOTAL

2

1

15/06/2023

CCF-1000

BSTR_23448

BS 750R16 R230 JAP

4.00

500.00

2,000.00

3

2

15/06/2023

CCF-1000

BSTR_23448

BS 700R16 R230 JAP

2.00

400.00

800.00

4

SUM

2,800.00

5

1

15/09/2023

CCF-1001

BSTR_23449

GO 1200R20 AZ0026 CHI

1.00

920.00

6

2

15/09/2023

CCF-1001

BSTR_23449

GO 1200R20 AZ183 CHI

2.00

1,000.00

2,000.00

7

SUM

2,920.00

8

1

15/09/2023

CCF-1000

BSTR_23450

BS 1200R20 G580 JAP

10.00

1,800.00

18,000.00

9

2

15/09/2023

CCF-1000

BSTR_23450

BS 1200R20 R187 JAP

10.00

1,800.00

18,000.00

10

3

15/09/2023

CCF-1000

BSTR_23450

BS 1200R20 G580 THI

10.00

1,800.00

18,000.00

11

SUM

54,000.00

12

1

16/09/2023

CCF-1002

BSTR_23451

BS 215/60R16 ER30 JAP

4.00

400.00

1,600.00

13

SUM

1,600.00

14

1

16/09/2023

CCF-1001

BSTR_23452

BS 1200R20 G580 JAP

5.00

1,800.00

9,000.00

15

SUM

9,000.00

16

1

16/09/2023

CCF-1001

BSTR_23453

BS 1200R20 G580 THI

5.00

1,880.00

9,400.00

17

SUM

9,400.00

SV

COL.xlsm

A

B

C

D

E

F

G

H

1

ITEM

DATE

CUSTOMER

INV.NO

BRAND

QTY

PRICE

TOTAL

2

1

10/06/2023

CCF-1002

BSJ_23444

BS 215/60R16 ER30 JAP

4.00

430.00

1,720.00

3

SUM

1,720.00

4

1

10/06/2023

CCF-1002

BSJ_23445

GO 1200R20 AZX0026 CHI

2.00

955.00

1,910.00

5

SUM

1,910.00

6

1

15/09/2023

CCF-1001

BSJ_23446

GO 1200R20 AZX0026 CHI

2.00

950.00

1,900.00

7

2

15/09/2023

CCF-1001

BSJ_23446

GO 1200R21 AZ0027 CHI

3.00

1,000.00

3,000.00

8

SUM

4,900.00

9

1

15/09/2023

CCF-1000

BSJ_23447

BS 1200R20 G580 JAP

1.00

2,000.00

10

2

15/09/2023

CCF-1000

BSJ_23447

BS 1200R20 G580 THI

1.00

2,000.00

11

3

15/09/2023

CCF-1000

BSJ_23447

BS 1200R20 R187 JAP

1.00

2,000.00

12

SUM

6,000.00

SR

COL.xlsm

A

B

C

D

E

F

G

H

1

ITEM

DATE

CUSTOMER

INV.NO

BRAND

QTY

PRICE

TOTAL

2

1

15/06/2023

CCF-1000

VSTR_23444

BS 750R16 R230 JAP

1.00

500.00

3

SUM

500.00

4

1

15/09/2023

CCF-1000

VSTR_23445

GO 1200R20 AZ0183

1.00

1,000.00

5

SUM

1,000.00

6

1

15/09/2023

CCF-1001

VSTR_23446

BS 1200R20 G580 JAP

1.00

1,800.00

7

2

15/09/2023

CCF-1001

VSTR_23446

BS 1200R20 G580 JAP

1.00

1,800.00

8

SUM

3,600.00

9

1

16/09/2023

CCF-1001

VSTR_23447

BS 215/60R16 ER30 JAP

4.00

400.00

1,600.00

10

SUM

1,600.00

11

1

16/09/2023

CCF-1001

VSTR_23448

BS 1200R20 R187 JAP

1.00

1,800.00

12

SUM

1,800.00

13

1

16/09/2023

CCF-1000

VSTR_23449

BS 1200R20 G580 THI

2.00

1,880.00

3,760.00

14

SUM

3,760.00

VS

COL.xlsm

A

B

C

D

E

F

G

H

1

ITEM

DATE

CUSTOMER

INV.NO

BRAND

QTY

PRICE

TOTAL

2

1

10/06/2023

CCF-1002

RSS_23222

BS 215/60R16 ER30

2.00

430.00

860.00

3

SUM

860.00

4

1

10/06/2023

CCF-1001

BSJ_23445

GO 1200R20 AZ0026

1.00

955.00

5

SUM

955.00

6

1

15/09/2023

CCF-1002

BSJ_23446

GO 1200R20 AZ0026

1.00

950.00

7

2

15/09/2023

CCF-1002

BSJ_23446

GO 1200R21 AZ0027

2.00

1,000.00

2,000.00

8

SUM

2,950.00

RS

when I select sheet name from combobox1, select the header from combobox2 and write the ID is relating with the header in textbox3 then will populate data in listbox and sum colulmn QTY in textbox4 and sum column TOTAL in textbox5 like this
example SV sheets

when just select header and ID without sheet name will add column sheets names in third column in listbox

as to dates(textbox1,textbox2) should be when select sheet name and header or when just select header without sheet name .
I hope correct my error and improve based on my requirements.
thanks

DanteAmor · Saturday at 3:58 PM

Let's try an advanced filter approach.

Create a sheet and call it "Temp".
On that sheet put the following structure, including the formulas:

Dante Amor

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

1

ITEM

DATE

SHEET NAME

CUSTOMER

INV.NO

BRAND

QTY

PRICE

TOTAL

SHEET NAME

DATE

CUSTOMER

INV.NO

BRAND

QTY

PRICE

TOTAL

2

>=0

<=0

1.00

3

4

00/01/1900

5

6

ITEM

DATE

SHEET NAME

CUSTOMER

INV.NO

BRAND

QTY

PRICE

TOTAL

Temp

Cell Formulas
Range	Formula
L2	L2	=">="&MAX(L3:L4)
M2	M2	="<="&MIN(M3:M4)
L4	L4	=MIN(B:B)
M4	M4	=MAX(B:B)

abdo meghari said:
but my project will be more More expansive

Wow it is. The project is ambitious and has a large number of combinations to filter, and with several specifications in each filter. That is why the advanced filter approach and hence the need for an auxiliary sheet.

when I select sheet name from combobox1, select the header from combobox2 and write the ID is relating with the header in textbox3 then will populate data in listbox

sum colulmn QTY in textbox4 and sum column TOTAL in textbox5

when just select header and ID without sheet name will add column sheets names in third column in listbox

as to dates(textbox1,textbox2) should be when select sheet name and header or when just select header without sheet name

This date option is improved, you can even filter by just one date, FROM DATE and it automatically takes up to the last date.

Or TO DATE and it automatically takes from the first date.

Filter by dates without selecting any field or with the combination of one or more or all fields.

Which seems wonderful to me and that is achieved in an easier way with the advanced filter.

Notes:

Obviously I improved the charging of the comboboxes.
I did a test with 8 thousand records on each sheet and the response time is one second.
The textbox3 works until you capture a complete value that exists
In textbox3 the thousands separator format is not necessary, that is, you can put 1800 instead of 1,800.00

Put all code in your userform:

VBA Code:

Dim sht As Worksheet
Const frm As String = "#,##0.00;-#,##0.00;0.00"

Sub FilterData()
  Dim lr As Long
  Dim qty As Double, tot As Double

  Application.ScreenUpdating = False
 
  Call List_initial
  sht.Range("K2, L3:M3, N2:S2").ClearContents
  If ComboBox1.ListIndex > -1 Then
    sht.Range("K2").Value = ComboBox1.Value
    ListBox1.ColumnWidths = ";;0"
  Else
    sht.Range("K2").Value = ""
    ListBox1.ColumnWidths = ""
  End If
  If IsDate(TextBox1.Value) Then sht.Range("L3").Value = CDate(TextBox1.Value)
  If IsDate(TextBox2.Value) Then sht.Range("M3").Value = CDate(TextBox2.Value)
  If ComboBox2.ListIndex > -1 Then sht.Cells(2, Columns("N").Column + ComboBox2.ListIndex).Value = TextBox3.Value
    
  sht.Range("A1", sht.Range("I" & Rows.Count).End(3)).AdvancedFilter 2, sht.Range("J1:R2"), sht.Range("K6:S6"), False
 
  lr = sht.Range("K" & Rows.Count).End(3).Row
  If lr > 6 Then
    With sht.Range("K7:S" & lr)
      .Cells(1).Value = 1
      .Columns(1).DataSeries xlColumns, xlLinear, xlDay, 1, Trend:=False
      ListBox1.RowSource = sht.Range("K7:S" & lr).Address(external:=True)

      TextBox4.Value = Format(WorksheetFunction.Sum(.Columns(7)), frm)
      TextBox5.Value = Format(WorksheetFunction.Sum(.Columns(9)), frm)
    End With
  End If
  Application.ScreenUpdating = False
End Sub

Private Sub ComboBox1_Change()
  Call FilterData
End Sub
Private Sub ComboBox2_Change()
  TextBox3.Value = ""
End Sub
Private Sub TextBox1_Change()
  Call List_initial
  If checkDate(TextBox1) Then Call FilterData
End Sub
Private Sub TextBox2_Change()
  Call List_initial
  If checkDate(TextBox2) Then Call FilterData
End Sub
Private Sub TextBox3_Change()
  Dim f As Range
  Dim vValue As Variant
 
  Call List_initial
 
  If TextBox3.Value = "" Then
    Call FilterData
    Exit Sub
  End If
  If ComboBox2.ListIndex > -1 Then
    vValue = IIf(IsNumeric(TextBox3.Value), Val(TextBox3.Value), TextBox3.Value)
    Set f = sht.Columns(ComboBox2.ListIndex + 4).Find(vValue, , xlFormulas, xlWhole, , , False)
    If Not f Is Nothing Then
      Call FilterData
    End If
  End If
End Sub

Function checkDate(tBox As MSForms.TextBox)
  With tBox
    checkDate = True
    If .Value = "" Then Exit Function
    If Len(.Value) <> 10 Or Not IsDate(.Value) Or Not .Value Like "??/??/????" Then checkDate = False
  End With
End Function

Sub List_initial()
  sht.Range("K7:S" & Rows.Count).ClearContents
  TextBox4.Value = ""
  TextBox5.Value = ""
End Sub

Private Sub UserForm_Activate()
  Dim arr As Variant, itm As Variant, a As Variant
  Dim i As Long, j As Long, k As Long
 
  Set sht = Sheets("Temp")
  sht.Range("A2:I" & Rows.Count).ClearContents
  sht.Range("K2, L3:M3, N2:S2").ClearContents
  sht.Range("K7:S" & Rows.Count).ClearContents
  sht.Range("G:I").NumberFormat = frm
  sht.Range("B:B").NumberFormat = "dd/mm/yyyy"

  ComboBox1.List = Array("SV", "SR", "VS", "RS")
  ComboBox2.List = Array("CUSTOMER", "INV.NO", "BRAND", "QTY", "PRICE", "TOTAL")
 
  For Each itm In ComboBox1.List
    a = Sheets(itm).Range("A2", Sheets(itm).Range("H" & Rows.Count).End(3)).Value
    ReDim b(1 To UBound(a, 1), 1 To UBound(a, 2) + 1)
      k = 0
    For i = 1 To UBound(a, 1)
      If a(i, 1) <> "SUM" Then
        k = k + 1
          b(k, 1) = a(i, 1)
          b(k, 2) = a(i, 2)
          b(k, 3) = itm
          b(k, 4) = a(i, 3)
          b(k, 5) = a(i, 4)
          b(k, 6) = a(i, 5)
          b(k, 7) = a(i, 6)
          b(k, 8) = a(i, 7)
          b(k, 9) = a(i, 8)
      End If
    Next
    sht.Range("A" & Rows.Count).End(3)(2).Resize(UBound(b, 1), UBound(b, 2)).Value = b
  Next

  With ListBox1
    .RowSource = ""
    .ColumnCount = 8
    .ColumnHeads = True
  End With
 
  Call FilterData
End Sub

abdo meghari · Sunday at 4:08 AM

Really excellent !!

I have some notes:
1- I think should be 9 columns , not 8 columns in this line

Rich (BB code):

<span>With</span> ListBox1<br>    <span>.</span>RowSource <span>=</span> <span>""</span><br>    <span>.</span>ColumnCount <span>=</span> <span>8</span>

because will not show TOTAL column in list box.
2-I try changing ColumnWidths in listbox by add this line

VBA Code:

.ColumnWidths = "70;70;70;70;70;100;70;70;70"

but unfortunately doesn't change anything because there is wide space among columns.

The textbox3 works until you capture a complete value that exists

3- could search for part of value when I write until finishing or could this way effect for running speed?

DanteAmor · Sunday at 11:21 AM

abdo meghari said:
1- I think should be 9 columns , not 8 columns in this line

OK

Rich (BB code):

  With ListBox1
    .RowSource = ""
    .ColumnCount = 9
    .ColumnHeads = True
  End With

abdo meghari said:
2. I try changing ColumnWidths in listbox by add this line

Rich (BB code):

  Call List_initial
  sht.Range("K2, L3:M3, N2:S2").ClearContents
  If ComboBox1.ListIndex > -1 Then
    sht.Range("K2").Value = ComboBox1.Value
    ListBox1.ColumnWidths = "70;70;0;70;70;100;70;70;70"    'to hide column sheet name
  Else
    sht.Range("K2").Value = "70;70;70;70;70;100;70;70;70"
    ListBox1.ColumnWidths = ""
  End If

abdo meghari said:
3. could search for part of value when I write until finishing or could this way effect for running speed?

Does it actually work using ? or/and * as a wildcard, but only in the text fields (customer, inv, brand), examples:

At the beginning:

Both:

In the end:

If you also want it to work with numbers, I put the adjusted code:

VBA Code:

Option Explicit

Dim sht As Worksheet
Const frm As String = "#,##0.00;-#,##0.00;0.00"

Sub FilterData()
  Dim lr As Long
  Dim qty As Double, tot As Double

  Application.ScreenUpdating = False
 
  Call List_initial
  sht.Range("K2, L3:M3, N2:S2").ClearContents
  If ComboBox1.ListIndex > -1 Then
    sht.Range("K2").Value = ComboBox1.Value
    ListBox1.ColumnWidths = "70;70;0;70;70;100;70;70;70"
  Else
    sht.Range("K2").Value = ""
    ListBox1.ColumnWidths = "70;70;70;70;70;100;70;70;70"
  End If
  If IsDate(TextBox1.Value) Then sht.Range("L3").Value = CDate(TextBox1.Value)
  If IsDate(TextBox2.Value) Then sht.Range("M3").Value = CDate(TextBox2.Value)
  If ComboBox2.ListIndex > -1 Then
    On Error Resume Next
    sht.Cells(2, Columns("N").Column + ComboBox2.ListIndex).Value = TextBox3.Text
    On Error GoTo 0
  End If
 
  sht.Range("A1", sht.Range("I" & Rows.Count).End(3)).AdvancedFilter 2, sht.Range("K1:S2"), sht.Range("K6:S6"), False
 
  lr = sht.Range("K" & Rows.Count).End(3).Row
  If lr > 6 Then
    With sht.Range("K7:S" & lr)
      .Cells(1).Value = 1
      .Columns(1).DataSeries xlColumns, xlLinear, xlDay, 1, Trend:=False
      ListBox1.RowSource = sht.Range("K7:S" & lr).Address(external:=True)

      TextBox4.Value = Format(WorksheetFunction.Sum(.Columns(7)), frm)
      TextBox5.Value = Format(WorksheetFunction.Sum(.Columns(9)), frm)
    End With
  End If
  Application.ScreenUpdating = False
End Sub

Private Sub ComboBox1_Change()
  Call FilterData
End Sub
Private Sub ComboBox2_Change()
  TextBox3.Value = ""
End Sub
Private Sub TextBox1_Change()
  Call List_initial
  If checkDate(TextBox1) Then Call FilterData
End Sub
Private Sub TextBox2_Change()
  Call List_initial
  If checkDate(TextBox2) Then Call FilterData
End Sub
Private Sub TextBox3_Change()
  Dim f As Range
  Dim vValue As Variant
 
  Call List_initial
 
  If TextBox3.Value = "" Then
    Call FilterData
    Exit Sub
  End If
  If ComboBox2.ListIndex > -1 Then
    'vValue = IIf(IsNumeric(TextBox3.Value), Val(TextBox3.Value), TextBox3.Value)
    'Set f = sht.Columns(ComboBox2.ListIndex + 4).Find(vValue, , xlFormulas, xlWhole, , , False)
    'If Not f Is Nothing Then
      Call FilterData
    'End If
  End If
End Sub

Function checkDate(tBox As MSForms.TextBox)
  With tBox
    checkDate = True
    If .Value = "" Then Exit Function
    If Len(.Value) <> 10 Or Not IsDate(.Value) Or Not .Value Like "??/??/????" Then checkDate = False
  End With
End Function

Sub List_initial()
  sht.Range("K7:S" & Rows.Count).ClearContents
  TextBox4.Value = ""
  TextBox5.Value = ""
End Sub

Private Sub UserForm_Activate()
  Dim arr As Variant, itm As Variant, a As Variant
  Dim i As Long, j As Long, k As Long
 
  Set sht = Sheets("Temp")
  sht.Range("A2:I" & Rows.Count).ClearContents
  sht.Range("K2, L3:M3, N2:S2").ClearContents
  sht.Range("K7:S" & Rows.Count).ClearContents
  sht.Range("G:I").NumberFormat = frm
  sht.Range("B:B").NumberFormat = "dd/mm/yyyy"

  ComboBox1.List = Array("SV", "SR", "VS", "RS")
  ComboBox2.List = Array("CUSTOMER", "INV.NO", "BRAND", "QTY", "PRICE", "TOTAL")
 
  For Each itm In ComboBox1.List
    a = Sheets(itm).Range("A2", Sheets(itm).Range("H" & Rows.Count).End(3)).Value
    ReDim b(1 To UBound(a, 1), 1 To UBound(a, 2) + 1)
      k = 0
    For i = 1 To UBound(a, 1)
      If a(i, 1) <> "SUM" Then
        k = k + 1
          b(k, 1) = a(i, 1)
          b(k, 2) = a(i, 2)
          b(k, 3) = itm
          b(k, 4) = a(i, 3)
          b(k, 5) = a(i, 4)
          b(k, 6) = a(i, 5)
          b(k, 7) = a(i, 6)
          b(k, 8) = a(i, 7)
          b(k, 9) = a(i, 8)
      End If
    Next
    sht.Range("A" & Rows.Count).End(3)(2).Resize(UBound(b, 1), UBound(b, 2)).Value = b
  Next

  With ListBox1
    .RowSource = ""
    .ColumnCount = 9
    .ColumnHeads = True
  End With
 
  Call FilterData
End Sub

But for it to work with numbers you will have to put formulas, examples:

To QTY, you can put 5 or =">=5"

PRICE: ="<500"

TOTAL: =">9000"

The search in numerical values could also be partial, but it implies more changes and I consider that with that you have enough tools.

Regards

abdo meghari · Monday at 4:28 AM

Really awesome new version !
many thanks .

Multiple columns to populate data on userform based on header in combobox

abdo meghari

Well-known Member

Excel Facts

DanteAmor

Well-known Member

abdo meghari

Well-known Member

DanteAmor

Well-known Member

abdo meghari

Well-known Member

Similar threads

Forum statistics

Share this page

Multiple columns to populate data on userform based on header in combobox

abdo meghari

Well-known Member

Excel Facts

DanteAmor

Well-known Member

abdo meghari

Well-known Member

DanteAmor

Well-known Member

abdo meghari

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock