protect and unprotect all sheets by a command button

uttamsaxena

Board Regular
Joined
Apr 22, 2003
Messages
179
I have a workbook with 10 sheets, which all are protected by the password(Tools>Protection>protect) . Users only feed data and any change in sheet is to be done by me every month. For editing something in each sheet, I have to protect and unprotect all sheets one by one.How can this be done by a command button on sheet one, which should ask for the password and on correct password shall unprotect all the sheets. Similarly another button on sheet 1 should lock all the sheets on confirmation of a password.

However for unprotecting and protecting single sheet, the normal method (Tools>Protection>unprotect) should do.
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Cbrine

Well-known Member
Joined
Dec 2, 2003
Messages
3,196
uttamsaxena said:
I have a workbook with 10 sheets, which all are protected by the password(Tools>Protection>protect) . Users only feed data and any change in sheet is to be done by me every month. For editing something in each sheet, I have to protect and unprotect all sheets one by one.How can this be done by a command button on sheet one, which should ask for the password and on correct password shall unprotect all the sheets. Similarly another button on sheet 1 should lock all the sheets on confirmation of a password.

However for unprotecting and protecting single sheet, the normal method (Tools>Protection>unprotect) should do.

Private Sub CommandButton1_Click()
Dim ws As Worksheet
dim Password as string
Password = Inputbox "Password"
For Each ws In ActiveWorkbook.Worksheets
If ws.ProtectContents = True Then
ws.Unprotect Password
Else
ws.Protect Password
End If
Next
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,114,093
Messages
5,545,919
Members
410,712
Latest member
jhgeorge
Top