help needed from someone with xl 2000

[Don Wiss]

Please go to VBA help file and look up the Protect Method as it applies to
a Worksheet object. In xl 2002 this has been expanded. We don't have an xl
2000 around here to know just what was added.

We use these arguments: DrawingObjects, Contents, UserInterfaceOnly,
AllowFormattingCells, AllowFiltering. Do any of these exist in xl 2000?

Thanks, Don <www.donwiss.com> (e-mail link at home page bottom).

 

[JMB]

AllowFormatting and AllowFiltering appear to be new to the protect method.
From the help file:

expression.Protect(Password, DrawingObjects, Contents, Scenarios,
UserInterfaceOnly)

There is a property to enable the autofilter. I've only used it once
before. I don't think it is persistent in that when the workbook is saved,
closed, and reopened it has to be reset. I think I used a Workbook_Open
event handler to set it each time the workbook was opened. Also, it only
works when UserInterFaceOnly is activated.

ActiveSheet.EnableAutoFilter = True
ActiveSheet.Protect contents:=True, userInterfaceOnly:=True

 

[GS]

Hi Don,

To add to JMB's reply, v2002 took a big leap over v2000 by adding the
protection class, and its associated options. Ordinarily, protection options
applied in v2002 and later would be ignored in v2000 as "backward
compatibility" usually provides that. A problem will arise trying to
"programmatically" apply these options if your project is run in v2000 using
the later version options. Here's a sub that encapsulates this issue and some
of the most common non-persistent properties. You can use it for both v2000
and later versions.

Sub wksProtect()
' This lists all the members of the Protection class.
' Move rows around to list desired settings first,
' then comment out the lower (unwanted) settings.

With ActiveSheet
If val(Application.Version) >= 10 Then
.Protect Password:=gszPwrd, _
DrawingObjects:=True, _
Contents:=True, _
Scenarios:=True, _
Userinterfaceonly:=True, _
AllowFiltering:=True, _
AllowFormattingColumns:=True, _
AllowFormattingRows:=True, _
AllowDeletingColumns:=True, _
AllowDeletingRows:=True, _
AllowFormattingCells:=True, _
AllowInsertingColumns:=True, _
AllowInsertingRows:=True ', _
AllowInsertingHyperlinks:=True, _
AllowUsingPivotTables:=True
Else
.Protect Password:=gszPwrd, _
DrawingObjects:=True, _
Contents:=True, _
Scenarios:=True, _
Userinterfaceonly:=True 'Non-persistent:This one must be reset (all
versions) when the workbook is re-opened. **Requires unprotecting first**
End If

'Non-persistent settings
'These must be reset when the workbook is re-opened
'UnComment the desired setting only
' .EnableSelection = xlNoRestrictions
.EnableSelection = xlUnlockedCells
' .EnableSelection = xlNoSelection

.EnableAutoFilter = True
' .EnableAutoFilter = False
End With 'ActiveSheet

End Sub

HTH
Regards,
Garry