VBA code to protect worksheet, and allow changes to cell comments

[Gerald Higgins]

Hi all, thanks in advance for any help.

I've got some code which protects the worksheet using a password.

I want to allow users to be able to input and edit comments.
It doesn't really matter whether this is restricted to unlocked cells, or
whether they can do it over the entire worksheet.

I can do this manually, and my code works for everything EXCEPT allowing comments to be edited / inserted.

Any ideas what I should be doing differently ?

Here's what I have now...

ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:=True
    ActiveSheet.EnableSelection = xlUnlockedCells
    ActiveSheet.EnableSelection = xlLockedCells
    ActiveSheet.Protect "password"

This does everything I want it to do, EXCEPT allow comments to be edited.
I've tried shuffling things around, but it either does just the same, or prompts for a password unexpectedly.
From the macro recorder, I've worked out that DrawingObjects has to be set to False for comments, but it doesn't seem to work when I put it all together.

Using 2003....

 

[GTO]

Hi Gerald,

No well tested, but maybe:
Sheet1.Protect Password:="MyPassword", DrawingObjects:=False, Contents:=True, Scenarios:=True

 

[Gerald Higgins]

Thanks for the input GTO but that wasn't it.
Specifically, I tried

    ActiveSheet.Protect Password:="password", DrawingObjects:=False, Contents:=True, Scenarios:=True
    ActiveSheet.EnableSelection = xlUnlockedCells
    ActiveSheet.EnableSelection = xlLockedCells
    ActiveSheet.Protect "password"

and

    ActiveSheet.Protect "password", DrawingObjects:=False, Contents:=True, Scenarios:=True
    ActiveSheet.EnableSelection = xlUnlockedCells
    ActiveSheet.EnableSelection = xlLockedCells
    ActiveSheet.Protect "password"

and they both seem to do the same - turn protection on, but not allow changes to comments.

Any more ideas ?

 

[GTO]

Okay, I do not think you need the second .Protect.

Here appears to be the problem though: xlLockedCells does not exist as a constant in 2003.

I would suggest that if you do not use Option Explicit, you may wish to consider it, as these type issues are avoided.

I used:

    ActiveSheet.Protect Password:="password", DrawingObjects:=False, Contents:=True, Scenarios:=True
    ActiveSheet.EnableSelection = xlNoRestrictions
End Sub

and all appears well.

Does that help?

Mark

 

[Gerald Higgins]

...and all appears well.

It most certainly does !!!

Thanks very much GTO, that did the trick.

 

[GTO]

Glad to help