Excel - reset button in spreadsheet

Asked By C.M.G. on 10-Feb-07 02:53 PM
Is it possible to place a button in a spreadsheet that will undo all the
changes that have been made since the spreadsheet was opened?

Thanks,

Clem.




Gord Dibben replied on 10-Feb-07 04:13 PM
Clem

There is one.

You will see it up in right-hand corner.

It is an "X" and when clicked, will close the workbook.

Click NO when asked to save changes.


Gord Dibben  MS Excel MVP
C.M.G. replied on 10-Feb-07 04:20 PM
Gord,

I do not suppose you know of a way of doing this without closing and
re-opening the spreadsheet?

Clem.
Gord Dibben replied on 10-Feb-07 07:18 PM
Not really.

I don't understand why you would want to revert to original in the first place
after making changes

If you are just working on one worksheet, make a copy of that when you open the
workbook.

Work on that copy then delete it when done with your experimentation.


Gord
C.M.G. replied on 11-Feb-07 10:23 AM
Gord,

Here is the reason: some of the data entry fields also contain formulas.
Field B is calculated from field A, and vice versa. Usually, a user will
only be interested in calculating in one direction, not the other. But
occasionally, a user may be interested in calculating first in one
direction, then the other. In that case, the formula in field A will have
been overwritten by the data entry, and it would be nice to have a "reset"
button.

Alternatives would be: Somehow prevent the formula from being overwritten by
the data entry (but I don't think that is possible),

or,

Make separate data entry and output cells (in this application, less elegant
than a "reset" button).

Any other ideas?

Thanks,

Clem.
Dave Peterson replied on 11-Feb-07 11:11 AM
You can prevent users from overwriting cells with formulas (or any other cells)
by locking those cells and protecting the worksheet.

Select the cells to lock
format|cells|Protection tab|check Locked.

Select the cells to leave unlocked
format|cells|protection tab|uncheck Locked.

I think that using a couple of cells makes sense.

I've done this kind of thing.

Column A contains the user typed input
Column B contains the "default" value--usually based on some calculation
Column C contains the value that further calculations will use.

=if(a2<>"",a2,b2)

Protecting columns B:C would keep the user from making changes to cells with
formulas.

============
But you could do something like this:

Say your user types answers in column E.
Create a hidden sheet that contains the defaults for all the cells in column E.
(You'll have to update this sheet whenever you change column E on the real
sheet.)

Then plop a button from the Forms toolbar somewhere on that "real" worksheet.

Option Explicit
Sub RevertToOrig()
Dim resp As Long

resp = MsgBox(Prompt:="Are you sure you want to revert?", Buttons:=vbYesNo)

If resp = vbNo Then
Exit Sub
End If

Worksheets("hidden").Range("e:e").Copy _
Destination:=ActiveSheet.Range("e1")

End Sub

But you can't let the users insert/delete rows.  And if you add/delete rows or
make other changes, you'll have to remember to update that hidden worksheet--if
you don't update it, you could be in for a world of work to fix all the
formulas/values.





--

Dave Peterson
C.M.G. replied on 11-Feb-07 01:01 PM
Dave,

That sounds great! I will give it a try.

Many thanks,

Clem.
ilia replied on 12-Feb-07 02:32 PM
You can have a number of ways to do this.  One is to make a copy of
the sheet on open, then add a button called Revert or something, that
will replace the edited sheet with the copy of the original.  You can
keep it VeryHidden if you like, that way you can only unhide it from
VBA.
C.M.G. replied on 12-Feb-07 06:24 PM
Thank you, Ilia. I see that there are several ways to achieve my goal. I will
experiment.

Clem.
Dan Greene replied on 21-Oct-09 03:50 PM
I am looking for the same thing and here is a reason why. I use excel to make invoices for my business. I want to make a button at the bottom of the page so when I click it it will clear all of the Services/Prices that I input so I can print and move along to the next invoice quickly. Having to exit and reload my invoice spreadsheet if more time consuming than simply clicking a button and clearing out a few specific fields. Thank you.




Not really.
10-Feb-07

Not really.

I do not understand why you would want to revert to original in the first place
after making changes

If you are just working on one worksheet, make a copy of that when you open the
workb

Previous Posts In This Thread:


reset button in spreadsheet
Is it possible to place a button in a spreadsheet that will undo all the
changes that have been made since the spreadsheet was opened?

Thanks,

Clem.


ClemThere is one.You will see it up in right-hand corner.
Clem

There is one.

You will see it up in right-hand corner.

It is an "X" and when clicked, will close the workbook.

Click NO when asked to save changes.


Gord Dibben  MS Excel MVP


Gord,I do not suppose you know of a way of doing this without closing and
Gord,

I do not suppose you know of a way of doing this without closing and
re-opening the spreadsheet?

Clem.



Not really.
Not really.

I do not understand why you would want to revert to original in the first place
after making changes

If you are just working on one worksheet, make a copy of that when you open the
workb


Re: reset button in spreadsheet
Gord,

Here is the reason: some of the data entry fields also contain formulas.
Field B is calculated from field A, and vice versa. Usually, a user will
only be interested in calculating in one direct


You can prevent users from overwriting cells with formulas (or any other
You can prevent users from overwriting cells with formulas (or any other cells)
by locking those cells and protecting the worksheet.

Select the cells to lock
format|cells|Protection tab|check Locked.


Dave,That sounds great!
Dave,

That sounds great! I will give it a try.

Many thanks,

Clem.


You can have a number of ways to do this.
You can have a number of ways to do this.  One is to make a copy of
the sheet on open, then add a button called Revert or something, that
will replace the edited sheet with the copy of the original.


Thank you, Ilia. I see that there are several ways to achieve my goal.
Thank you, Ilia. I see that there are several ways to achieve my goal. I will
experiment.

Clem.

EggHeadCafe - Software Developer Portal of Choice
ASP.NET Process Killer Revisited
http://www.eggheadcafe.com/tutorials/aspnet/761a922d-7b70-4df6-9e23-4a372d7bc912/aspnet-process-killer-re.aspx
Dan Greene replied on 21-Oct-09 04:16 PM
Figured it out.
Open the Control Toolbox
Make a Button and in VBA make a clear macro using the command:
Range([Cell1],[Cell2]).ClearContents




Reason Why
21-Oct-09

I am looking for the same thing and here is a reason why. I use excel to make invoices for my business. I want to make a button at the bottom of the page so when I click it it will clear all of the Services/Prices that I input so I can print and move along to the next invoice quickly. Having to exit and reload my invoice spreadsheet if more time consuming than simply clicking a button and clearing out a few specific fields. Thank you.

Previous Posts In This Thread:

EggHeadCafe - Software Developer Portal of Choice
Select Subset of Rows in ADO.NET DataTable
http://www.eggheadcafe.com/tutorials/aspnet/457daa01-063a-42f0-8a15-185933676c4f/select-subset-of-rows-in.aspx