Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using Autofilter on Protected sheets
Hi,
I am having trouble using autofilter once I protect my worksheets. I run a mcaro and the code is below. I am using Excel 2003 and ticking the auto-filter box when I protect the sheet aswell but once I save & close the sheet and reopen to test, it doesn't allow me autofilter and sheets are protect and will not allow any changes. Can someone help please. Sub ExceptionsDays() ' ' Dim MyDate As Date, StringDate As String With Sheets("Daily Sheet") StringDate = InputBox("Enter Date") If IsDate(StringDate) Then MyDate = DateValue(StringDate) .Range("E2") = MyDate Else MsgBox "Invalid Date" Exit Sub End If Sheets("Daily Sheet").Unprotect Password:="yogin" .Range("$B$4:$D$1000").AutoFilter Field:=1 .Range("$B$4:$D$1000").AutoFilter Field:=4 .Range("$B$4:$D$1000").AutoFilter Field:=4, Criteria1:="DEX" .EnableAutoFilter = True Sheets("Daily Sheet").Protect Password:="yogin" .EnableAutoFilter = True Sheets("Daily Sheet").Select End With End Sub Thanks Yogin |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using Autofilter on Protected sheets
Hi
The range you try to filter does only have 3 columns ie 3 fields, and your code tries to filter on field 4. To allow users to manipulate the autofilter criterias, see the line that protect the sheet below: Sub ExceptionsDays() ' ' Dim MyDate As Date, StringDate As String StringDate = InputBox("Enter Date") If IsDate(StringDate) Then MyDate = DateValue(StringDate) Else MsgBox "Invalid Date" Exit Sub End If With Sheets("Daily Sheet") .Unprotect password:="yogin" .Range("$B$4:$D$1000").AutoFilter .Range("E2") = MyDate .Range("$B$4:$D$1000").AutoFilter Field:=4, Criteria1:="DEX" .EnableAutoFilter = True .Protect password:="yogin", userInterfaceOnly:=True .Select End With End Sub Hopes it helps Best Regards, Per Yogin" skrev i meddelelsen ... Hi, I am having trouble using autofilter once I protect my worksheets. I run a mcaro and the code is below. I am using Excel 2003 and ticking the auto-filter box when I protect the sheet aswell but once I save & close the sheet and reopen to test, it doesn't allow me autofilter and sheets are protect and will not allow any changes. Can someone help please. Sub ExceptionsDays() ' ' Dim MyDate As Date, StringDate As String With Sheets("Daily Sheet") StringDate = InputBox("Enter Date") If IsDate(StringDate) Then MyDate = DateValue(StringDate) .Range("E2") = MyDate Else MsgBox "Invalid Date" Exit Sub End If Sheets("Daily Sheet").Unprotect Password:="yogin" .Range("$B$4:$D$1000").AutoFilter Field:=1 .Range("$B$4:$D$1000").AutoFilter Field:=4 .Range("$B$4:$D$1000").AutoFilter Field:=4, Criteria1:="DEX" .EnableAutoFilter = True Sheets("Daily Sheet").Protect Password:="yogin" .EnableAutoFilter = True Sheets("Daily Sheet").Select End With End Sub Thanks Yogin |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using Autofilter on Protected sheets
Per - Thank you for your help.
I modified the code slightly as I was getting an error when I ran the code you gave, See below. Also thank you for pointing out that I have only selected 3 columns instead fo 4. i will amend this. --------------------------------- Sub ExceptionsDays() ' ' Dim MyDate As Date, StringDate As String With Sheets("Daily Sheet") StringDate = InputBox("Enter Date") If IsDate(StringDate) Then MyDate = DateValue(StringDate) .Range("E2") = MyDate Else MsgBox "Invalid Date" Exit Sub End If Sheets("Daily Sheet").Unprotect Password:="yogin" .Range("$B$4:$E$1000").AutoFilter Field:=1 .Range("$B$4:$E$1000").AutoFilter Field:=4 .Range("$B$4:$E$1000").AutoFilter Field:=4, Criteria1:="DEX" Sheets("Daily Sheet").EnableAutoFilter = True Sheets("Daily Sheet").Protect Password:="yogin", userInterfaceOnly:=True Sheets("Daily Sheet").Select End With End Sub "Per Jessen" wrote: Hi The range you try to filter does only have 3 columns ie 3 fields, and your code tries to filter on field 4. To allow users to manipulate the autofilter criterias, see the line that protect the sheet below: Sub ExceptionsDays() ' ' Dim MyDate As Date, StringDate As String StringDate = InputBox("Enter Date") If IsDate(StringDate) Then MyDate = DateValue(StringDate) Else MsgBox "Invalid Date" Exit Sub End If With Sheets("Daily Sheet") .Unprotect password:="yogin" .Range("$B$4:$D$1000").AutoFilter .Range("E2") = MyDate .Range("$B$4:$D$1000").AutoFilter Field:=4, Criteria1:="DEX" .EnableAutoFilter = True .Protect password:="yogin", userInterfaceOnly:=True .Select End With End Sub Hopes it helps Best Regards, Per Yogin" skrev i meddelelsen ... Hi, I am having trouble using autofilter once I protect my worksheets. I run a mcaro and the code is below. I am using Excel 2003 and ticking the auto-filter box when I protect the sheet aswell but once I save & close the sheet and reopen to test, it doesn't allow me autofilter and sheets are protect and will not allow any changes. Can someone help please. Sub ExceptionsDays() ' ' Dim MyDate As Date, StringDate As String With Sheets("Daily Sheet") StringDate = InputBox("Enter Date") If IsDate(StringDate) Then MyDate = DateValue(StringDate) .Range("E2") = MyDate Else MsgBox "Invalid Date" Exit Sub End If Sheets("Daily Sheet").Unprotect Password:="yogin" .Range("$B$4:$D$1000").AutoFilter Field:=1 .Range("$B$4:$D$1000").AutoFilter Field:=4 .Range("$B$4:$D$1000").AutoFilter Field:=4, Criteria1:="DEX" .EnableAutoFilter = True Sheets("Daily Sheet").Protect Password:="yogin" .EnableAutoFilter = True Sheets("Daily Sheet").Select End With End Sub Thanks Yogin |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using Autofilter on Protected sheets
Hi
Thanks for your reply. I'm glad that I could help. Best regards, Per "Yogin" skrev i meddelelsen ... Per - Thank you for your help. I modified the code slightly as I was getting an error when I ran the code you gave, See below. Also thank you for pointing out that I have only selected 3 columns instead fo 4. i will amend this. --------------------------------- Sub ExceptionsDays() ' ' Dim MyDate As Date, StringDate As String With Sheets("Daily Sheet") StringDate = InputBox("Enter Date") If IsDate(StringDate) Then MyDate = DateValue(StringDate) .Range("E2") = MyDate Else MsgBox "Invalid Date" Exit Sub End If Sheets("Daily Sheet").Unprotect Password:="yogin" .Range("$B$4:$E$1000").AutoFilter Field:=1 .Range("$B$4:$E$1000").AutoFilter Field:=4 .Range("$B$4:$E$1000").AutoFilter Field:=4, Criteria1:="DEX" Sheets("Daily Sheet").EnableAutoFilter = True Sheets("Daily Sheet").Protect Password:="yogin", userInterfaceOnly:=True Sheets("Daily Sheet").Select End With End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Autofilter in protected sheet | Excel Worksheet Functions | |||
Using Autofilter on a Protected Worksheet | Excel Discussion (Misc queries) | |||
Applying autofilter to protected sheet | Excel Discussion (Misc queries) | |||
Autofilter on protected work book? | Excel Discussion (Misc queries) | |||
Autofilter on protected workbook | Excel Discussion (Misc queries) |