Select all the unlocked cells on a worksheet

  • Thread starter Thread starter Steven Revell
  • Start date Start date
S

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
 
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
 
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
 
Back
Top