To enable changing locked cells of a protected sheet via VBA you must
set the *UserInterfaceOnly* parameter of the Protect method to
*True*...
Sheets("xxxx").Protect UserInterfaceOnly:=True
...if you don't want to toggle protection for every change. Note that
the UserInterfaceOnly parameter does not persist between sessions, and
so protection must be 'reset' each time the file is opened. Note also
that protection *must be removed* in order to reset it...
In the Workbook_Open event (or Sub Auto_Open n a sandard module):
Sub ResetProtection(Optional Wks As Worksheet)
If Wks Is Nothing Then Set Wks = ActiveSheet
With Wks
.Unprotect: .Protect UserInterfaceOnly:=True
End With 'Wks
End Sub 'ResetProtection
...and this is called in the Workbook_Open event as follows.
Call ResetProtection
Note that if you use a password to protect sheets, it must be added...
In the declaration section of a standard module:
Public gsPWD$ = " " '//replace the space as desired
Sub ResetProtection(Optional Wks As Worksheet)
If Wks Is Nothing Then Set Wks = ActiveSheet
With Wks
.Unprotect Password:=gsPWD
.Protect Password:=gsPWD, UserInterfaceOnly:=True
End With 'Wks
End Sub 'ResetProtection
There are other protection parameters as well, which you may want to
apply when protecting sheets and so should also be included...
Public Const PWRD$ = "" '//none
Sub wksProtect(Optional Wks As Worksheet)
' Protects specified sheets according to Excel version.
' Assumes Public Const PWRD as String contains the password,
' even if there isn't one.
'
' Arguments: Wks [In] Optional. Ref to the sheet to be protected.
' (Defaults to ActiveSheet if missing)
If Wks Is Nothing Then Set Wks = ActiveSheet
With Wks
If Val(Application.VERSION) >= 10 Then
'Copy/paste the desired parameters above the commented line.
.Protect Password:=PWRD, _
DrawingObjects:=False, Contents:=True, Scenarios:=True, _
UserInterfaceOnly:=True, _
AllowFiltering:=True, _
AllowFormattingColumns:=True, _
AllowFormattingRows:=True, _
AllowFormattingCells:=True ', _
AllowDeletingColumns:=True, _
AllowDeletingRows:=True, _
AllowInsertingColumns:=True, _
AllowInsertingHyperlinks:=True, _
AllowInsertingRows:=True, _
AllowUsingPivotTables:=True
Else
.Protect Password:=PWRD, _
DrawingObjects:=False, Contents:=True, Scenarios:=True, _
UserInterfaceOnly:=True
End If
' .EnableAutoFilter = True
.EnableOutlining = True
' .EnableSelection = xlNoRestrictions
.EnableSelection = xlUnlockedCells
' .EnableSelection = xlNoSelection
End With
End Sub
...where the list can be modified by simply moving the apostrophe (see
the line "AllowFormattingCells = True") OR individual lines to the
desired location. This sub assumes the default settings and so needs
only to be edited to suit. (Everything after the apostrophe is
commented out) Note that this is a rudimentary approach to protection
management because the setting must be set manually beforehand.
Usage:
Sub ResetProtection(Optional Wks As Worksheet)
If Wks Is Nothing Then Set Wks = ActiveSheet
Wks.Unprotect PWRD: wksProtect Wks
End Sub
===
<FWIW>
The approach I now use is a bit more complex in that settings for
protected sheets are stored in a defined name "uiProtect" with local
scope. This is a delimited string that stores all possible settings as
either positive (>0) to set *True* or negative values (<1) to set
*False*. The string is dumped into an array and my procedure reads from
there for the target sheet. The EnableSelection property is set by a
value from 0-2 (0=xlNoRestrictions, 1=xlUnlockedCells,
2=xlNoSelection), read from the last element in the array.
Note that "EnableAutoFilter, EnableOutling, and EnableSelection are
properties of the Worksheet object, and have nothing to do with
protection. These settings are commonly applied when protection is
applied because they (except the last one) do not persist between
sessions. (I just think it's a convenient way for me to work with them)
HTH
--
Garry
Free usenet access at
http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
