how to record macro for passwords

  • Thread starter Thread starter Sherees
  • Start date Start date
S

Sherees

Hi,
I have more than fifty sheets, which i want to lock with a password? I have
tried to record macro but it does not work as intended. When I run the
recorded macro, it protects the sheet but without the password. Solution
Please!

Thanks
Sherees
 
Hi Jan

Thanks a lot
I have run the macro, n i have done the same macro for unprotecting also,
but i want to give a password for this macro, so when we run this macro, it
will ask for the password so other users cant unprotect the sheet.

Thank you again
 
It just takes a little modification to make it all work. I have added the
"masterPassword" constant so that you can control things better. With it,
people will have to always enter the correct password and so the password
won't get changed by accidental (or intentional) typos by users later on.

Naturally, if they look at the code, they'll see the password there, but
it's a step forward. The first line [Const masterPassword = "your!password"]
goes somewhere near the beginning of the code module you put these routines
into, not within either of the Sub routines.

Const masterPassword = "your!password"

Sub ProtectAllSheets()
Dim oSh As Worksheet
Dim thePassword As String

thePassword = _
InputBox("Enter the Password to protect all sheets.", _
"Password Required", "")
If thePassword = masterPassword Then
MsgBox "You did not provide a valid password, cannot continue.", _
vbOKOnly, "No Password Entered"
Exit Sub
End If
For Each oSh In Worksheets
oSh.Protect Password:=thePassword
Next
End Sub

Sub ProtectAllSheets()
Dim oSh As Worksheet
Dim thePassword As String

thePassword = _
InputBox("Enter the Password to unprotect all sheets.", _
"Password Required", "")
If thePassword = masterPassword Then
MsgBox "You did not provide a valid password, cannot continue.", _
vbOKOnly, "No Password Entered"
Exit Sub
End If
For Each oSh In Worksheets
oSh.Unprotect Password:=thePassword
Next
End Sub
 
OOPS!! One of the dangers of cut'n'paste got me again! The name of the
second routine above should be changed to Sub UnprotectAllSheets() otherwise
you get the dreaded "ambiguous name detected" error. mea culpa.
 
this might work...

Sub ProtectAllSheets()
Dim oSh As Worksheet
Dim sPass1 as string
Dim sPass2 as string

spass1 = inputbox("Please enter a password to protect.")
spass2 = inputbox("Please confirm the passord.")

if spass1 <> spass2 then
msgbox "passwords don't match"
else
For Each oSh In Worksheets
oSh.Protect spass1
Next
endif
End Sub
 
Maybe something like this pair of macros then?

Sub ProtectAllSheets()
Dim xPass As String
Dim oSh As Worksheet
xPass = InputBox("Password:", "Protect Sheets")
For Each oSh In Worksheets
oSh.Protect xPass
Next
End Sub

Sub UnprotectAllSheets()
Dim xPass As String
Dim oSh As Worksheet
xStart:
xPass = InputBox("Password:", "Unprotect Sheets")
On Error GoTo WrongPass
For Each oSh In Worksheets
oSh.Unprotect xPass
Next
Exit Sub
WrongPass:
MsgBox "Wrong password", vbCritical, "Incorect password"
On Error GoTo 0
GoTo xStart
End Sub
 
Back
Top