VB protect sheet but allow certain options

[excel-chump]

Is there a way to use vb to not just protect a sheet but to choose specific
items in the "Protect Worksheet" dialogue box. (The, "Allow all users of this
worksheet to:" check boxes.)

This is 2003

 

[Gleam]

Do you know about Format / Cells / Protection ?

 

[Otto Moehrbach]

Record a macro while you do it manually, to get the code. HTH Otto

 

[Dave Peterson]

Record a macro when you choose the items you want chosen. You'll see the code.

 

[excel-chump]

Dave,
So obvious! Thanks. Also a another question: I have this in the worksheet
code.
'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
ActiveSheet.Unprotect
Const WS_RANGE As String = "g3:f38" '<=== change to suit


On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Select Case .Value
Case "YES": .Interior.ColorIndex = 4 'green
Case "NO": .Interior.ColorIndex = 3 'red
Case "A1": .Interior.ColorIndex = 12 'dark yellow
Case "A2": .Interior.ColorIndex = 6 'yellow
Case 0: .Interior.ColorIndex = 19 'blank

End Select
End With
End If

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowSorting:=True, AllowFiltering:=True
ws_exit:
Application.EnableEvents = True
End Sub

When I set the worksheet as shared it no longer works and I get an error
message:

"Run-time error '1004': Unprotect method of Worksheet class failed."

Are there any suggestions on how to work around that?

 

[Dave Peterson]

You can't change the worksheet protection in a shared workbook.

You'll have to choose--either use protection or use sharing, but not both.