Select unlocked cells in a protected sheet.

Pittsy

New Member
Joined
Sep 15, 2016
Messages
25
Me again.

A slightly more challenging one this time.

I have a worksheet with multiple formula some on hidden rows, all nicely laid out and formatted to make an easy to use standard report. I have the sheet protected from accidental editing by well meaning colleagues. All good!

I want to insert a clear all button so that a user can get a clear form easily (there is no need to keep a filled copy).
I have entered VBA code to select all unlocked cells and then clear contents.

My issue comes when I protect the sheet I get an error message saying Can't do this to a protected sheet.
Why if all the cells selected are unlocked??

What am I missing??

Regards
Pittsy
 
Last edited:

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Assuming your code selects the unlocked cells before clearing them (as you described), did you check the box that allows users to select unlocked cells when you protected the worksheet?

Here's the code that worked for me:

Code:
Public Sub ClearUnlockedCells()
  Dim UnlockedCells As Range
  Dim Cell As Range
  
  On Error GoTo ErrHandler
  If Not TypeOf ActiveSheet Is Worksheet Then
    MsgBox "You must activate a worksheet first.", vbExclamation
    GoTo ExitProc
  End If
  
  For Each Cell In ActiveSheet.UsedRange
    If Not Cell.Locked Then
      If UnlockedCells Is Nothing Then
        Set UnlockedCells = Cell
      Else
        Set UnlockedCells = Union(UnlockedCells, Cell)
      End If
    End If
  Next Cell
  
  If UnlockedCells Is Nothing Then
    MsgBox "No unlocked cells were found.", vbExclamation
  Else
    UnlockedCells.ClearContents
  End If
  
ExitProc:
  On Error Resume Next
  Set UnlockedCells = Nothing
  Set Cell = Nothing
  Exit Sub
  
ErrHandler:
  MsgBox Err.Description, vbCritical
  Resume ExitProc
End Sub
 
Last edited:
Upvote 0
Here's a general solution that is much faster than looping through ranges of cells because it takes advantage of the high-speed compiled code in which Excel VBA's Find method is implemented.

VBA Code:
Function GetUnlockedCells(SearchRange As Range) As Range 'Union
    '
    'Finds all unlocked cells in the specified range and returns a range-union of them.
    '
    'AUTHOR: Peter Straton
    '
    '*************************************************************************************************************

    Dim FoundCell As Range
    Dim FirstCellAddr As String
    Dim UnlockedUnion As Range

    'NOTE: When finding by format, you must first set the FindFormat specification:

    With Application.FindFormat
        .Clear
        .Locked = False 'This is the key to this technique
    End With

    'NOTE: Unfortunately, the FindNext method does not remember the SearchFormat:=True specification so it is
    'necessary to capture the address of the first cell found, use the Find method (instead) inside the find-next
    'loop and explicitly terminate the loop when the first-found cell is found a second time.

    With SearchRange
        Set FoundCell = .Find(What:="", After:=.Cells(1, 1), LookIn:=xlFormulas, LookAt:=xlPart, _
                              SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, _
                              SearchFormat:=True)
        If Not FoundCell Is Nothing Then
            FirstCellAddr = FoundCell.Address
            Do
'                Debug.Print FoundCell.Address
                If UnlockedUnion Is Nothing Then
                    Set UnlockedUnion = FoundCell.MergeArea                         'Include merged cells, if any
                Else
                    Set UnlockedUnion = Union(UnlockedUnion, FoundCell.MergeArea)   '           "
                End If

                Set FoundCell = .Find(What:="", After:=FoundCell, SearchDirection:=xlNext, SearchFormat:=True)
            Loop Until FoundCell.Address = FirstCellAddr
        End If
    End With
    Application.FindFormat.Clear        'Cleanup

    Set GetUnlockedCells = UnlockedUnion
End Function 'GetUnlockedCells
 
Upvote 0

Forum statistics

Threads
1,224,820
Messages
6,181,161
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top