Link to home
Start Free TrialLog in
Avatar of DarinOBrien
DarinOBrien

asked on

Excel - how to create button to clear certain fields

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
Avatar of zorvek (Kevin Jones)
zorvek (Kevin Jones)
Flag of United States of America image

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

Kevin
Avatar of DarinOBrien
DarinOBrien

ASKER

The fields are just cells for variable data entry...very simple spreadsheet.
The the macro would look something like:

Public Sub ClearFields()

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

End Sub

Kevin
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
SOLUTION
Avatar of zorvek (Kevin Jones)
zorvek (Kevin Jones)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
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
Thanks to both of you for the excellent help!