Macros to Protect/Unprotect Worksheets and Workbooks

knksyon29 · Jun 23rd 2012

Re: Macros to Protect/Unprotect Worksheets and Workbooks

VB:

Code

Dim ws As Worksheet
Sub ProtectAll()
Dim S As Object
Dim pWord1 As String, pWord2 As String


pWord1 = InputBox("Please Enter the password")


If pWord1 = "" Then Exit Sub
   pWord2 = InputBox("Please re-enter the password")
If pWord2 = "" Then Exit Sub
   'make certain passwords are identical
If InStr(1, pWord2, pWord1, 0) = 0 Or _ 
   InStr(1, pWord1, pWord2, 0) = 0 Then
MsgBox "You entered different passwords. No action taken"
Exit Sub


End If
For Each ws In Worksheets
ws.Protect Password:=pWord1
Sheets("Sheet2").Unprotect Password:=pWord1
Next
MsgBox "All sheets Protected."
Exit Sub
End Sub

Display More

I need a revise code for these current code that i posted, since i cannot work into it..

my problem is, everytime i put a password on the protectall, it overwrites the previous password i already put it in.
for example, i put 12345 password for the first time and it did protect the whole sheet, but when i try to input another password for example 67890, it overwrites the 12345??

thanks for the help..

Whitterquick · Oct 19th 2012

Re: Macros to Protect/Unprotect Worksheets and Workbooks

Hello,
I have seen this issue come up a few times on multiple forums and asked a multitude of diferent ways.
It was a very frustrating problem and I hope this can be useful to anyone.

Here is reletively simple code that I have pulled together form many sources and then customizing it.
This code will use a command button to unprotect all worksheets within a workbook and notify the user of the state of the worksheets.

Code

Private Sub cmdGuardControl_Click()
'<<<<<<<<<<< Protects/Unprotects Worksheets >>>>>>>>>>>>>
'<<<<<< Controls Color & Caption of command button >>>>>>
'<<<< Color & Caption indicates worksheets condition >>>>


Dim wSheet As Worksheet
Application.ScreenUpdating = False
        For Each wSheet In Worksheets


                        '<<<<<<<<<<< Unprotects Worksheets
                If wSheet.ProtectContents = True Then
                        wSheet.Unprotect Password:="PassWord1"
                   
                        '<<<<<<<<<<< Protects Worksheets
                ElseIf wSheet.ProtectContents = False Then
                            wSheet.Protect Password:="PassWord1"
                End If
 
        Next wSheet
        
        
        
                If ActiveSheet.ProtectContents = True Then
                    
                        '<<<<<<<<<<< Controls Color and Caption of cmdGuardControl (Button)
                            ActiveSheet.Unprotect Password:="PassWord1"
                        cmdGuardControl.Caption = "Protected"
                                Me.cmdGuardControl.BackColor = RGB(50, 100, 50)
                                Me.cmdGuardControl.ForeColor = RGB(0, 225, 0)
                            ActiveSheet.Protect Password:="PassWord1"
        
        
        
                ElseIf ActiveSheet.ProtectContents = False Then
                        
                        '<<<<<<<<<<< Controls Color and Caption of cmdGuardControl (Button)
                        cmdGuardControl.Caption = "Unprotected"
                            Me.cmdGuardControl.BackColor = RGB(225, 25, 25)
                            Me.cmdGuardControl.ForeColor = RGB(50, 0, 0)
                End If
Application.ScreenUpdating = True
 


End Sub

Display More

The password can be changed to whatever personal choice you would like.
For general protection without a password, simply leave the password as, =""

For any cells that need to remain unprotected, simply uncheck the "locked" box in "format cells", "protection".

I have found that hiding a worksheet with this button then protecting the workbook keeps things pretty tight.
Also PW protect your code.

I hope this helps,
WQ

Macros to Protect/Unprotect Worksheets and Workbooks

Participate now!

Share