Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / New Users / February 2007

Tip: Looking for answers? Try searching our database.

reset button in spreadsheet

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
C.M.G. - 10 Feb 2007 19:53 GMT
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 - 10 Feb 2007 21:13 GMT
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

>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.
C.M.G. - 10 Feb 2007 21:20 GMT
Gord,

I don't suppose you know of a way of doing this without closing and
re-opening the spreadsheet?

Clem.

> Clem
>
[quoted text clipped - 14 lines]
>>
>>Clem.
Gord Dibben - 11 Feb 2007 00:18 GMT
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

>Gord,
>
[quoted text clipped - 21 lines]
>>>
>>>Clem.
C.M.G. - 11 Feb 2007 15:23 GMT
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.

> Not really.
>
[quoted text clipped - 35 lines]
>>>>
>>>>Clem.
Dave Peterson - 11 Feb 2007 16:11 GMT
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.

> Gord,
>
[quoted text clipped - 59 lines]
> >>>>
> >>>>Clem.

Signature

Dave Peterson

C.M.G. - 11 Feb 2007 18:01 GMT
Dave,

That sounds great! I'll give it a try.

Many thanks,

Clem.

> You can prevent users from overwriting cells with formulas (or any other
> cells)
[quoted text clipped - 123 lines]
>> >>>>
>> >>>>Clem.
ilia - 12 Feb 2007 19:32 GMT
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.

> 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.
C.M.G. - 12 Feb 2007 23:24 GMT
Thank you, Ilia. I see that there are several ways to achieve my goal. I'll
experiment.

Clem.

> 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
[quoted text clipped - 8 lines]
>>
>> Clem.
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.