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