"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?
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

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,611
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,021
Messages
5,834,992
Members
430,331
Latest member
Syed Yasir Hannan

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
Top