I have a set of questionnaire form...
if A1="No" then disable/lock B1
if A2="No" then disable/lock B2
etc

if A1 is Yes then you can (enable/unlock) B1 and can accept values etc

can not seem to lock and protect the worksheet to do what I want....
I have been using worksheet change event....

I tried ....
Sheets("xxxx").range("A1:B39").Locked = False
Sheets("xxxx").protect

I then try and set specific cells to be locked = true
i.e
if range("A1")="No" then range("B1").locked = true
else range("B1").locked = False
end if


however this approach is not working....
can you suggest the best way to achieve my goal...
disable/enable or lock/unlock hide/unhide B1 dependent on A1
disable/enable or lock/unlock hide/unhide B2 dependent on A2

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

Geez.., I hate when the keys don't event: See typo fixes below...

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 in a standard 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, EnableOutlining, 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