Excel-VBA : Change Passwords for all the WorkSheets in one shot

Objective : Suppose you have many worksheets in an excel and you want to change all the passwords in one shot rather doing it manually for every sheet, which might be very bad way to do specially when you have a power of doing it using VBA.

NOTE: you need a place to store your password, most of the timewe store it in our mind but computer doesn’t have mind, it has memory :), but the point is where, it could be a file, DB . Why not use your excel for that.

Store it in a separate sheet and make it very hidden in your code.

Follow the below steps:

  • Open a new workbook.
  • create a button and name it as “Change Password”
Create a button

Create a button

  • Press “Alt+F11” and copy paste the given code.


  • Assign the macro to the button “Change Passsword”
Assign Macro

Assign Macro

  • protect all the WorkBook using the same password, say “one”
Protect Sheets

Protect Sheets

  • create a new worksheet , don’t protect it and name it as “IMP”
  • In “IMP” Sheet , at “A1” cell put “one” (password)
IMP sheet

IMP sheet

  • That’s it Click the “Change Password” button to change all the worksheets password in one shot.
  • At the end your IMP sheet will be very hidden, and you can see it only through your code.
Change Password

Change Password


Complete Code:

Dim mainworkBook As Workbook
Sub FnChangePasswords()
    Set mainworkBook = ActiveWorkbook
    Sheets("IMP").Visible = xlVeryHidden
    currPwd = Sheets("IMP").Range("A1")
    intInput = InputBox("Enter the Current Password")
    If (StrComp(currPwd, intInput, vbTextCompare) = 0) Then
        newPwd = InputBox("Enter the New Password")
        For i = 1 To mainworkBook.Sheets.Count
            sheetName = mainworkBook.Sheets(i).Name
            If (sheetName <> "IMP") Then
                Sheets(sheetName).Unprotect currPwd
                'Do your work here
                Sheets(sheetName).Unprotect newPwd
                Sheets(sheetName).Protect newPwd
            End If
        Next i
        Sheets("IMP").Range("A1").Value = newPwd
        Sheets("IMP").Visible = xlVeryHidden
        MsgBox "All the Worksheets Passwords are changed"
        MsgBox "Incorrect Password"
    End If
End Sub


You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *

%d bloggers like this: