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?
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
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,
 

Ozhatch

Board Regular
Joined
Oct 31, 2011
Messages
65
Office Version
  1. 2007
Platform
  1. Windows
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:
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
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.
 

Ozhatch

Board Regular
Joined
Oct 31, 2011
Messages
65
Office Version
  1. 2007
Platform
  1. Windows
Thanks Smitty! Done, and working perfectly.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,153
Messages
5,600,022
Members
414,356
Latest member
death20

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
Top