"clear all" causes cell to become protected

[wombat47]

Have a protected sheet with some cells available for input.
However, if user uses the edit clear all command on an unprotected cell; this causes the cell to become protected and no data can now be entered.
Is there a way around this?

 

[Barry Katcher]

Never used the Edit>Clear>All until now, and it does the same for me. Try using Edit>Clear>Contents.

 

[wombat47]

Thing is worksheet is used by others!! One of them did it and reported worksheet failure. So - question is how can I stop the ability to use the "edit - clear all" command on a protected worksheet in an unprotected cell as doing so causes the cell to become protected.

 

[Barry Katcher]

From the menu, click on Tools>Customize, choose the Commands tab and select Edit. With that open, click on the menu again, this time Edit>Clear. In the drop-down list, drag All off of the list and drop it anywhere in the spreadsheet. This removes it from the Edit menu. To put it back again, do the same thing, but drag the All from the Commands tab over to the Edit>Clear menu.

Be aware that this will do it on the computer you are on; you will have to do it to other people's computers if they are sharing the workbook.

 

[Jaafar Tribak]

Here is a VBA trick :


The code below assumes that the WorkBook is named Book1, the protected Sheet is named Sheet1 and the unlocked Cell is A1. Change these as required.


Place this in the ThisWorkBook Module :

Private Sub Workbook_BeforeClose(Cancel As Boolean)

    Application.CommandBars.FindControl(ID:=1964).Reset

End Sub


Private Sub Workbook_Open()

    Application.CommandBars.FindControl(ID:=1964).OnAction = "Customise"

End Sub

Place this in aStandardModule :

Public Sub Customise()

    ' Change the Book,Sheet and Cell adresses to meet your data arrangement.
    If ActiveWorkbook Is Workbooks("Book1.xls") Then
        If ActiveSheet Is Worksheets("Sheet1") Then
            If ActiveCell.Address = "$A$1" Then
                If ActiveSheet.ProtectContents = True Then
                    CommandBars.FindControl(ID:=1964).OnAction = "CustomClear"
                    Exit Sub
                End If
            End If
        End If
    End If
    CommandBars.FindControl(ID:=1964).Reset
          
End Sub


Public Sub CustomClear()

    ActiveSheet.Unprotect
    With ActiveCell
        .ClearComments
        .ClearContents
        .ClearFormats
        .ClearNotes
        .ClearOutline
        .Locked = False
    End With
    ActiveSheet.Protect
    
End Sub

Save the WorkBook, close it and reopen it.

Now , activate Cell A1 and choose Edit>Clear>All from the Main Menu and the Cell will be Cleared of everything but will stay unlocked !

Regards.

 

[Barry Katcher]

Jaafar, you de man!