Searching for locked/unlocked cells
 

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

Searching for locked/unlocked cells
 

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



"Karthik Bhat - Bangalore" wrote in message
ups.com...
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

Searching for locked/unlocked cells
 

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

NickHK

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
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



"Karthik Bhat - Bangalore" wrote in message
ups.com...
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