VBA to Protect sheets & keep some options on

Solved/Closed
issa - Apr 13, 2011 at 01:54 AM
 Issa - Apr 13, 2011 at 01:19 PM
Dears,

I will be very grateful if some one can help me. I have excel file include 50 sheets, so I made one sheet that include all the basis info for all the sheets and at the same time I made command button to protect/Unprotect all the sheets in one command.

This is the commands:

Private Sub CommandButton1_Click()
Dim WS As Worksheet
Dim PW As String

PW = "secret" ' to change Password
For Each WS In Worksheets
WS.Protect Password:=PW
Next WS
End Sub
Private Sub CommandButton2_Click()
Dim WS As Worksheet
Dim PW As String

PW = "secret" ' to change Password
For Each WS In Worksheets
WS.Unprotect Password:=PW
Next WS
End Sub


So what I need is to add something to those VBA to keep some of the options active, I mean if you go to: Tools/Protections .. in seen box/window you can select in checkbox for the options, i.e:
1. Format Rows 2. Edit object
Which means that even if the sheet protected, the user can select objects and can change the row's formats as I selected.

Therefore what should I add to this VBA to activate those two options.

Many thanks in advance for any kind assistance.
Related:

1 response

RWomanizer Posts 365 Registration date Monday February 7, 2011 Status Contributor Last seen September 30, 2013 120
Apr 13, 2011 at 08:18 AM
replace following line in your code of Private Sub CommandButton1_Click()

WS.Protect Password:=PW  


with
WS.Protect Password:=PW, DrawingObjects:=False, Contents:=True, Scenarios:= True, AllowFormattingRows:=True 
WS.EnableSelection = xlNoSelection 
0
Dear RWomanizer,

Thank you very much, it works as I need, really thank you and I appreciate it.

Sorry, but I need now something else if you can, I have 50 sheets in excel and all are the same in every thing (format, no of rows, columns, ...etc), so each sheet belong to one supplier and in each sheet there are 3 checkboxes. I already made VBA for each checkbox that if I clicked the checkbox (True) the VBA will do something (i.e. to hide some rows, ...etc), but what I need now to make module instead of copying and pasting the same VBA in all 50 sheets, can I do it??

Here is my VBA:

Private Sub CheckBox1_Click()
If CheckBox15 = False Then
Range("A52:W89").Select
Selection.EntireRow.Hidden = True
Else:
Range("A52:W89").Select
Selection.EntireRow.Hidden = False
End If
End Sub
*****************************
Private Sub CheckBox2_Click()
If CheckBox16 = False Then
Range("A90:W132").Select
Selection.EntireRow.Hidden = True
Else:
Range("A90:W132").Select
Selection.EntireRow.Hidden = False
End If
End Sub


Many thanks in advance.
0