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

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>

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

NickHK

"Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >
>
>