Show Buttons
Share On Facebook
Share On Twitter
Share On Google Plus
Share On Linkdin
Share On Reddit
Contact us
Hide Buttons

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

Objec­tive : Sup­pose you have many work­sheets in an excel and you want to change all the pass­words in one shot rather doing it man­u­ally for every sheet, which might be very bad way to do spe­cially when you have a power of doing it using VBA.

NOTE: you need a place to store your pass­word, most of the timewe store it in our mind but com­puter doesn’t have mind, it has mem­ory :), but the point is where, it could be a file, DB . Why not use your excel for that.

Store it in a sep­a­rate sheet and make it very hid­den in your code.

Fol­low the below steps:

  • Open a new workbook.
  • cre­ate a but­ton and name it as “Change Password”
Create a button

Cre­ate a button

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

code

  • Assign the macro to the but­ton “Change Passsword”
Assign Macro

Assign Macro

  • pro­tect all the Work­Book using the same pass­word, say “one”
Protect Sheets

Pro­tect Sheets

  • cre­ate a new work­sheet , don’t pro­tect 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 Pass­word” but­ton to change all the work­sheets pass­word in one shot.
  • At the end your IMP sheet will be very hid­den, and you can see it only through your code.
Change Password

Change Pass­word

 

Com­plete 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

 

You may also like...

Leave a Reply

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

%d bloggers like this: