Stop macro from saving changes

[manoj.tare]

As far as I know, whenever we create macro to change soemthing in the
excel (like changing contents of some cells etc), it automatically
saves the workbook, i.e. the excel doesn't allow to undo the changes
made by macro.
In short, I want the macro to just do the changes and don't save the
excel. I will see if the changes are correct and then save.

Can we override this? (FYI, this is what I observed in excel 2000.)

Regards,
Manoj

 

[Guest]

Running a macro will not automatically save your workbook but you will not be
able to use the undo function on changes made by the macro (easily).

What you can do is save your file before running the macro. Run the macro
and review changes made. If you are not happy with the results close the file
without saving and re-open it. It should now be in the state it was in before
running the macro.

Otherwise if you really want to be able to use the undo command after
running your macro have a look at
http://www.j-walk.com/ss/excel/tips/tip23.htm on John Walkenbach's site.

Hope this helps
Rowan

 

[manoj.tare]

Thanks Rowan.
Frankly speaking, this method sounds difficult to me. Is there no other
easy method?

 

[Guest]

Not that I am aware of. You could make a backup copy of the sheet/s that will
be changed but that may lead you into all sorts of other problems with linked
formulas to other sheets etc.

 

[Bob Phillips]

I would have thought you should look at why the workbook gets saved. It must
be in the macro somewhere, or you have autosave installed.

--

HTH

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

 

[OJ]

I don;t think it does get saved does it. Just doesn't allow undo's to
give the impressino (to Manoj) that the file has been saved. Really, it
has just been changed and Undo won't work.

 

[manoj.tare]

Yes, it appeared to me when I saw, as Rowan said, that if I reopen the
excel, it won't have the changes macro has done.
And I don't have auto-save add-in installed at my place.
Please help.
Thanks,
Manoj

 

[OJ]

So if you reopen the file then the changes are gone? then it definately
HASN'T saved has it??

 

[manoj.tare]

It hasn't saved it. That's for sure. May be it disables the undo button
or just doesn't keep log of changes. But what I observed is that it
also doesn't allow to undo the changes I did before running the macro.
I.e if I have done some changes, they are available to undo before
running macro. As soon as I run it, they are lost. The undo button is
disabled and even Ctrl + Z for that matter doesn't do anything.

If I reopen, it will open in the last saved state.

Regards,
Manoj

 

[Tom Ogilvy]

Most macro commands will cause the undo history to be lost. Undo doesn't
support changes made by macros. Thus, when a macro changes the spreadsheet
environment, the context of the undo has been lost and therefore Undo gets
disabled is my surmise.

 

[manoj.tare]

So can't we save this undo history (or whatever) and restore back while
ending macro?

 

[Dave Peterson]

Yes, but you have to do the dirty work yourself.

The link that Rowan gave you in the first reply showed one way of doing this.