People always ask, how to do you lock Excel? In fact you have to unlock Excel and then protect the sheet. The default setting for all cells is locked, so you need to unlock input cells.
I covered this topic in a recent post.
If you have unlocked your input cells you may want to clear them all and get them ready for input.
[Check out the comments below for a suggestion by Rick Rothstein an Excel MVP with an elegant technique.]
A macro can go through all the unlocked cells and remove any entries. The assumption here is that only the input cells are unlocked. I have included two macros. One for the current sheet and one for the whole file. Before we go through them let’s look at two other useful macros.
All four macros are in a file that you can download at the button at the bottom of this post.
But first a warning.
MACROS CLEAR THE UNDO LIST. THIS MEANS YOU CAN’T UNDO THE MACRO OR ANYTHING YOU DID BEFORE YOU RAN THE MACRO. IT IS ALWAYS A GOOD IDEA TO SAVE THE FILE BEFORE YOU RUN THE MACRO. IF SOMETHING BAD HAPPENS THEN CLOSE THE FILE WITHOUT SAVING.
First check your unlocked cells
You may have accidentally unlocked some non-input cells (Eg headings, constants or formulas) in the sheet. I have included two macros to check and/or report on all the unlocked cells. You can run these macros before clearing any unlocked input cells.
The first macro works on the current sheet and is used if you don’t have many input cells.
Message Box macro
If you think there may only be a few unlocked cells with entries this macro will display a message box for each unlocked cell that has an entry. If there are a lot this will take some time and then you can use the next macro.
Sub CheckUnLockedCells() 'This macro uses message boxes to displays details of unlocked cells ' that have values in the current sheet Dim c As Range Dim i As Long On Error GoTo HandleError For Each c In ActiveSheet.UsedRange If c.Locked = False And (c.Value <> "" Or c.Formula <> "") Then MsgBox ActiveSheet.Name & " " & c.Address & " has " & c.Value i = i + 1 End If Next c 'final message with how many unlocked cell have entries If i = 0 Then MsgBox ActiveSheet.Name & " - all unlocked cells were empty." Else MsgBox ActiveSheet.Name & " - has " & i & " unlocked cell(s) with entries." End If HandleExit: Set c = Nothing Exit Sub HandleError: MsgBox "An error has occurred - macro stopped" GoTo HandleExit End Sub
Unlocked cell report macro
This macro works on the whole file. It creates a table report of all the unlocked cell with entries, including a formula if they have one. You may need to lock some of these formula cells if they aren’t input cells. You can re-run this macro to see any differences.
Note: If you have the Microsoft Inquire add-in, it has a similar report.
Sub UnLockedCellsReport() 'this macro reports on all the unlocked cells in the file that have values Dim c As Range Dim ws As Worksheet Dim wsRep As Worksheet Dim r As Long Dim strName As String On Error GoTo HandleError 'name used for report sheet strName = "_Unlocked_Cells_" 'see if the report sheet already exists For Each ws In Worksheets If ws.Name = strName Then Set wsRep = ws ws.Cells.Value = "" Exit For End If Next ws 'create the report sheet if it doesn't exist If wsRep Is Nothing Then Set wsRep = Sheets.Add wsRep.Name = strName End If r = 2 'sheet headings wsRep.Range("A1:D1") = Array("Sheet Name", "Unlocked Cell", "Value", "Formula") wsRep.Range("A1:D1").Font.Bold = True For Each ws In Worksheets If ws.Name <> strName Then For Each c In ws.UsedRange If c.Locked = False And (c.Value <> "" Or c.Formula <> "") Then 'populate the report wsRep.Cells(r, 1) = ws.Name wsRep.Cells(r, 2) = Replace(c.Address, "$", "") wsRep.Cells(r, 3) = c.Value If Left(c.Formula, 1) = "=" Then wsRep.Cells(r, 4) = "'" & c.Formula r = r + 1 End If Next c End If Next ws wsRep.Range("A1:D1").EntireColumn.AutoFit HandleExit: Set c = Nothing Set ws = Nothing Set wsRep = Nothing Exit Sub HandleError: MsgBox "An error has occurred - macro stopped" GoTo HandleExit End Sub
Clear your unlocked cells
Once you are confident that only input cells are unlocked and you want to clear them then you can run the following macro. Remember macros can’t be undone so save your file before running this.
Sub ClearUnLockedCells() 'this macro clears entries in unlocked cells ' in the active sheet Dim c As Range On Error GoTo HandleError For Each c In ActiveSheet.UsedRange If c.Locked = False Then c.Value = "" Next c HandleExit: Exit Sub HandleError: MsgBox "An error has occurred - macro stopped" GoTo HandleExit End Sub
If you want to do this for the whole file use the macro below
Sub ClearUnLockedCellsFile() 'this macro clears entries in unlocked cells ' in all the sheet in the file Dim ws As Worksheet Dim c As Range On Error GoTo HandleError For Each ws In Worksheets For Each c In ws.UsedRange If c.Locked = False Then c.Value = "" Next c Next ws HandleExit: Set ws = Nothing Set c = Nothing Exit Sub HandleError: MsgBox "An error has occurred - macro stopped" GoTo HandleExit End Sub
The reason I use.
c.Value = ""
Is that Merged Cells can cause the macro to crash when you use ClearContents.
You can download a file with all the above code at the button below.
The macros are all in the ModUnocked module – see below. Press Alt + F11 to see the macro window.