Hello;

I would appreciate help with this one:

I have a worksheet which is protected with a password. Some (select) users
have access to the password and can modify it and are instructed that after
modifying it, they should again protect it using the same password so that
it cannot be changed by other users.

Sometimes users forget to re-protect the sheet. What code would be required
to alert the user that the worksheet is unprotected & that he/she should
password protect it. This code should get activated when the user exits from
the worksheet & goes to another without protecting it.


TIA
Maria

Use the workbook_beforeClose event

if worksheets("Sheet1").ProtectContents = False then
msgbox "Protect sheet"
cancel = true
End if

--
Regards,
Tom Ogilvy

Maria wrote in message
...
Hello;

I would appreciate help with this one:

I have a worksheet which is protected with a password. Some (select) users
have access to the password and can modify it and are instructed that
after
modifying it, they should again protect it using the same password so that
it cannot be changed by other users.

Sometimes users forget to re-protect the sheet. What code would be
required
to alert the user that the worksheet is unprotected & that he/she should
password protect it. This code should get activated when the user exits
from
the worksheet & goes to another without protecting it.


TIA
Maria

Thanks a lot Tom for your prompt reply. It worked perfectly.

I also tried putting the code in the Worksheet Deactivate part & it worked
as soon as the user went away from Sheet 1. Do you see any problems using it
this way?

Thanks
M

--
Maria "Tom Ogilvy" wrote in message
...
Use the workbook_beforeClose event

if worksheets("Sheet1").ProtectContents = False then
msgbox "Protect sheet"
cancel = true
End if

--
Regards,
Tom Ogilvy

Maria wrote in message
...
Hello;

I would appreciate help with this one:

I have a worksheet which is protected with a password. Some (select)
users
have access to the password and can modify it and are instructed that
after
modifying it, they should again protect it using the same password so
that
it cannot be changed by other users.

Sometimes users forget to re-protect the sheet. What code would be
required
to alert the user that the worksheet is unprotected & that he/she should
password protect it. This code should get activated when the user exits
from
the worksheet & goes to another without protecting it.


TIA
Maria

Maria, you could also put some code to protect the sheet when the user
clicked on another sheet like this

Private Sub Worksheet_Deactivate()
Sheets("Sheet1").Protect password:="123"
End Sub


--
Paul B
Always backup your data before trying something new
Using Excel 2000 & 97
Please post any response to the newsgroups so others can benefit from it
** remove news from my email address to reply by email **
"Maria" wrote in message
...
Thanks a lot Tom for your prompt reply. It worked perfectly.

I also tried putting the code in the Worksheet Deactivate part & it worked
as soon as the user went away from Sheet 1. Do you see any problems using
it
this way?

Thanks
M

--
Maria "Tom Ogilvy" wrote in message
...
Use the workbook_beforeClose event

if worksheets("Sheet1").ProtectContents = False then
msgbox "Protect sheet"
cancel = true
End if

--
Regards,
Tom Ogilvy

Maria wrote in message
...
Hello;

I would appreciate help with this one:

I have a worksheet which is protected with a password. Some (select)
users
have access to the password and can modify it and are instructed that
after
modifying it, they should again protect it using the same password so
that
it cannot be changed by other users.

Sometimes users forget to re-protect the sheet. What code would be
required
to alert the user that the worksheet is unprotected & that he/she
should
password protect it. This code should get activated when the user
exits
from
the worksheet & goes to another without protecting it.


TIA
Maria

No particular problem. You have to cover the possibility the user could
close the workbook with that sheet active - thus never triggering the
deactivate event.

--
Regards,
Tom Ogilvy

Maria wrote in message
...
Thanks a lot Tom for your prompt reply. It worked perfectly.

I also tried putting the code in the Worksheet Deactivate part & it worked
as soon as the user went away from Sheet 1. Do you see any problems using
it
this way?

Thanks
M

--
Maria "Tom Ogilvy" wrote in message
...
Use the workbook_beforeClose event

if worksheets("Sheet1").ProtectContents = False then
msgbox "Protect sheet"
cancel = true
End if

--
Regards,
Tom Ogilvy

Maria wrote in message
...
Hello;

I would appreciate help with this one:

I have a worksheet which is protected with a password. Some (select)
users
have access to the password and can modify it and are instructed that
after
modifying it, they should again protect it using the same password so
that
it cannot be changed by other users.

Sometimes users forget to re-protect the sheet. What code would be
required
to alert the user that the worksheet is unprotected & that he/she
should
password protect it. This code should get activated when the user
exits
from
the worksheet & goes to another without protecting it.


TIA
Maria