how can i restrict modification/change of certain ranges in a worksheet with VBA

Flora Edwards
Flora Edwards used Ask the Experts™
on
I need help with a code.  

basically,

the code should not allow user to modify any values into the followings ranges of Sheet("Data")

the only modification should be allowed by the Sub procedure that writes values into these cells that procedure name is Macro500

i googled to find the worksheet change event that restricts but i could not find something useful.

A33:B34
A38:B39
A15:B16
A21:B28
D15:K17
C21:K30
D32:K35
C38:K40
B6:C9
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016
Commented:
Hi,

this line protect the worksheet but letting vba modifying it
Workshe

Open in new window

et.Protect "Password", UserInterfaceOnly := True

Open in new window

eg.
Sheets("Sheet1").Protect "PW", UserInterfaceOnly := True

Open in new window

but first you have to lock the ones you don't want to be modified
Worksheets("Sheet1").Range("A33:B34,A38:B39,A15:B16,A21:B28,D15:K17,C21:K30,D32:K35,C38:K40,B6:C9").Locked = True

Open in new window

Regards
Flora EdwardsMedicine

Author

Commented:
thanks very much

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial