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”
- Press “Alt+F11” and copy paste the given code.
- Assign the macro to the button “Change Passsword”
- protect all the WorkBook using the same password, say “one”
- create a new worksheet , don’t protect it and name it as “IMP”
- In “IMP” Sheet , at “A1” cell put “one” (password)
- 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.
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