ClearFormats locks range? 1

[notuserfriendly]

Hi, I'm wondering why my sheet behaves strangely. I'm trying to protect some parts of the sheet.
There is one problem though. After running this code the range is locked. It happens after .ClearFormats is run.
I've been trying to change it with .Locked = False for the same range but it's no use. Any ideas?

With Range(ws.Cells(startRow, col1), ws.Cells(endRow, col2))                        
 .ClearFormats
 .ClearComments
 .ClearContents
End With

 

[GlennUK]

Are you trying to change it after the ClearFormats?

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[xlbo]

so what do you think the locked property is part of ??

the clue is in the menu path

FORMAT>Cells>Protection

If you clear the formats, you clear the proection settings you have just set - and by default, cells are locked and therefore protected when the sheet is

The order you need to run with is:

1: Clear formats
2: do your formatting including locking / unlocking cells
3: protect the sheet

If you try and protect the sheet too early, you will not be able to change the format properties of the cells...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question