Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

expand code by search for each column from combobox

0

Hi,

I want expanding the code to make search based on multiple columns  when select the  header from combobox3

currently the code will search based on column D and  does : 

first when select sheet name from combobox1 , then will populate data in listbox based on sheet name  is matched with combobox1 after this if I select month from comboboxes 2 then will just populate data in listbox for this month based on selected comboboxes 1,2 together

second when write the ID in textbox1 based on column D, then will populate data on userform based on selected comboboxes1,2 together



now I add combobox3 and  will populate headers names in combobox3 to select specific column to search . so  when select sheet name from combobox1 and select header from combobox3 then will populate data in listbox based on column contains header is matched with combobox3 .

thanks 

Answer
Discuss

Discussion

Hi again Mussala

Sorry but I'm struggling to understand your question.

Your current form has TextBox1 as "CSS" and the lists box is filtered by that, Sheet (ComboBox1) and Month (ComboBox2).

Do you want the user to pick a column from the dropdown on new ComboBox3 then filter the listbox based on TextBox1 value (and CB1/ CB2)?
John_Ru (rep: 6232) Aug 5, '23 at 6:21 am
Do you want the user to pick a column from the dropdown on new ComboBox3 then filter the listbox based on TextBox1 value (and CB1/ CB2)?
yes  that's correct   
Mussala (rep: 12) Aug 5, '23 at 7:03 am
Add to Discussion

Answers

0
Selected Answer

Mussala

The attached revised file allows you to "pick a column from the dropdown on new ComboBox3 then filter the listbox based on TextBox1 value (and CB1/ CB2)".

I've modified your UserForm slightly and made changes to your code (in bold in the extracts below).

Firstly at the top of the UserForm code, I've declared a new variable Srch ( for the Search column):

Option Explicit
Option Compare Text

Private Data, Temp, Srch As Integer

When you launch the form,  you won;t see Textbox1 or its labelsince the initialisation code now includes:

Private Sub UserForm_Initialize()


<< existing code>>
  ' hide textbox1 and label
  TextBox1.Visible = False
  Label2.Visible = False

End Sub

but they will appear if you chose a Search column in ComboBox3, whose code is:

Private Sub ComboBox3_Change()
  ' show label2 move cursor to (blank) textbox1
  Label2.Visible = True
  With TextBox1
    .Visible = True
    .SetFocus
    .Value = ""
  End With
End Sub

If you then type a value into the tetxtbox, this runs

Private Sub TextBox1_Change()
  ' record column chosen by user
  Srch = ComboBox3.ListIndex + 3
  Call LBoxPop
End Sub

which in turn calls the modified code below:

Private Sub LBoxPop()
<< existing code>>
  For i = 2 To UBound(Data)
    ' filter on value for chosen column (Srch) or 4
    If TextBox1.Value = "" Then
        crit = Data(i, 4)
        Srch = 4
        Else
        crit = TextBox1.Value
    End If
    If ComboBox2.Value = "" Then cmb2 = Month(Data(i, 2)) Else cmb2 = Val(ComboBox2.Value)

    'If Data(i, 4) Like crit & "*" And Month(Data(i, 2)) = cmb2 Then
    ' filter on chosen column (Srch)
    If Data(i, Srch) Like "*" & crit & "*" And Month(Data(i, 2)) = cmb2 Then
      x = x + 1
      For j = 1 To 10
        Temp(x, j) = Data(i, j)
        If j = 2 Then Temp(x, 2) = Format(Data(i, 2), "DD/MM/YYYY")
        If j >= 8 Then Temp(x, j) = Format$(Data(i, j), myFormat(1))
      Next j
    End If
  Next i
<< existing code>>

Note that the addition of a leading wildcard ("*" & crit) means you can put a string from any part of that field e.g. if searching for a CSS, you can now type "202" (instead of "R202") and quickly get results for CSS R202.

Hope this makes sense and does what you want.

Discuss

Discussion

really  thank  you  for  new  version .
Mussala (rep: 12) Aug 5, '23 at 1:16 pm
Great. Thanks for selecting my, Mussala. 
John_Ru (rep: 6232) Aug 5, '23 at 4:07 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login