Clear cell contents

[Ozhatch]

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?

 

[Smitty]

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:

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]

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...

 

[Smitty]

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]

Thanks Smitty! Done, and working perfectly.