Excel - how to create button to clear certain fields

DarinOBrien
DarinOBrien used Ask the Experts™
on
I've got a spreadsheet with about 10 fields that the end-user can change.  I would like to create a button (or something) that would allow the end-user to quickly clear all data in those fields.  I need something fool proof!  

Any suggestions?

Thanks,
Darin
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2008

Commented:
Are the fields cells or controls? If controls, are they ActiveX controls or Forms controls?

Kevin

Author

Commented:
The fields are just cells for variable data entry...very simple spreadsheet.
Top Expert 2008

Commented:
The the macro would look something like:

Public Sub ClearFields()

    Sheet1.Range("A1").ClearContents
    Sheet1.Range("B2").ClearContents
    ...

End Sub

Kevin
CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

Top Expert 2008

Commented:
Or you can name the cells to be cleared "DataEntryCells" and the run this macro which would require no modifications:

Public Sub ClearFields()

    Range("DataEntryCells").ClearContents

End Sub

Kevin
Top Expert 2008
Commented:
To create a named range select the cell or cells to name (hold down CTRL to select multiple disparate cells), open the Define Name dialog (in Excel 2003 and earlier choose the menu command Insert->Name->Define, in Excel 2007 and later navigate to the Formula tab, click "Name Manager" and click "New"), type the name in the topmost text edit field and click OK. Selected cells can also be named by entering the name in the Name Box located on the left end of the Formula Bar. Note that when using the latter technique, if the name has already been defined, the previously defined range will be selected instead of the name being redefined with the current selection.

To rename a range, in Excel 2003 and earlier, choose the menu command Insert->Name->Define to display the Define Name dialog, find and select the name in the list, edit the name in the top-most text edit box, and click "Add". Then find and select the old name and click Delete. In Excel 2007 and later, navigate to the Formula tab, click "Name Manager", select the name to edit and click "Edit". Change the name and click "OK".

To change the range to which an existing name refers, in Excel 2003 and earlier, choose the menu command Insert->Name->Define to display the Define Name dialog, find and select the name in the list, edit the range in the bottom text edit box, and click "Add". In Excel 2007 and later, navigate to the Formula tab, click "Name Manager", select the name to edit and click "Edit". Change the range address and click "OK".

A defined name can contain only characters, numbers, and underscores. It cannot be a cell reference (e.g., A1 or R1C1). It cannot start with a number. And it can not
be any of the following values:

Print_Titles
Print_Area
Database
Criteria
Data_Form
Extract
Consolidate_Area
Sheet_Title
Recorder
_FilterDatabase
Auto_Open
Auto_Close
Auto_Activate
Auto_Deactivate

Kevin

Author

Commented:
Cool but I've never done a macro before.  Can you give me the skinny?  Can you also tell me how to "name the cells"?

thanks,
darin
Top Expert 2008
Commented:
To add VBA code to a regular or general module in an Excel workbook, press ALT+F11 to open the VBA development environment (VBE). Select the menu command Insert->Module to create a new VBA module. Paste the code into the document window that appears. Press ALT+F11 to return to the Excel workbook.

To add a command button to a worksheet that runs a macro, right-click on the menu bar and select Forms. Click on the gray rectangle icon. With the mouse, click where the upper left corner of the command button is to be placed and drag the mouse down and to the right to create the shape of the command button. A dialog is displayed asking for the macro to run when the command button is clicked. Select the desired macro and click the OK command button. If the macro is Private then it won't be listed but it can still be attached to the command button by manually entering its name.

In Excel 2007 and later, navigate to the Developer tab and select Insert in the Controls group. From the menu select the Button control from the Forms controls (top set). With the mouse, click where the upper left corner of the command button is to be placed and drag the mouse down and to the right to create the shape of the command button. A dialog is displayed asking for the macro to run when the command button is clicked. Select the desired macro and click the OK command button. If the macro is Private then it won't be listed but it can still be attached to the command button by manually entering its name.

Kevin

Author

Commented:
Hi zvorek,

Sorry for the delay.  I'm anxious to give this a shot but it's going to take a few days before I have time to work on this.  I'll keep you posted.  In the meantime, thanks for the detailed instructions; much needed for a newbie like me.

darin

Author

Commented:
I'm trying to use the "clear-data" macro provided by Fayaz.  The problem I'm having is the following message:

"Macros in this worbook are disabled because the security level is high, and the macros have not been digitally signed or verified as safe.  To run the macros, you can either have them signed or change your security level. Click Help for more information."

How do I get it to run on my computer?  More importantly, I intend to send this to some of my customers.  Will they run into the same message/problem???

thanks,
darin
Top Expert 2008

Commented:
Macros in a workbook will only run if they are allowed to run. Starting with Excel 2000, Excel provides macro security controlling how macros run. In Excel versions 2000 and 2002 (XP) there are three levels of security:

Low - Macros are always allowed to run.

Medium - Macros run if allowed by clicking "Enable Macros" when prompted when opening workbooks.

High - Macros will not be allowed to run unless the workbook contains an accepted digitial certificate. Each time a workbook with a digital certificate is opened the Security Warning dialog is displayed asking if the digital certificate is to be installed. Once installed the workbook can be opened and macros enabled without any warnings.

In Excel 2003 a new security level was introduced:

Very High - Only macros installed in trusted locations will be allowed to run. All other signed (with a digitial certificate) and unsigned macros will not be allowed to run. A trusted location is either of the XLStart folders.

The security level is set at the application level and affects all workbooks opened on a specific user account. It is set by choosing the menu command Tools->Macro->Security and navigating to the "Security Level" tab.

In Excel 2007 the options are basically the same as with 2003 but have been renamed:

Low is now "Enable all macros"
Medium is now "Disable all macros with notification
High is now "Disable all macros except digitally signed macros"
Very High is now "Disable all macros without notification"

To change the setting in Excel 2007, click the Microsoft Office Button, click Excel Options, click Trust Center, click Trust Center Settings, and click Macro Settings. In Excel 2010 and later, navigate to the File tab, click Options, click Trust Center, click Trust Center Settings, and click Macro Settings.

In Excel 2007 and later, the ability to specify trusted folders was added. Any workbook with macros stored in a trusted folder is opened with macros enabled and no warnings. To add a trusted folder in Excel 2007, click the Microsoft Office Button, click Excel Options, click Trust Center, click Trust Center Settings, click Trusted Locations, and click "Add new location". In Excel 2010 and later, navigate to the File tab, click Options, click Trust Center, click Trust Center Settings, click Trusted Locations, and click "Add new location".

Kevin

Author

Commented:
Thanks to both of you for the excellent help!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial