OzGrid

Excel: Password Protect/Unprotect All Excel Worksheets in One Go

< Back to Search results

 Category: [Excel]  Demo Available 

Excel: Password Protect/Unprotect All Excel Worksheets in One Go

 

Got any Excel/VBA Questions? Excel Help 

Excel has protection that we can add to an Excel Worksheet via Tools>Protection>Protect Sheet. A password can also be supplied so that another user cannot unprotect it without the password.

A common question that I'm often asked is, "how can I password protect and unprotect all Worksheets in my Workbook in one go. I have many sheets and I have to protect and unprotect each Worksheet individually." Unfortunately there is no standard feature in Excel that will allow us to protect and unprotect all Worksheets in one go, however, we can use some fairly simple VBA code to do this for us. Follow the steps below:

  1. Open The Workbook The Code Is Needed For. Or, Go To Window>Unhide And Unhide Your Personal.Xls To Make It Available To Any Workbook.
  2. Go To Tools>Macro>Visual Basic Editor (Alt+F11) And Then Insert>UserForm. This Should Also Display The Control Toolbox. If It Doesn't Go To View>Toobox.
  3. From The Toolbox Select A TextBox (Ab|). Now Click Onto The UserForm To Add It. Position It In The Top Left And Size It So It Is Your Preferred Size.
  4. Ensure The Textbox Is Still Selected And Then Go To View>Properties (F4). From The Properties Window Of The Textbox Scroll Down To PasswordChar And In The White Box On The Right Enter An Asterisk *
  5. Now From The Toolbox Select A CommandButton And Then Click The UserForm And Position It In The Top Right.
  6. With The CommandButton Still Selected Go To View>Properties (F4). From The Properties Window Of The CommandButtonScroll Down To Caption And In The White Box On The Right Enter The Caption: OK. If You Are Using Excel 97 Also Scroll Down ToTakeFocusOnClick And Set This To False.
  7. Now Select The UserForm And From It's Properties Window Find Caption And Change It To: Protect/Unprotect All Sheets.

  1. Now Go To View>Code (F7) And Place In The Code Exactly As Shown Below
    Private Sub CommandButton1_Click()
    
    Dim WSheet As Worksheet
    
    	For Each WSheet In Worksheets
    
    		If WSheet.ProtectContents = True Then
    
    			WSheet.Unprotect Password:=TextBox1.Text
    
    		Else
    
    			WSheet.Protect Password:=TextBox1.Text
    
    		End If
    
    	Next WSheet
    
    Unload Me
    
    End Sub
  2. Now Go To Insert>Module And In Here Add The Code Below
    Sub ShowPass()
    
        UserForm1.Show
    
    End Sub
  3. Now Click The Top Right X, Or Push Alt+Q To Get Back To Excel.
  4. Go To Tools>Macro>Macros (Alt+F8) Select ShowPass And Then Click Options And Assign A Shortcut Key.

This newly added feature will unprotect all Worksheets that are protected and protect all Worksheets that are unprotected.

A WORD OF WARNING: As you are not asked to confirm your password you should be very sure of what you type.

 

See also:

Excel VBA Macro: Determine Which Button, Control or Command Button Was Clicked
Excel VBA: Macro Code To Run Macros On Protected Worksheets & Sheets
Excel VBA: Stop Screen Flickering in Recorded Macros and Speed up Your Code
Excel Ranges: Finding the Last Cell in a Range
Excel: Get File Name From User to Open Workbook Or Save Workbook
Excel VBA Runtime Errors & Preventing Them
Sheet/Worksheet CodeNames
Automatically Run Excel Macros via Workbook & Worksheet Events

 

See also: Index to Excel VBA Code; Index to Excel Freebies; Lesson 1 - Excel Fundamentals; Index to how to… providing a range of solutions

 

Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions. Online slots all their lifetime was popular online casino game https://casinomasta.com/casino-games/ . Several years ago slot machines gone completely new level – all gamblers can play slot machines with their own storyline, excellent music and nice graphic.

 

 

 


Gallery



stars (0 Reviews)