Select unlocked cells in a protected sheet.

[Pittsy]

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

 

[ParamRay]

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:

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

 

[pstraton]

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.

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