"clear all" causes cell to become protected

wombat47

New Member
Joined
Nov 26, 2005
Messages
2
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?
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Barry Katcher

Well-known Member
Joined
Feb 25, 2002
Messages
4,053
Never used the Edit>Clear>All until now, and it does the same for me. Try using Edit>Clear>Contents.
 

wombat47

New Member
Joined
Nov 26, 2005
Messages
2
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

Well-known Member
Joined
Feb 25, 2002
Messages
4,053
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

Well-known Member
Joined
Dec 5, 2002
Messages
8,067
Office Version
  1. 2016
Platform
  1. Windows
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 :

Code:
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 :

Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,536
Messages
5,572,774
Members
412,482
Latest member
arooshrana2
Top