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
--
Life isa journey not a destination

Hi Sherees,

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!

You can use code like this:

Sub ProtectAllSheets()
Dim oSh As Worksheet
For Each oSh In Worksheets
oSh.Protect "Password"
Next
End Sub

Of course you need to change the password...

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com

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
--
Life isa journey not a destination


"Jan Karel Pieterse" wrote:

Hi Sherees,

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!

You can use code like this:

Sub ProtectAllSheets()
Dim oSh As Worksheet
For Each oSh In Worksheets
oSh.Protect "Password"
Next
End Sub

Of course you need to change the password...

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com

.

Hi Sherees,

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.

Su

Sub ProtectAllSheets()
Dim sPass As String
Dim oSh As Worksheet
sPass = InputBox("Please enter password")
'Stop here if no password was entered or user pressed cancel
If Len(sPass) = 0 Then Exit Sub
'Confirm password
If sPass = InputBox("Please re-enter password") Then
For Each oSh In Worksheets
oSh.Protect sPass
Next
Else
MsgBox "Passwords do not match, operation cancelled", _
vbExclamation + vbOKOnly
End If
End Sub

Sub UnProtectAllSheets()
Dim oSh As Worksheet
Dim sPass As String
sPass = InputBox("Please enter password")
'Stop here if no password was entered or user pressed cancel
If Len(sPass) = 0 Then Exit Sub
On Error Resume Next
For Each oSh In Worksheets
oSh.Unprotect sPass
Next
If Err.Number 0 Then
MsgBox "Unable to unprotect sheets, possibly wrong password", _
vbExclamation + vbOKOnly
End If
End Sub


Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com

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

"Sherees" wrote:

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
--
Life isa journey not a destination


"Jan Karel Pieterse" wrote:

Hi Sherees,

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!

You can use code like this:

Sub ProtectAllSheets()
Dim oSh As Worksheet
For Each oSh In Worksheets
oSh.Protect "Password"
Next
End Sub

Of course you need to change the password...

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com

.

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.


"Sherees" wrote:

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
--
Life isa journey not a destination


"Jan Karel Pieterse" wrote:

Hi Sherees,

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!

You can use code like this:

Sub ProtectAllSheets()
Dim oSh As Worksheet
For Each oSh In Worksheets
oSh.Protect "Password"
Next
End Sub

Of course you need to change the password...

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com

.

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

--
HTH...

Jim Thomlinson


"Sherees" wrote:

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
--
Life isa journey not a destination


"Jan Karel Pieterse" wrote:

Hi Sherees,

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!

You can use code like this:

Sub ProtectAllSheets()
Dim oSh As Worksheet
For Each oSh In Worksheets
oSh.Protect "Password"
Next
End Sub

Of course you need to change the password...

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com

.

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
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Sherees" wrote:

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
--
Life isa journey not a destination


"Jan Karel Pieterse" wrote:

Hi Sherees,

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!

You can use code like this:

Sub ProtectAllSheets()
Dim oSh As Worksheet
For Each oSh In Worksheets
oSh.Protect "Password"
Next
End Sub

Of course you need to change the password...

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com

.