ExcelBanter - Using Autofilter on Protected sheets

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Using Autofilter on Protected sheets (https://www.excelbanter.com/excel-discussion-misc-queries/197362-using-autofilter-protected-sheets.html)

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

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

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

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