Goto Locked Cells

[Jaafar Tribak]

Hi and happy new year everyone.

Is there a programmatic way to select all locked cells in the active worksheet more efficiently than finding out by looping through each and every cell in the used range ? .. Something similar to the Goto Special command or Range.SpecialCells(....

Regards.

 

[Jaafar Tribak]

I just remembered the CellFormat Class that can be attained via the FindFormat Prroperty and then do the search. This method should be faster I guess although it too requires a bit of looping.

 

[Rick Rothstein]

What is it you want to do with the locked cells once you select them? I ask because you may be able to use Cells.Replace after setting this...

Application.FindFormat.Locked = True

after which you can set a common value into the locked cells (for example, "") and/or change their various format properties via...

Application.ReplaceFormat

 

[Jaafar Tribak]

What is it you want to do with the locked cells once you select them?

Nothing really. I was just revisiting the GoToSpecial command and found nothing for locked cells which I found rather intriguing.
I have ever hardly used the FindFormat\Find\FindNext\Replace commands in the past as I find them rather messy and clumsy... I guess I will need a refresher.
Thank you Rick for answering.

 

[Rick Rothstein]

Your last response suggests you are only thinking of loops with the Replace function... I was thinking of non-looping code using Replace. Here are a few examples of what I was suggesting (procedure names describe functionality)...

Sub ColorAllLockedCells()
  With Application
    .FindFormat.Clear
    .ReplaceFormat.Clear
    
    .FindFormat.Locked = True
    .ReplaceFormat.Interior.Color = vbYellow
    
    Cells.Replace "", "", , , , , True, True
    
    .FindFormat.Clear
    .ReplaceFormat.Clear
  End With
End Sub

Sub ColorLockedCellsWithDataInThem()
  With Application
    .FindFormat.Clear
    .ReplaceFormat.Clear
    
    .FindFormat.Locked = True
    .ReplaceFormat.Interior.Color = vbYellow
    
    Cells.Replace "*", "", , , , , True, True
    
    .FindFormat.Clear
    .ReplaceFormat.Clear
  End With
End Sub

Sub PutTextIntoLockedCells()
  With Application
    .FindFormat.Clear
    .ReplaceFormat.Clear
    
    .FindFormat.Locked = True
    .ReplaceFormat.Interior.Color = vbYellow
    
    Cells.Replace "", "XXX", , , , , True, True
    
    .FindFormat.Clear
    .ReplaceFormat.Clear
  End With
End Sub

Sub ClearColorLockedCells()
  With Application
    .FindFormat.Clear
    .ReplaceFormat.Clear
    
    .FindFormat.Locked = True
    .ReplaceFormat.Interior.Color = vbYellow
    
    Cells.Replace "*", "#N/A", , , , , True, True
    Cells.SpecialCells(xlConstants, xlErrors).ClearContents
    
    .FindFormat.Clear
    .ReplaceFormat.Clear
  End With
End Sub

 

[Jaafar Tribak]

@Rick Rothstein​

That is awesome !

I regret having always overlooked these handy FindFormat, ReplaceFormat and Replace commands although they seem to be rather slow when searching large ranges.

I wonder how we could come up with a clean function that returns the actual range of locked cells using the same approach but without altering the cells.

Thanks.

 

[Rick Rothstein]

For the speed, you might try disabling ScreenUpdating.

The only thing I can think of off the top of my head for your other question is to duplicate the sheet (delete it afterwards), use the Replace function on that duplicated sheet to put something SpecialCells can recognize into the Locked cells, perhaps #N/A. Then use Cells.SpecialCells(xlConstants,xlErrors) to identify the range (store it in a range variable), then reference that range variable to the original sheet... or something along those lines.

Edit Note
-----------------------
Another thought... if your Locked cells are confined to the used range, then instead of duplicating the sheet, you could store the data in an array, do the other stuff I suggested to the original sheet and then restore the data from the array at the end.

 

[Jaafar Tribak]

Yeah. Those were the possible workarounds that I too had in mind.
Interesting that excel and vba offer ways to retrieve special cells but excluded locked cells.
Thanks for the follow up.