EnableSelection Protecting sheet question

[Damian]

I have a sheet which I need to protect:
I have this code (VBA)
With ActiveSheet
.Protect Password:="xxx"
.EnableSelection = xlUnlockedCells
End With

AND it works great. BUT I need to add to this so they cab use FormatCells,
FormatColumns and FormatRows.
When I wrote this:
With ActiveSheet
.Protect Password:="xxx"
.EnableSelection = xlUnlockedCells
.EnableSelection = xlFormatCells
.EnableSelection = xlFormatColumns
.EnableSelection = xlFormatRows
End With

IT DOES NOT WORK. Am I doing the code wrong?

 

[Gord Dibben]

Yes, you are using the code wrong.

With ActiveSheet
.Protect Password:="xxx"
.EnableSelection = xlUnlockedCells
.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True
End With

Recording a macro whilst protecting the sheet would have shown you the code.


Gord Dibben MS Excel MVP

 

[Damian]

Gord,
I cant seem to change the letter"p" in .Protect to a capital "P".
It switches back to a lower case every time i change it. I tried everything.
and the code does not work it its in lower case.

Any idea why its going Loco?

Thanks.

 

[Gord Dibben]

What happens when you copy/paste the code into the module?

On my end I can't get the upper case "P" to change to a lower case unless I
rem with an apostrophe.

Can't say I have seen this before.

Perhaps someone can explain for us.

One thing I did notice was that the code I posted did not set the password.

This revision does.

With ActiveSheet
.EnableSelection = xlUnlockedCells
.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True
.Protect Password:="xxx"
End With


Gord