Select all the unlocked cells on a worksheet

[Steven Revell]

Hi,

does anyone know how to select all the unlocked cells on a
worksheet.

All i need to do is show which cells are unlocked without
changing the formatting of the cells.

Thanks for any help,

Steven

 

[KM]

You can use the following code

Dim rgeStart As Range
Dim rgeEnd As Range
Dim Z As Integer, Y As Integer
Dim str1 As String, str2 As String

Set rgeStart = Application.Range("A1")
Set rgeEnd = Application.Cells.SpecialCells
(xlCellTypeLastCell)

For Z = 1 To rgeEnd.Row
For Y = 1 To rgeEnd.Column
If Not Application.Cells(Z, Y).Locked Then
str1 = Mid(Application.Cells(Z, Y).Address, 2, 1)
& Mid(Application.Cells(Z, Y).Address, 4, 1) & ","
str2 = str2 & str1
End If
Next Y
Next Z
str2 = Left$(str2, Len(str2) - 1)
Application.Range(str2).Select

 

[Tom Ogilvy]

The obvious is to loop through all the cells in the sheet and check the
locked property of the cell, building a union.

If the sheet is protected, you can use sendkeys to travel through them:

Sub GetUnlocked()
Dim rng As Range
Dim cell As Range
SendKeys "{tab}", True
Set rng = ActiveCell
Debug.Print rng.Address
SendKeys "{tab}", True
Set cell = ActiveCell
Debug.Print cell.Address
Do While Intersect(cell, rng) Is Nothing
Set rng = Union(rng, cell)
SendKeys "{tab}", True
Set cell = ActiveCell
Debug.Print rng.Address, cell.Address
Loop
rng.Select

End Sub