Be the first user to complete this post

  • 0
Add to List

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

Also Read:

  1. VBA-Excel: Find a word in a specific paragraph and change its formatting
  2. How to Enable Macro in Excel
  3. VBA Excel - Cells, Ranges and Offset: Refer Range by using A1 Notations
  4. VBA-Excel: Read XML by Looping through Nodes
  5. VBA-Excel: Get ALL The Opened Internet Explorer (IE) using Microsoft Excel