VBA to Protect sheets & keep some options on [Solved/Closed]

issa - Apr 13, 2011 at 01:54 AM - Latest reply:  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.
See more 

2 replies

RWomanizer
Posts
368
Registration date
Monday February 7, 2011
Status
Contributor
Last seen
September 30, 2013
- Apr 13, 2011 at 08:18 AM
0
Thank you
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 
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.