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. Excel-VBA : Open a MS Word Document using Excel File using Explorer Window.
  2. VBA-Excel: Modified Consolidator – Merge or Combine Multiple Excel Files Into One Where Columns Are Not In Order
  3. VBA-Excel: Get the Instance of already opened word document
  4. VBA-Excel: SUDOKU Solver
  5. Excel-VBA : Send Mail with Embedded Image in message body From MS Outlook using Excel.
  6. Excel-VBA : Insert Multiple Images from a Folder to Excel Cells
  7. VBA Codes - Record Macro
  8. Excel-VBA : Send a Excel Workbook as Attachment in Mail From MS Outlook Using Excel