How do I allow a macro to run on a protecetd sheet without the user having to enter the password?

[Dan E]

I have a macro (see below) that begins by unprotecting the active worksheet,
runs several subs, then protects the sheet again. The user runs the macro
by clicking on a graphic in a locked cell. The sheet is normally password
protected. I'm pretty sure that something odd has happened - I'm sure the
user could run the macro without having to give the password to unprotect,
and now the macro won't run without they do that. Also, when the macro
finishes, the sheet is protected, but doesn't seem to have password
protection - Tools|Protection|Unprotect just unprotects it without asking
for the password. Maybe I'm going nuts... Anyway - is there a way of
building the passowrd protection into the macro, and also, is what happens
in the description above the norm? I was SURE the macro would run on a
password protected sheet without the user being asked for the password.

Macro:-
____________
Sub Main_MEMCARE()
ActiveSheet.Unprotect
Trim_Text
Color_Text
myRows
CC_OT
ALL_OT
ActiveSheet.Protect
End Sub
______________

All help and suggestions gratefully received,

TIA

Dan

 

[Gord Dibben]

Dan

I would wrap the code in something similar to this

ActiveSheet.Unprotect Password:="justme"

'your macros go here

ActiveSheet.Protect Password:="justme", DrawingObjects:=True, _
Contents:=True, Scenarios:=True

Note: for users not to have access to the password, you must protect the VBA
Project, which could be cracked by a determined user.

Not sure how the code ran before on a protected sheet unless you are running
Excel 2002 or 2003 and had a bunch of options checked in the
Tools>Protection>Protect sheet options.


Gord Dibben Excel MVP

 

[Dan E]

Many thanks, Gord - that will work. Yes I am running Excel 2003, but don't
have anything fancy under protection. Maybe I just had a senior moment.

Thanks again,

Dan