Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Drop-down list on simple excel 2003 form using "form" from data me | Excel Discussion (Misc queries) | |||
Excel 2003 - Customise a Data Form for dropdown lists | Excel Discussion (Misc queries) | |||
How do I save worksheet form data into a table in Excel 2003? | Excel Discussion (Misc queries) | |||
create a data entry form template in Excel 2003? | Excel Discussion (Misc queries) | |||
Clearing Form | Excel Programming |