Macro to clear unlocked cells in locked workbook

[kafka]

I have a workbook containing a fairly large number of cells for data entry. I would like to lock the workbook so that headings, etc. cannot be overwritten but unlock the data entry cells. I would then like to create a macro which clears data from the unlocked cells. The macro would select the entire workbook but only clear the data entry cells. i have found that this can't be done by simply recording a macro. Is this possible?

 

[Rick Rothstein]

I simply unlock the cells I want the macro to clear (Format Cells) then lock the worksheet the (Protect Sheet). I can then simply run the macro which clears all the old data. The worksheet is locked so that the cursor can only be placed on an unlocked cell (i.e. nothing else on the worksheet can be changed).

If it might matter to you, or to a future reader of this thread, the code I posted in Message #9 will work whether the worksheet is protected or not (all that is needed is for the cells you want to be able to be cleared to be unlocked and the rest of the cells to be locked).

 

[sduttonusa]

Hi Rick,
I just came across this thread . . . I have a workbook with 50+ worksheets, each with about 200 cells that are unlocked for data entry. I was hoping to find a Macro that would allow me at the start of every year, clear all unlocked cells in my workbook. (I'm using Excel for Mac 2016 if that makes any difference.) Not being familiar with Macros, I tried your formula in Message #9, and was given a compilation error. But this could very well be because I don't know how to properly create a Macro. Could you help me solve this?

 

[sduttonusa]

Here's the error I'm receiving:

Compile error:


Named argument not found
(Module1 3:25)

The SearchFormat:= is highlighted

 

[Rick Rothstein]

Here's the error I'm receiving:

Compile error:


Named argument not found
(Module1 3:25)

The SearchFormat:= is highlighted

In my version of Excel (PC version 2010), SearchFormat is a valid named argument. It is possible that the Mac version is different from the PC version with respect to the Find function and its arguments. Unfortunately, I do not have a Mac where I can test this idea. Hopefully one of our Mac regulars will come by and figure out what the problem is for you. If you do not hear anything back in this thread in a day or two, I would start a new thread and mention you are having a Mac related problem in the thread's title, then mention what you are trying to do, show the code you are using and show the error message you got, then ask your question.

 

[sduttonusa]

Thanks so much for taking time to help me out!

 

[lray801]

Hello Rick,

I'm curious if there's any modification to the code that can be made that will resize the rows back to their original size. I'm a bit OCD and it drives me crazy that when they're cleared the rows are all different sizes. The following code, that used to work but doesn't anymore, would do it:
Sub ClearUnlockedCells()

Dim wks As Worksheet

For Each wks In ThisWorkbook.Worksheets
On Error Resume Next
wks.UsedRange.Value = vbNullString
Err.Clear: On Error Goto -1: On Error Goto 0
Next wks

Set wks = Nothing

End Sub

It would also do all the worksheets in one shot, which was really handy.

Thanks for your help, Rick!
-lray801