Searching for locked/unlocked cells

  • Thread starter Karthik Bhat - Bangalore
  • Start date
K

Karthik Bhat - Bangalore

Hi All

I have a spreadsheet which has many cells locked, and they are spread
all across my worksheet.

I want a code which will help me select locked/unlocked cells (as
required) in the selected range in one go. Something like 'Visible
cells only' and 'Blanks' options available in the 'Go to Special'
screen.

A macro that asks me "Go to:"
Locked Cells
Unlocked cells

Thanks a lot
Karthik Bhat
 
B

Bernie Deitrick

Karthik,

Paste the sub below into your personal.xls and assign it to a custom toolbar button.

HTH,
Bernie
MS Excel MVP

Sub SelectedLockedUnlockedCells()
Dim myCell As Range
Dim myRange As Range
Dim SelLocked As Boolean
Dim myReply As Variant
myReply = MsgBox("Select Locked = ""Yes""" & Chr(10) & _
"Select UnLocked = ""No""", vbYesNoCancel)
If myReply = vbCancel Then Exit Sub

For Each myCell In Selection
If myReply = vbYes And myCell.Locked Then
If myRange Is Nothing Then
Set myRange = myCell
Else
Set myRange = Union(myRange, myCell)
End If
End If
If myReply = vbNo And Not myCell.Locked Then
If myRange Is Nothing Then
Set myRange = myCell
Else
Set myRange = Union(myRange, myCell)
End If
End If
Next myCell
If myRange Is Nothing Then
MsgBox "No " & IIf(myReply = vbYes, "Locked", "Unlocked") & _
" cells found in the current selection."
Exit Sub
End If
myRange.Select
End Sub
 
N

NickHK

Karthik,
Depending on the WS Protection and .EnableSelection setting this may fail.

NickHK
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top