modifying codes on userform to expand search by two comboboxes and textbox

[Mussa]

Hello
Currently, the code is fetching data based on selecting a specific sheet from combobox1 , and selecting a specific month from combobox2 , it filters the data in the list box based on the sheet selected from combobox1 and the month selected from combobox2 . What I want is when I search for the ID into textbox1 based on the fourth column(D) based on the sheet selected from combobox1 and the month chosen from combobox2 , then the data will be filtered In the list box for that identifier within the selected sheet and month(based on two comboboxes and textbox1)
here is my file
SSR v0 a modified v0 b.xlsm
hope somebody can modify it .

 

[sijpie]

Set rngDB = Range ("A1").currentregion

 

[Mussa]

thanks.
it gives error

, but I can't where is debug line despite of I using F8 will move line to line within this procedure

n = n + 1
        ReDim Preserve vR(1 To n)
        vR(n) = rng.EntireColumn.Width
    Next rng

without I've found where is the line.

 

[sijpie]

I'll have a look later this week

 

[sijpie]

just I ask about this line
VBA Code:
Set rngDB = Range("A1:J20")
could be dynamic without specify the end of row number, please?

You previously asked the above. Why? you only need the column width for each column, so you only need to loop through the columns once. No need to loop through all the cells.

    Set rngDB = Range("A1:J1")
    For Each rng In rngDB
        n = n + 1
        ReDim Preserve vR(1 To n)
        vR(n) = rng.EntireColumn.Width
    Next rng

This creates vR with ten elements (for the 10 columns)

This then gets put into sWidth
sWidth then looks like this: 99;110.4;107.4;88.8;91.8;86.4;85.8;64.2;73.2;94.2

That string is read into the Listbox.ColumnWidths

sWidth = Join(vR, ";")
    Debug.Print sWidth
    With lbxResult
        .ColumnCount = 10
        .ColumnWidths = sWidth

If you were using
rngDB = Range("A1").Currentregion
then the vR would contain thousands of elements, all strung together in sWidth. And then the Listbox is asked to put thousands of columnwidths. That is where the code will fail.

So change it back to

Set rngDB = Range("A1:J1")

and the userform will show up nicely

 

[Mussa]

despite of the code contains array and no big data seems to be slow when load data on userform.
thanks very much for your assistance.

 

[sijpie]

Yes, there is a small delay before the rows appear. But hey, the computer is always waiting far longer for the user to do something than the user is waiting for the computer...