MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Macro protection override?


Posted by RoB on August 14, 2001 2:17 PM

Hi again :),
Is there a way to protect a sheet from a user changing the data, but allowing the data to be changed by a macro? I have a sort macro that runs and returns values to a sheet, and i want the user to be able to view it, but not change it. I ONLY want the macro to be able to change it. (the macro can run multiple times, it clears cells before it pastes)

Thanks


Posted by Robb on August 14, 2001 5:37 PM

Rob

I have had to do the same thing many times and the only way I have found is to Unprotect and then protect the sheet within the macro e.g.

Worksheets(???).Unprotect password:="whatever"

{Your code to change values}

Worksheets(???).Protect password:="whatever"

You do need to lock the project with a password (protect the module in 95) to prevent the user having access to the password.
I'd be very interested to learn if there is another way to do this>

Regards

Posted by Ivan F Moala on August 14, 2001 7:17 PM

Run a macro to protect the sheet BUT set
Userinterfaceonly to TRUE

eg
ActiveSheet.Protect UserInterfaceOnly:=True

from then on ONLY macros will be able to change
the sheets.

NB: You must protect the sheet again in this manner
when openning it up again.


Ivan

: Hi again :), : Is there a way to protect a sheet from a user changing the data, but allowing the data to be changed by a macro? I have a sort macro that runs and returns values to a sheet, and i want the user to be able to view it, but not change it. I ONLY want the macro to be able to change it. (the macro can run multiple times, it clears cells before it pastes)