VBA Userform: Search Table column values based on table column name

[inddon]

Hello There,

I have a excel table defined. Created a Userform with 3 objects:

1. ComboBox: Which will display all the Table column names
2. TextBox: Enter the value to be searched for the Table Column Name selected above.
3. Button Search: On pressing, it should show up the row for the above given points 1 & 2
4. Button Reset: On pressing, it should clear all the filter values on the worksheet.
5. Cancel: Rest & close the UserForm.

This can be achieved using the filter options in the Table. The users are real non-excel users with min. knowledge. That is why this requirement.


Please advise. I have attached the sample workbook for your reference and a jpeg file for the final output.

Looking forward to hearing from you.

Regards
Don

 

[Somendra Misra]

Hi Don,

See the file.

Regards,

 

[inddon]

Hi Don,

See the file.

Regards,

Hello Somendra,

Thank you for your solution. It works perfect

Is it possible to do this solution dynamically? If the User enters extends the table, and forgets to add those table columns manually in Sheet2, then it will miss those new columns in the combobox.

Looking forward to hearing from you.

Regards
Don

 

[Somendra Misra]

@inddon

See the file. I had included a TRANSPOSE function on Sheet2 Which will expand upto more 13 columns in table. I had also changed the named range List to be dynamic and also included LOOKUP_Table named range to be used in VBA.

Go through the same and and write back if you face any problem.

Regards,

 

[inddon]

@inddon

See the file. I had included a TRANSPOSE function on Sheet2 Which will expand upto more 13 columns in table. I had also changed the named range List to be dynamic and also included LOOKUP_Table named range to be used in VBA.

Go through the same and and write back if you face any problem.

Regards,

Thanks Somendra, it works perfect.

I have 3 questions:

1. Is there a way to do it without using an extra worksheet to reference. I mean to do it in VBA using the loop to get the column names of the table and fill the combobox?


2. Currently, it looks for a complete string match in a column.

I wish if it could look for a partial string eg. List all rows which has 'Customer Name' includes string 'ork'. I will include a check box. If it is checked then it is partial search, else unchecked is complete string search.

3. Currently your solution works for both (Integer/dates). Does it need a seperate code for this?

Looking forward to hearing from you

Regards,
Don

 

[inddon]

@inddon

See the file. I had included a TRANSPOSE function on Sheet2 Which will expand upto more 13 columns in table. I had also changed the named range List to be dynamic and also included LOOKUP_Table named range to be used in VBA.

Go through the same and and write back if you face any problem.

Regards,

Hello Somendra,

Someone helped me in getting point 1 to work: to get the combobox filled with the Table column names with the loop method. (attaching the workbook for your reference)

Could you please advise on the other points, when you are free.

Thanks & regards
Don

 

[Somendra Misra]

Hi Don,

Since you got your first query solved, here are the suggestion for 2 and 3.

2. For partial search you can use wild cards like *ORE* that will give the list of data which is there in customer name containing ORE. You can also use relational operator for fields with numbers like >300, say for quantity.

Just try it.

3. No there are no separate code for dates/strings or integers.

Regards,

 

[inddon]

Hi Don,

Since you got your first query solved, here are the suggestion for 2 and 3.

2. For partial search you can use wild cards like *ORE* that will give the list of data which is there in customer name containing ORE. You can also use relational operator for fields with numbers like >300, say for quantity.

Just try it.

3. No there are no separate code for dates/strings or integers.

Regards,

Hello Somendra,

Thank you for your suggestion

Seems I am doing something wrong in the below code syntax with wildcards:

ActiveSheet.ListObjects("Sheet1").Range.AutoFilter Field:=Me.ComboBox1.Value, Criteria1:="*" & Me.Textbox1.Value & "*"

Could you please advice, on the code for partial and full search code?

Thanks a lot

Regards
Don[/code]

 

[Somendra Misra]

You need not to insert wild card on the code. Type the char *OBR* in search text box.

Regards,

 

[inddon]

You need not to insert wild card on the code. Type the char *OBR* in search text box.

Regards,

phew....

Hello Somendra,

I am selecting a column name from the combo box (Customer), and in text field inserting *OBR* , it gives me an error for the below code:

Could you please advise?

ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=Me.ComboBox1.Value, Criteria1:=Me.TextBox1.Value

Thanks & regards
Don

 

[Somendra Misra]

@inddon

Here are the two result I am getting, and I had changed nothing in the code.





Regards,

 

[inddon]

@inddon

Here are the two result I am getting, and I had changed nothing in the code.

View attachment 10951

View attachment 10952

Regards,

Hello Somendra,

Your version works perfect, which uses the lookup from worksheet Sheet2

ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=Application.WorksheetFunction.VLookup(Me.ComboBox1.Value, Sheets("Sheet2").Range("Lookup_Table"), 2, 0), Criteria1:= _
  Me.TextBox1.Value

After I included the VBA for dynamically getting the Table Column names, the above code is changed to and that seems to give problem:

'----------------
' changed code
'----------------
ActiveSheet.ListObjects("Sheet1").Range.AutoFilter Field:=Me.ComboBox1.Value, Criteria1:="*" & Me.Textbox1.Value & "*"

I am attaching this workbook for your reference. Please have a look and advise me.

Thanks a ton

regards
Don

 

[Somendra Misra]

See the file.

Regards,

 

[inddon]

See the file.

Regards,

Wondeful. Thanks a lot Somendra

regards
Don

 

[Somendra Misra]

You are Welcome Don!!!

Regards,

 

[pradisdeep]

In this case, How to search for an approximate match?