Be the first user to complete this post

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

Press "Alt+F11" and copy paste the given code.

code

Assign the macro to the button "Change Passsword"

Assign Macro

protect all the WorkBook using the same password, say "one"

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

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

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"
    Else
        MsgBox "Incorrect Password"
    End If
End Sub

excel workbook 47 password protected macro 3 automate 45