How to add a password to a macro?
Category: [Excel] Demo Available
How to add a password to a macro?
Requirement:
The user has two separate macros that work correctly with buttons in the quick access toolbar to execute each independently.
They are 'Protect All Worksheets' & 'Unprotect All Worksheets.'
The user wants to add a password just to the code 'Unprotect All Worksheets' so that when its button is pressed a password must be entered before executing.
Also, does anyone have a code to automatically 'Protect All Worksheets' when closing a workbook? (No password is needed here.)
Sub Unprotect_All()
Dim ws As Worksheet
On Error Resume Next
Application.ScreenUpdating = False
For Each ws In ActiveWorkbook.Worksheets
ws.Unprotect Password:="wts"
Next ws
Application.ScreenUpdating = True
On Error GoTo 0
End Sub
Solution:
Code:
Sub Unprotect_All()
Application.ScreenUpdating = False
Dim ws As Worksheet, pWord As String, response As String
pWord = "MyPassword"
response = InputBox("Please enter the password.")
If response = "" Then
Exit Sub
ElseIf response = pWord Then
For Each ws In Sheets
ws.Unprotect Password:=response
Next ws
Else
MsgBox ("Invalid password. Please try again.")
End If
Application.ScreenUpdating = True
End Sub
Obtained from the OzGrid Help Forum.
Solution provided by Mumps.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to use a macro to auto-scroll |
| How to loop a macro with various length columns |
| How to use Excel VBA macro to import data from multiple workbooks to main workbook |
| How to crate a macro for text copy and paste in different worksheets based on a variable in Excel |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.