auto filter use with command button
 

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

auto filter use with command button
 

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.


--
Regards,

OssieMac


"deen" wrote:

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

auto filter use with command button
 

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

OssieMac


"OssieMac" wrote:

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.


--
Regards,

OssieMac


"deen" wrote:

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

auto filter use with command button
 

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















On Apr 21, 11:29*am, OssieMac
wrote:
Should have included that you would need to run the code from the Command
button event not worksheet change event.
--
Regards,

OssieMac



"OssieMac" wrote:
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.

--
Regards,

OssieMac

"deen" wrote:

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- Hide quoted text -

- Show quoted text -

auto filter use with command button
 

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.
--
Regards,

OssieMac


"deen" wrote:

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















On Apr 21, 11:29 am, OssieMac
wrote:
Should have included that you would need to run the code from the Command
button event not worksheet change event.
--
Regards,

OssieMac



"OssieMac" wrote:
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.

--
Regards,

OssieMac

"deen" wrote:

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- Hide quoted text -

- Show quoted text -

auto filter use with command button
 

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




On Apr 21, 2:24*pm, OssieMac
wrote:
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.
--
Regards,

OssieMac



"deen" wrote:
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

On Apr 21, 11:29 am, OssieMac
wrote:
Should have included that you would need to run the code from the Command
button event not worksheet change event.
--
Regards,

OssieMac

"OssieMac" wrote:
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.

--
Regards,

OssieMac

"deen" wrote:

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- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

auto filter use with command button
 

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))

--
Regards,

OssieMac


"OssieMac" wrote:

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

--
Regards,

OssieMac


"deen" wrote:

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




On Apr 21, 2:24 pm, OssieMac
wrote:
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.
--
Regards,

OssieMac



"deen" wrote:
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

On Apr 21, 11:29 am, OssieMac
wrote:
Should have included that you would need to run the code from the Command
button event not worksheet change event.
--
Regards,

OssieMac

"OssieMac" wrote:
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.

--
Regards,

OssieMac

"deen" wrote:

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- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

auto filter use with command button
 

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

--
Regards,

OssieMac


"deen" wrote:

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




On Apr 21, 2:24 pm, OssieMac
wrote:
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.
--
Regards,

OssieMac



"deen" wrote:
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

On Apr 21, 11:29 am, OssieMac
wrote:
Should have included that you would need to run the code from the Command
button event not worksheet change event.
--
Regards,

OssieMac

"OssieMac" wrote:
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.

--
Regards,

OssieMac

"deen" wrote:

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- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

auto filter use with command button
 

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









On Apr 22, 4:59*am, OssieMac
wrote:
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))

--
Regards,

OssieMac



"OssieMac" wrote:
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

--
Regards,

OssieMac

"deen" wrote:

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

On Apr 21, 2:24 pm, OssieMac
wrote:
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.
--
Regards,

OssieMac

"deen" wrote:
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

On Apr 21, 11:29 am, OssieMac
wrote:
Should have included that you would need to run the code from the Command
button event not worksheet change event.
--
Regards,

OssieMac

"OssieMac" wrote:
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.

--
Regards,

OssieMac

"deen" wrote:

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- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

auto filter use with command button
 

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.

--
Regards,

OssieMac


"deen" wrote:

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









On Apr 22, 4:59 am, OssieMac
wrote:
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))

--
Regards,

OssieMac



"OssieMac" wrote:
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

--
Regards,

OssieMac

"deen" wrote:

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

On Apr 21, 2:24 pm, OssieMac
wrote:
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.
--
Regards,

OssieMac

"deen" wrote:
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

On Apr 21, 11:29 am, OssieMac
wrote:
Should have included that you would need to run the code from the Command
button event not worksheet change event.
--
Regards,

OssieMac

"OssieMac" wrote:
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.

--
Regards,

OssieMac

"deen" wrote:

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- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -