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

K

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


On 8 Dec., 02:09, Keyrookie wrote:
How can you have your macros run in a protected sheet? *Also, I need to
be able to insert comments. *Any ideas anyone?

K

--
Keyrookie

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

On Tue, 8 Dec 2009 01:09:10 +0000, Keyrookie
wrote:


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

K

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

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 ToolsProtect 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

On Wed, 9 Dec 2009 04:10:20 +0000, Keyrookie
wrote:


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;907914 Wrote:
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

On Tue, 8 Dec 2009 01:09:10 +0000, Keyrookie
wrote:
-

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

K-