Clear cell contents

Ozhatch

Board Regular
Joined
Oct 31, 2011
Messages
65
Office Version
  1. 2007
Platform
  1. Windows
I have a spreadsheet (Excel 2003) which I want to distribute to the members of a group. There is a requirement to clear the contents of a range of cells (I15:Q29) at the end of each year.

For security purposes, many of the cells in that range are locked, but do not contain any data. The sheet and the workbook are password protected.

I would like to insert an icon in that range of cells that will clear the contents of all the cells in the range.

Can anybody please suggest a way to do this?
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Welcome to the Board!

You can record a macro unprotecting the sheet, selecting the range and deleting it, then reprotect. The only thing the recorder won't give you is the password, but that's easy. Here's an example:

Code:
ActiveSheet.Unprotect "Password Goes Here"
   Range("I15:Q29").ClearContents
ActiveSheet.Protect "Password Goes Here"

You also might consider naming the range in question and just using that.

HTH,
 
Upvote 0
That sounds good, thank you. Is is possible to set up, say, a text box with the words "Reset Form" that would run the macro? A lot of the users are not very Excel-savvy, and I want to make the form as user-friendly as possible.

Also, I would prefer not to have to explain how to run a macro to some of the users... :eek:
 
Upvote 0
You can draw any shape from the Shapes Gallery (I generally use a rounded rectangle); once it's drawn you'll get a prompt to assign a macro to it.
 
Upvote 0
Thanks Smitty! Done, and working perfectly.
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,249
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top