+ Reply to Thread
Results 1 to 3 of 3

Searching for locked/unlocked cells

  1. #1
    Karthik Bhat - Bangalore
    Guest

    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


  2. #2
    Bernie Deitrick
    Guest

    Re: 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" <karthik.bhat@db.com> wrote in message
    news:1126775325.196916.52620@g44g2000cwa.googlegroups.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
    >




  3. #3
    NickHK
    Guest

    Re: 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
    news:eO5qKjfuFHA.4080@TK2MSFTNGP12.phx.gbl...
    > 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" <karthik.bhat@db.com> wrote in message
    > news:1126775325.196916.52620@g44g2000cwa.googlegroups.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
    > >

    >
    >




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1