Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
data filter
I have a workbook that has 3 worksheets in it. sheet one has an inflation
index. Sheet 2 is data entry for quotes. Sheet 3 is a duplicate of sheet 2 with the quote price inflated if a cell on Sheet 2 indicates to do so. Sheet 2 and 3 can be autofiltered to view certain locations. If you are going to auto filter sheet 2 to enter quotes only for a certain location(s), then you will want to see only those same locations on sheet 3 inflated. Is there any way to program the autofilter arrows so that if you filter sheet 2, it performs the same filter on sheet 3 so that the user doesn't have to repeat the steps? Sheet 3 is all formulas, so there is no user intervention to sheet 3 at all other than the fact that they have to re-filter. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
data filter
Hello Pam,
If you right click on the Sheet3 name and select View code and copy the following code into the VBA editor then each time you activate sheet3 it should set the filters the same as sheet2. However with a caveat. If you are using xl2007 you cannot set more than 2 options in a single dropdown filter. (previous versions you can only select one or with custom you get to pick 2 using between etc.) Private Sub Worksheet_Activate() Dim ws2 As Worksheet Dim ws3 As Worksheet Dim i As Long Set ws2 = Worksheets("Sheet2") Set ws3 = Worksheets("Sheet3") 'Ensure autofilter is invoked on Sheet3 With ws3 If Not .AutoFilterMode Then .UsedRange.AutoFilter End If End With With ws2 'Test if Autofilter invoked on sheet2 If .AutoFilterMode Then 'Test if an actual filter is set If .FilterMode Then With .AutoFilter 'Test each filter For i = 1 To .Filters.Count With .Filters(i) If .On Then If .Operator = 0 Then ws3.AutoFilter.Range _ .AutoFilter _ Field:=i, _ Criteria1:=.Criteria1 Else ws3.AutoFilter.Range _ .AutoFilter _ Field:=i, _ Criteria1:=.Criteria1, _ Operator:=.Operator, _ Criteria2:=.Criteria2 End If End If End With Next i End With Else If ws3.FilterMode Then ws3.ShowAllData End If End If Else If ws3.FilterMode Then ws3.ShowAllData End If End If End With End Sub -- Regards, OssieMac |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
data filter
thank you for the response. I am using Excel 2003. When I invoke the macro,
I am getting a runtime error that I "cannot use this command in a protected sheet" and the debugger is on the Autofilter section for ws3. I do have the protection set to allow autofilter, so I'm not sure why it won't allow the command. "OssieMac" wrote: Hello Pam, If you right click on the Sheet3 name and select View code and copy the following code into the VBA editor then each time you activate sheet3 it should set the filters the same as sheet2. However with a caveat. If you are using xl2007 you cannot set more than 2 options in a single dropdown filter. (previous versions you can only select one or with custom you get to pick 2 using between etc.) Private Sub Worksheet_Activate() Dim ws2 As Worksheet Dim ws3 As Worksheet Dim i As Long Set ws2 = Worksheets("Sheet2") Set ws3 = Worksheets("Sheet3") 'Ensure autofilter is invoked on Sheet3 With ws3 If Not .AutoFilterMode Then .UsedRange.AutoFilter End If End With With ws2 'Test if Autofilter invoked on sheet2 If .AutoFilterMode Then 'Test if an actual filter is set If .FilterMode Then With .AutoFilter 'Test each filter For i = 1 To .Filters.Count With .Filters(i) If .On Then If .Operator = 0 Then ws3.AutoFilter.Range _ .AutoFilter _ Field:=i, _ Criteria1:=.Criteria1 Else ws3.AutoFilter.Range _ .AutoFilter _ Field:=i, _ Criteria1:=.Criteria1, _ Operator:=.Operator, _ Criteria2:=.Criteria2 End If End If End With Next i End With Else If ws3.FilterMode Then ws3.ShowAllData End If End If Else If ws3.FilterMode Then ws3.ShowAllData End If End If End With End Sub -- Regards, OssieMac |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
data filter
There are 2 methods of fixing this. One is to include unprotect and protect
code within the code being run. The other method is to use a separate macro to invoke the protection and set the UserInterfaceOnly parameter. (I dont think this can be done in the userinterface mode). The second method you can delete the macro after setting the protection so the password is not visible in the code but make sure you keep a copy of the password somewhere. Also note that there was a bug in the previous code. You will see a comment where I added a line of code. It caused a problem if more than one filter was set then later one of the filters was turned off. Following code for first method Private Sub Worksheet_Activate() Dim ws2 As Worksheet Dim ws3 As Worksheet Dim i As Long Set ws2 = Worksheets("Sheet2") Set ws3 = Worksheets("Sheet3") 'Edit the password to your password. ws3.Unprotect Password:="ossiemac" 'Ensure autofilter is invoked on Sheet3 With ws3 If Not .AutoFilterMode Then .UsedRange.AutoFilter End If 'Following line added to code to remove a bug If .FilterMode Then ShowAllData End With With ws2 'Test if Autofilter invoked on sheet2 If .AutoFilterMode Then 'Test if an actual filter is set If .FilterMode Then With .AutoFilter 'Test each filter For i = 1 To .Filters.Count With .Filters(i) If .On Then If .Operator = 0 Then ws3.AutoFilter.Range _ .AutoFilter _ Field:=i, _ Criteria1:=.Criteria1 Else ws3.AutoFilter.Range _ .AutoFilter _ Field:=i, _ Criteria1:=.Criteria1, _ Operator:=.Operator, _ Criteria2:=.Criteria2 End If End If End With Next i End With Else If ws3.FilterMode Then ws3.ShowAllData End If End If Else If ws3.FilterMode Then ws3.ShowAllData End If End If End With ws3.Protect _ DrawingObjects:=False, _ Contents:=True, _ Scenarios:=False, _ AllowFiltering:=True, _ UserInterfaceOnly:=True, _ Password:="ossiemac" End Sub Second Method. Remove the protect and unprotect lines from the previous code and then run the following code on its own to protect the worksheet. You can copy it into the same sheet module as the other code, place the cursor anywhere in the sub and press F5 to run it from the VBA editor. Sub ProtectSheet3() Worksheets("Sheet3").Protect _ DrawingObjects:=False, _ Contents:=True, _ Scenarios:=False, _ AllowFiltering:=True, _ UserInterfaceOnly:=True, _ Password:="ossiemac" End Sub -- Regards, OssieMac |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
data filter
I chose method one, and it worked beautifully. I did add a line to reprotect
the sheet after the End With. thank you so much for your help. "OssieMac" wrote: There are 2 methods of fixing this. One is to include unprotect and protect code within the code being run. The other method is to use a separate macro to invoke the protection and set the UserInterfaceOnly parameter. (I dont think this can be done in the userinterface mode). The second method you can delete the macro after setting the protection so the password is not visible in the code but make sure you keep a copy of the password somewhere. Also note that there was a bug in the previous code. You will see a comment where I added a line of code. It caused a problem if more than one filter was set then later one of the filters was turned off. Following code for first method Private Sub Worksheet_Activate() Dim ws2 As Worksheet Dim ws3 As Worksheet Dim i As Long Set ws2 = Worksheets("Sheet2") Set ws3 = Worksheets("Sheet3") 'Edit the password to your password. ws3.Unprotect Password:="ossiemac" 'Ensure autofilter is invoked on Sheet3 With ws3 If Not .AutoFilterMode Then .UsedRange.AutoFilter End If 'Following line added to code to remove a bug If .FilterMode Then ShowAllData End With With ws2 'Test if Autofilter invoked on sheet2 If .AutoFilterMode Then 'Test if an actual filter is set If .FilterMode Then With .AutoFilter 'Test each filter For i = 1 To .Filters.Count With .Filters(i) If .On Then If .Operator = 0 Then ws3.AutoFilter.Range _ .AutoFilter _ Field:=i, _ Criteria1:=.Criteria1 Else ws3.AutoFilter.Range _ .AutoFilter _ Field:=i, _ Criteria1:=.Criteria1, _ Operator:=.Operator, _ Criteria2:=.Criteria2 End If End If End With Next i End With Else If ws3.FilterMode Then ws3.ShowAllData End If End If Else If ws3.FilterMode Then ws3.ShowAllData End If End If End With ws3.Protect _ DrawingObjects:=False, _ Contents:=True, _ Scenarios:=False, _ AllowFiltering:=True, _ UserInterfaceOnly:=True, _ Password:="ossiemac" End Sub Second Method. Remove the protect and unprotect lines from the previous code and then run the following code on its own to protect the worksheet. You can copy it into the same sheet module as the other code, place the cursor anywhere in the sub and press F5 to run it from the VBA editor. Sub ProtectSheet3() Worksheets("Sheet3").Protect _ DrawingObjects:=False, _ Contents:=True, _ Scenarios:=False, _ AllowFiltering:=True, _ UserInterfaceOnly:=True, _ Password:="ossiemac" End Sub -- Regards, OssieMac |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Filter, Cell Reference formula and copy formula on filter data | Excel Programming | |||
DataFilterAuto Filter in excel 2007? | New Users to Excel | |||
Data - Filter - Auto Filter | Excel Discussion (Misc queries) | |||
in data/filter/auto filter | Excel Worksheet Functions | |||
"Criteria Range" in the "Data/Filter/Advanced Filter" to select Du | Excel Worksheet Functions |