auto filter use with command button

[deen]

Dear All,

This program run in the work sheet , its working fine. but need to use
the program in use of command button is not working fine ,

can any body sort out issue(same program run thru command button).


Private Sub Worksheet_Change(ByVal Target As Range)
Const inputCell = "$B$1" ' must have the $ symbols
If Target.Address <> inputCell Then
Exit Sub
End If
Application.ScreenUpdating = False
Range("B2").Select ' get read to set AutoFilter
If IsEmpty(Target) Or _
Trim(Target) = "" Or _
UCase(Trim(Target)) = "ALL" Then
'need to show all
Selection.AutoFilter field:=2
Else
'need to filter by B1 entry
Selection.AutoFilter field:=2,
Criteria1:=UCase(Trim(Target.Value))
End If
Range(inputCell).Select
Application.ScreenUpdating = True
End Sub

Regars,

Deen

 

[OssieMac]

Not sure if I really understand the problem but I am interpreting it as you
want to run the code from a command button instead of a worksheet change
event. If this is correct then you can no longer use Target as the
Activecell. Assuming that you want to test for the Activecell = inputCell
then you could try the following in lieu of testing for Target:-

Const inputCell = "$B$1" ' must have the $ symbols
If ActiveCell.Address <> inputCell Then
Exit Sub
End If

and the rest of your code here.

 

[OssieMac]

Should have included that you would need to run the code from the Command
button event not worksheet change event.

 

[deen]

Hi,

I was Changed but is not working fine,


Private Sub CommandButton1_Click()
Const inputCell = "$B$1" ' must have the $ symbols
If ActiveCell.Address <> inputCell Then
Exit Sub
End If
Application.ScreenUpdating = False
Range("B2").Select ' get read to set AutoFilter
If IsEmpty(Target) Or _
Trim(Target) = "" Or _
UCase(Trim(Target)) = "ALL" Then
'need to show all
Selection.AutoFilter field:=2
Else
'need to filter by B1 entry
Selection.AutoFilter field:=2,
Criteria1:=UCase(Trim(Target.Value))
End If
Range(inputCell).Select
Application.ScreenUpdating = True
End Sub

 

[OssieMac]

I assume that where you are using Target, you mean the ActiveCell. Try
replacing Target with ActiveCell throughout your code. (You can't use target
with the Command Button).

If still not working, let me know which line it is failing on and what the
error message you are getting.

 

[deen]

Hi,

I was tried this also , is not working,


Private Sub CommandButton1_Click(Target As Range)
Const inputCell = "$B$1" ' must have the $ symbols
If ActiveCell.Address <> inputCell Then
Exit Sub
End If
Application.ScreenUpdating = False
Range("B2").Select ' get read to set AutoFilter
If IsEmpty(ActiveCell) Or _
Trim(ActiveCell) = "" Or _
UCase(Trim(ActiveCell)) = "ALL" Then
'need to show all
Selection.AutoFilter field:=2
Else
'need to filter by B1 entry
Selection.AutoFilter field:=2,
Criteria1:=UCase(Trim(ActiveCell.Value))
End If
Range(inputCell).Select
Application.ScreenUpdating = True
End Sub

 

[OssieMac]

Just a little extra. You can change the following line:-

ActiveSheet.Range("C1").AutoFilter Field:=2, _
Criteria1:=UCase(Trim(Range("B1").Value))

to this by just using Cells in lieu of Range("C1")

ActiveSheet.Cells.AutoFilter Field:=2, _
Criteria1:=UCase(Trim(Range("B1").Value))

 

[OssieMac]

I am not really sure that I understand exactly what you are trying to
achieve. However, the following macro will set Filter 2 to the value that is
in cell B1.
If no value in B1 or B1 is "ALL" then it resets filter 2 to All.

Note that you had an incorrect procedure name:-
Private Sub CommandButton1_Click(Target As Range)
You cannot have a parameter, Target, with a commandbutton event.

When using the following macro, you need to enter the required filter value
in B1 and PRESS ENTER before you click the command button.

Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
'Const inputCell = "$B$1" 'don't need this

If IsEmpty(Range("B1")) Or _
Trim(Range("B1")) = "" Or _
UCase(Trim(Range("B1"))) = "ALL" Then
'need to show all
Selection.AutoFilter Field:=2
Else
'need to filter by B1 entry
'NOTE: Range("C1") is any cell within the filtered range
ActiveSheet.Range("C1").AutoFilter Field:=2, _
Criteria1:=UCase(Trim(Range("B1").Value))
End If
'Range(inputCell).Select 'don't need this
Application.ScreenUpdating = True
End Sub

 

[deen]

Hi All,

is not working weather add some rows,data star with A7,

its shows error like , complie error , is not find in project or
library.


Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
'Const inputCell = "$B$1" 'don't need this
If IsEmpty(Range("B6")) Or _
Trim(Range("B6")) = "" Or _
UCase(Trim(Range("B6"))) = "ALL" Then
'need to show all
Selection.AutoFilter Field:=2
Else
'need to filter by B1 entry
'NOTE: Range("C1") is any cell within the filtered range
ActiveSheet.Range("b6").AutoFilter Field:=2, _
Criteria1:=UCase(Trim(Range("B6").Value))
End If
'Range(inputCell).Select 'don't need this
Application.ScreenUpdating = True

End Sub

 

[OssieMac]

It works for me because I tested it with some dummy data. Are you inserting
the macro in the correct place under the sheet name in the VBA editor or are
you putting it in a module. Right click the sheet name and select View code
and insert the code in the VBA editor where it opens. You will have to delete
it from any other area where you have inserted it if you have it in the wrong
place like a module.