Clear all unprotected Cells in every Sheet of Workbook
You can create a button on your spreadsheet that when it is pressed, it will clear any data on the spreadsheet that is not protected. You can also create a button to clear the unprotected data from all worksheets of a workbook. If you have a form that you use over and over again and need to clear out some text, you can create a button to wipe all of them off the page and have just the information you want it to stay. To do this, follow these steps. This page shows how to setup a button to clear the data on all worksheets. If you need instructions on setting it up just for one sheet, click here.
-
Open the spreadsheet that you want to put the button on
-
Click the Developer tab (if it is not there, you will need to go to options and add it. Click link for instructions)
-
Click on Design Mode
 |
-
Click Insert
 |
-
Click Command Button (ActiveX Control)
 |
-
Drag where you want the button to appear and the size of the button
-
Click View Code
 |
-
Visual Basic window will appear
-
Make sure it says Commandbutton1 on the left
 |
-
Make sure it says Click on the right
 |
-
Type the following in the window
 |
-
Click the Save button
 |
-
Close Visual Basic
-
Make sure the button is selected
-
Click Properties
-
Properties will appear
 |
-
Currently the button shows Command (it says Commandbutton1 if the button is big enough)
 |
-
The text Commandbutton1 is listed under Caption
 |
-
You can change it to say what you want
-
In properties, change the text for Caption to what you want. (in this example, I am going to have it say clear Cells)
 |
-
Now the button looks like this
 |
-
Finish all the editing you are going to do
-
You will need to protect the sheet (Click the link for instructions)
-
Save the spreadsheet in a macro enabled spreadsheet
-
Click File
 |
-
Click Save as
 |
-
Under the drop down list for Save as type, select
 |