Using ActiveX TextBox and Combo box to create Dynamic Search Function

[Jords998]

Hi, I'm pretty new to VBA & I was looking for a little help on a project.

I'm creating a date entry form / flat file database to save customer details. The file contains 33 columns of data starting at D22 and ending at AJ7000, I would like the search function to only look at a specific column based on a drop down list using an ActiveX combo box. I have created the drop down & linked this to cell B16. The Text Box is linked to cell B20.

I know I'm after something like -
If B16 = Customer Name set range to D22:D7000
If B16 = Customer Address set range to E22:E7000.
If D22 does not contain B20 value then hide row
Next row.

What VBA would I need to do this? I would like the the search function to hide any rows where the cell in the column selected doesn't contain the value in B20. I hope this makes sense

 

[Jords998]

Hi, I'm pretty new to VBA & I was looking for a little help on a project.

I'm creating a date entry form / flat file database to save customer details. The file contains 33 columns of data starting at D22 and ending at AJ7000, I would like the search function to only look at a specific column based on a drop down list using an ActiveX combo box. I have created the drop down & linked this to cell B16. The Text Box is linked to cell B20.

I know I'm after something like -
If B16 = Customer Name set range to D22:D7000
If B16 = Customer Address set range to E22:E7000.
If D22 does not contain B20 value then hide row
Next row.

What VBA would I need to do this? I would like the the search function to hide any rows where the cell in the column selected doesn't contain the value in B20. I hope this makes sense

I should have also mentioned I would like to unhide all rows in the data range if B20 is blank. I have a button linked to a macro to clear the contents of both B16 and B20

 

[mumps]

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet.
Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[Jords998]

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet.
Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

I have uploaded a de-sensitized version to box.com here Example File

For reference, ComboBox1 is now Cell B12 and the TextBox1 is now B13.

I would like the ComboBox1 to filter the search area to a single column, then TextBox1 to filter that column based on cells that contain the string entered.

I would also like the Clear Contents Button to remove the existing filter & display all results.

Please ignore all the wacky code that doesn't really have a purpose, its basically Frankenstein as I have found pieces of code online & put them together ?

 

[mumps]

This macro will do the filter based on B12 and B13:

Sub FilterData()
    Application.ScreenUpdating = False
    Dim fnd As Range
    Set fnd = Rows(20).Find(Range("B12").Value, LookIn:=xlValues, lookat:=xlWhole)
    If Not fnd Is Nothing Then
        Range("D20", Range("AJ" & Rows.Count).End(xlUp)).AutoFilter fnd.Column - 3, Range("B13").Value
    End If
    Application.ScreenUpdating = True
End Sub

This macro will show all the data and clear the filter criteria:

Sub ClearData()
    Range("D20").AutoFilter
    Range("B12:B13").ClearContents
End Sub

 

[Jords998]

This macro will do the filter based on B12 and B13:

Sub FilterData()
    Application.ScreenUpdating = False
    Dim fnd As Range
    Set fnd = Rows(20).Find(Range("B12").Value, LookIn:=xlValues, lookat:=xlWhole)
    If Not fnd Is Nothing Then
        Range("D20", Range("AJ" & Rows.Count).End(xlUp)).AutoFilter fnd.Column - 3, Range("B13").Value
    End If
    Application.ScreenUpdating = True
End Sub

This macro will show all the data and clear the filter criteria:

Sub ClearData()
    Range("D20").AutoFilter
    Range("B12:B13").ClearContents
End Sub

Works a treat, thank you very much

 

[mumps]

You are very welcome.