Clearing data from Excel 2003 Form

[berniean]

Hi, I have an Excel Form with cells for direct data entry and cells with
formulas. The form is protected so that only those cells for direct data
entry are editable. All the other cells are locked. I want to create a macro
to clear all the data from the unlocked cells. I recorded a macro using the
F5 (goto) function key, the shift key to select contiguous cells, and the
delete key to clear those cells. The problem is, if I move anything on the
form, the cell references in the macro don't change. I have to edit the macro
in the vb editor.

Is there a way to select, in a protected sheet, all unlocked cells and
delete their contents, without using cell references that may change when the
form is changed?

Any help is greatly appreciated,
Bernie

 

[p45cal]

If the cells are for direct entry it's unlikely they'll have a formul
in so:

Range("E9:H33").SpecialCells(xlCellTypeConstants
7).ClearContents

otherwise to detect unlocked cells:

For Each cll In Range("E9:H33").Cells
If Not cll.Locked Then cll.ClearContents
Next cl

 

[berniean]

Thank you for the response. Unfortunately, the first code doesn't work on
protected sheets. The second code started to work but stopped when it hit a
merged cell. I get "Run time error '1004': Cannot change part of a merged
cell." Do you know of a way around that?
Thanks again, Bernie

 

[p45cal]

First code, use:
Activesheet.protect UserInterfaceOnly:=true
to protect the sheet.

Second code, don't use merged cells! Are you wanting to clear data fro
merged cells? or are these cells headers?
If the merged cells are cells from the same row then use -Centre acros
selection- in the -Horizontal -section of the -Text Alignment- sectio
of the -Alignment -tab of the -Format Cells- dialogue box *instead* o
merging them.