Clearing data from Excel 2003 Form
 

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

Clearing data from Excel 2003 Form
 

If the cells are for direct entry it's unlikely they'll have a formula
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 cll


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=120852

Clearing data from Excel 2003 Form
 

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" wrote:


If the cells are for direct entry it's unlikely they'll have a formula
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 cll


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=120852

Clearing data from Excel 2003 Form
 

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

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

berniean;436168 Wrote:
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

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=120852