| 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 - |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 - |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 - |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 - |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 - |
| 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 | |||
Linear Mode
