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?
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.
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.
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 ThisWorkBookModule :
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Private Sub Workbook_Open()
Application.CommandBars.FindControl(ID:=1964).OnAction = "Customise"
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"
Public Sub CustomClear()
.Locked = False
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 !