Filter on cell value

[adam]

Why doesn't the sheet filter when the number is written in custom format in the active sheets cell O6?

Amy help on this would be kindly appreciated.

Thanks in advance.

[HansV]

Change

Sheets("Mydata").Range("A4").AutoFilter Field:=2, Criteria1:=Range("O6").Value

to

Sheets("Mydata").Range("A4").AutoFilter Field:=2, Criteria1:=Range("O6").Text

[adam]

Thanks for the help Hans. The intention of the following code is to unfilter the sheet "MyData" when the value in cell O6 of the active sheet gets cleared. But the code does not seem to work.

What may be the reason behind this? Code is as follows;

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
    If Sheets("Mydata").AutoFilterMode Then
        Range("B4").AutoFilter
    End If
    Sheets("MyData").Range("O6").Text = ""
End Sub

[HansV]

You should test whether cell O6 has been cleared. You have seen many examples of such code in the past.

[adam]

Thanks for the help Hans.

[adam]

At present the following code filters the sheet's column B by the value on text box 1.

My question of concern is how to change the code so that it filters the columns simultaneously when the user types the values in appropriate text boxes and combo boxes.

Let's say for example;
to change the code to filter the column B by textbox 1 value. Column D by combobox1 value and column E by text box2 value.

Any help on this would be kindly appreciatd.

Thanks in advance.

Code: Select all

Sub Filter()
    If UCase(Sheets("Mydata").Range("B5").Value) <> "ALL" Then
        Range("A4").AutoFilter Field:=2, Criteria1:=Sheets("Mydata").TextBox1.Text
    Else
        If Sheets("Mydata").AutoFilterMode Then
            Sheets("Mydata").Range("A4").AutoFilter
        End If
    End If
End Sub

[HansV]

What is the relationship between Sheets("Mydata").Range("B5") and Sheets("Mydata").TextBox1? Is cell B5 the linked cell of the text box?

[adam]

Sheets "MyData"s column headers are on row 4. And the columns where filter is applied starts from column"A" to column"N".

At present the code filters the sheet by column B when the search value is written on textbox1. Meaning cell B5 is the linked cell of the text box.

I hope this details my question.

[HansV]

If you have column headers in A4:N4 on the MyData sheet, cell B5 would be part of the data, so I don't understand why it would be the linked cell of a text box.

[adam]

Nevermind, I guess I've figured that out.

[VegasNath]

Out of interest, how did you 'figure it out'?

[adam]

A change of mind did that. Meaning I quit my previous intention.