Macro functions in a protected sheet

[Keyrookie]

How can you have your macros run in a protected sheet? Also, I need to
be able to insert comments. Any ideas anyone?

K

 

[Per Jessen]

Hi

Your macro can unprotect the sheet, do what you need and protect the
sheet again.

Sub Test()
Sheeets("Sheet1
With Sheets("Sheet1")
.Unprotect Password:="JustMe"
.Range("A1").AddComment ("Merry Christmas")
.Range("B1")="Hello"
.Protect Password:="JustMe"
End With
End Sub

Hopes this helps.
.....
Per

 

[Gord Dibben]

Depends upon what the macro is doing.

Most times you add lines to unprotect...do your stuff.....re-protect.

To insert Comments, when you protect the sheet make sure you allow "edit
objects"


Gord Dibben MS Excel MVP

 

[Keyrookie]

Thanks for the replies. Is there anyway to "allow edit" ONLY a range of
cells? There are other cells I would like to disallow edit. Also, the
macro is performing a "paste special" option to replace a formula. The
cell that contains the formula I would like to protect to keep from
accidental overwrites.

I hope that clarifies. K

 

[Gord Dibben]

By default all cells on a sheet are locked when you protect the sheet.

Best is to select all cells by CTRL + a and unlock all.

Then select those cells which you want locked and set them to locked.

Now protect the sheet. Users can edit unlocked cells.

See options for allowables when go to Tools>Protect Sheet

If you want to edit a locked cell via macro, you must add code to unprotect,
do the stuff, then re-protect.

Sub pasteit()

Activesheet.unprotect password:="mypassword"

code to do things

Activesheet.protect password:="mypassword"

End Sub


Gord