Protected sheet - Autofilter - Excel 97

[Midnight]

Hi,

I've been searching for a solution to my problem. I have created a big
spreadsheet and would like to hide the formulas used. The only way I
can think to do this is by hiding the cell contents in the cell
properties and then protecting the worksheet.

The problem with this is that multiple sheets (that need the formulas
hidden) make use of autofilters.

I'm using Excel97 and am aware the only way around the problem is with
VB (which I'm in my infancy in learning).

I found the code below but am having problems with it. There was
initially a couple of lines which appeared to say 'If autofilter is
off, then activate'. I took this out because I only want the
autofilters that have already been set-up.

The code I have (which I pasted into 'This Workbook' object )
---------------------
Private Sub Workbook_Open()

With Worksheets("Timesheets")

EnableAutoFilter = True
Protect Password:="123", _
Contents:=True, UserInterfaceOnly:=True
End With
End Sub


Timesheets is the name of the first workbook
123 is the password (for now).

Firstly, I'd like to know why the code is still not allowing use of
the autofilter when the sheet is protected and how I can fix it.
Secondly, at the moment the code attempts to activate the autofilter
on the Timesheets worksheet only. How do I go about adding the four
additional sheets that also have autofilters. Do I set-up a new piece
of code for each, or could they all be included in one section of
code?

Thanks.

 

[Norman Jones]

Hi Midnight,

I suspect that you were missing an initial point (full stop)
before EnableAutoFilter and before Protect.


Try:
'=============>>
Private Sub Workbook_Open()
Dim SH As Worksheet
Const PWORD As String = "123" '<<=== CHANGE

For Each SH In Me.Worksheets
With SH
.EnableAutoFilter = True
.Protect Password:=PWORD, _
Contents:=True, _
UserInterfaceOnly:=True
End With
Next SH
End Sub
'<<=============

This is workbook event code and should be pasted into
the workbook's ThisWorkbook module *not* a standard
module or a sheet module:

Right-click the Excel icon on the worksheet (or the icon to
the left of the File menu if your workbook is maximised)
Select 'View Code' from the menu and paste the code.
Alt-F11 to return to Excel.

 

[Tom Ogilvy]

Just to add. In xl97, I have seen weird behavior in excel when you protect a
sheet in the workbook open event and it isn't active. YOu might add:

Private Sub Workbook_Open()
Dim SH As Worksheet
Dim sh1 as Worksheet
Const PWORD As String = "123" '<<=== CHANGE
set sh1 = Activesheet
Application.ScreenUpdating = False
For Each SH In Me.Worksheets
With SH
.Activate
.EnableAutoFilter = True
.Protect Password:=PWORD, _
Contents:=True, _
UserInterfaceOnly:=True
End With
Next SH
sh1.Activate
Application.ScreenUpdating = True
End Sub

It may not be an issue, but the additions shouldn't be detrimental.

 

[Norman Jones]

Hi Tom,

'-------------------
Just to add. In xl97, I have seen weird behavior in excel when you protect
a
sheet in the workbook open event and it isn't active. YOu might add:

Private Sub Workbook_Open()
Dim SH As Worksheet
Dim sh1 as Worksheet
Const PWORD As String = "123" '<<=== CHANGE
set sh1 = Activesheet
Application.ScreenUpdating = False
For Each SH In Me.Worksheets
With SH
.Activate
.EnableAutoFilter = True
.Protect Password:=PWORD, _
Contents:=True, _
UserInterfaceOnly:=True
End With
Next SH
sh1.Activate
Application.ScreenUpdating = True
End Sub

It may not be an issue, but the additions shouldn't be detrimental.
'-------------------

Thank you for the suggestion.

I do not recall (or was unaware) of the potential xl97 behaviour!

 

[Tom Ogilvy]

It may not be universal, but I definitely ran into it. It had to do with how
cells were highlighted during selection - as I recall, they weren't when
working on sheets that were not active when they were protected in the
workbook open event. I don't think I ever saw anything in the KB and
certainly didn't spend much time brooding about it since it was so easily
rectified by activating the sheet.

 

[Norman Jones]

Hi Tom,

'-----------------
It may not be universal, but I definitely ran into it. It had to do with
how
cells were highlighted during selection - as I recall, they weren't when
working on sheets that were not active when they were protected in the
workbook open event. I don't think I ever saw anything in the KB and
certainly didn't spend much time brooding about it since it was so easily
rectified by activating the sheet.
'-----------------

Very interesting!

Thank you for the additional backgound detail.

I shall be very circumspect in future before I advocate
the removal of Activate from an OP's code!

 

[Tom Ogilvy]

As long as your circumspections don't become ruminations and your late to the
party <g>.