Lock cells

kwaring · Mar 22nd, 2002, 07:20 PM

Is there any way to lock cells so that a user cannot select or change them but let a macro change them?

Tom Schreiner · Mar 22nd, 2002, 07:25 PM

Do a search for Protect or Unprotect and you will get all of the syntax you need...

You will obviously need to unprotect at the beginning of your macro and then Protect at the end.

Dave Hawley · Mar 23rd, 2002, 01:19 AM

Hi

Yes there is! The Protect Method in VBA has an extra argument called "UserInterfaceOnly", by default this is False. If you howeve set it to True, any VBA code can change Object Properties, use Methods etc. I do however URGE you to read the help in the Excel VBE on "Protect Method" as the "UserInterfaceOnly" as it is wiped out after a save.

On a persoanl note I have found (in Excel 97 at least) that some operations, such as FillAcrossSheets does leave the Worksheet unprotected. For this reason I have avoided this and always used the:

Sub AnyOldCode()
Sheet2.UnProtect Password:="Secret"
On Error GoTo ReProtect
'Any Code
Reprotect:
Sheet2.Protect Password:="Secret
End Sub

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 22nd, 2002, 07:20 PM	#1
kwaring New Member Join Date: Mar 2002 Location: Nanaimo, BC Posts: 49	Is there any way to lock cells so that a user cannot select or change them but let a macro change them?

Mar 22nd, 2002, 07:25 PM	#2
Tom Schreiner Board Regular Join Date: Mar 2002 Location: Cincinnati, Ohio, USA Posts: 6,824	Do a search for Protect or Unprotect and you will get all of the syntax you need... You will obviously need to unprotect at the beginning of your macro and then Protect at the end.

Mar 23rd, 2002, 01:19 AM	#3
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	Hi Yes there is! The Protect Method in VBA has an extra argument called "UserInterfaceOnly", by default this is False. If you howeve set it to True, any VBA code can change Object Properties, use Methods etc. I do however URGE you to read the help in the Excel VBE on "Protect Method" as the "UserInterfaceOnly" as it is wiped out after a save. On a persoanl note I have found (in Excel 97 at least) that some operations, such as FillAcrossSheets does leave the Worksheet unprotected. For this reason I have avoided this and always used the: Sub AnyOldCode() Sheet2.UnProtect Password:="Secret" On Error GoTo ReProtect 'Any Code Reprotect: Sheet2.Protect Password:="Secret End Sub