Excel Protect Function, while allowing macros

  • Thread starter Thread starter RK
  • Start date Start date
R

RK

How can I protect a range of cells, so that Macros will work in those cells,
but users cannot access them?
 
Lock the cells and protect the worksheet. Have the macro unprotect the sheet
and then reprotect it when done.

ActiveSheet.Unprotect Password:="mypassword"
'Do Stuff
ActiveSheet.Protect Password:="mypassword"

There are a lot of potential options for protection so I would recommend
that you use the marco recorder to record you sheet protection to get all of
the conditions.

NOTE: The recorder will not record the password, you need to add this
section manually

This is a sample of what you may get
ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
True, AllowFormattingCells:=True, AllowFormattingColumns:=True,
AllowFormattingRows:=True

Simply add the condition Password:="mypassword" like this

ActiveSheet.Protect Password:="mypassword",DrawingObjects:=False,
Contents:=True, Scenarios:= _
True, AllowFormattingCells:=True, AllowFormattingColumns:=True,
AllowFormattingRows:=True
 
Of course if you put the password in the code, anyone can read it if the open
the visual basic editor...

to protect against this you old protect the VBproject, from the editor
window select menu Tools>VBAProject Properties... select the Protection tab
and then check the lock option and enter a password, and remember to keep it
in a secure place!

Next time the file is opened your code will be protected.

--
If this is the answer you hoped for please remember to click the yes button
below...

Kind regards

Rik
 
Back
Top