VBA for Clearing data from the worksheet

[Guest]

Is it possible to press a cell on the worksheet to clear all entered data in
the entire worksheet?
I have a worksheet that users enter several data in different pages and then
print a report. The next user will have to clear all eneries and then enter
their own data before printing the next report. At this time, we close the
program and reopen it which is a bit of pain, just wondering if thee is a VBA
code to activate create a key (let call it "Reset all Forms" on the worksheet
to press and clear all data.
I appreciate any comments in advance.
Dori

 

[Fredrik Wahlgren]

Is it possible to press a cell on the worksheet to clear all entered data
in
the entire worksheet?
I have a worksheet that users enter several data in different pages and
then
print a report. The next user will have to clear all eneries and then
enter
their own data before printing the next report. At this time, we close the
program and reopen it which is a bit of pain, just wondering if thee is a
VBA
code to activate create a key (let call it "Reset all Forms" on the
worksheet
to press and clear all data.
I appreciate any comments in advance.
Dori

No, but I can think of some altrenatives
1) Let each user work on a copy of the original sheet.
2) If you know in advance what cells are used, you could make a macro that
cleans the sheet. You could record it and then edit the generated code to
make sure it clears all cells where data is entered.

/Fredrik.

 

[Bob Phillips]

Dori,

How about dragging a button off of the forms toolbar, and assigning a clear
macro to that?

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Hi,
What about:
1. clear all entries when the book opens
2. clear all entries when a button is clicked.

Let's first create a Clear procewdure, say ClearAllEntires
- go in the vba editor: from excel press ALT+F11 or menu Tolls>Macros>Visual
Basic Editor. There add a new code module (menu Insert > Module)
- in this new module enter and modify the following sub:
Public Sub ClearAllEntires()
'here code to clear whatever needs to be cleared.
'eg: clear sheet1 A2:C100
Thisworkbook.Worksheets("Sheet1").Range("A2:C100").ClearContents
'other clearings on a row each
End Sub

Then, let's call the macro from a button
- Make the Forms toolbar is visible: menu View>Toolbars>Forms
-from the Forms toolbar, drag a button on the sheet
- the 'Assign Macro' dialog should pop up right away, if not, right-click
the new button and choose 'Assign Macro' from the pop up menu.
Assign the macro by selecting 'ClearAllEntires' from the list.
Now when the button is clicked the ClearAllEntires macro is run ie clearing
entry ranges. Note: you can have multiple buttons calling the same
ClearAllEntires sub.

Now, let's call the macro when the book opens to clear any data saved with
the book:
- in the ThisWorkbook code module (from the Project Explorer window in the
vba editor, double-click it to open the module), use the Workbook_Open sub
Private Sub Workbook_Open()
ClearAllEntires
End Sub

 

[Gord Dibben]

Dori

CTRL + a to select all cells on a worksheet.

Edit>Clear>Contents to blank the cells.

To have the same command on a button.

Sub gone()
ActiveSheet.Cells.ClearContents
End Sub

Insert a button from the Forms Toolbar and assign the macro to that button.


Gord Dibben Excel MVP

 

[Guest]

Thank you every one for your help. I created a reset button and it works well.
Dori