| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 - |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 - |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Separate Button for Auto Filter Selected Range | New Users to Excel | |||
| Auto Filter Toolbar Button Does not Toggle | Excel Discussion (Misc queries) | |||
| Auto filter through the Comman Button | Excel Worksheet Functions | |||
| Is it possible to change the blue colour of an activated Auto Filter button? | Excel Discussion (Misc queries) | |||
| The Auto Filter button lost the column specified option. | Excel Worksheet Functions | |||
Hybrid Mode
