"undo" macro!?

[Guest]

Guys-

I have a macro that I perform, but I would like to create another macro to "undo" this macro! The reason is b/c once I run the original macro, there are instances in which I would like my spreadsheet to go back to the way it was before the macro, and the "undo" function is not an option in this case. So, I would like to create an "undo" macro to perform this function. Any ideas?

 

[Mark O'Brien]

This could be tricky. One suggestion is save the workbook in a "temp" directory when you run it.

You could then seamlessly reload this by hitting an "undo" button or toolbar option.

_________________<font color = green> Mark O'Brien
This message was edited by Mark O'Brien on 2002-03-11 13:00

 

[Jack in the UK]

Hi

Scripts by nature a fuzzy little nasty thinks in one sence they dont undo !

What you need to do is :
1 set reversal of you code IE return it to as was on another command button
2 place rthe reset undos in auto_Close to remake as you want.

Does this help...


BTW depend of what you do and what i guess..

 

[Guest]

I didn't exactly follow that last post...I think I need a little more explanation (sorry!)

 

[]-[ /-\ \/\/ /-\ | | /-\]

Hello,

butt-ugly code, but I thought I would take a stab at it anyway. It's amateurish, but it works. (Maybe someone has better idea or can refine?) Please go easy on the tomatoes!

place in 'thisworkbook' object:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim TWbName$
TWbName = ThisWorkbook.Path & "" & Mid(ThisWorkbook.Name, 1, Len(ThisWorkbook.Name) - 4)
On Error Resume Next
Kill TWbName & ".xlu"
Kill TWbName & ".del"
End Sub

Place in a module:

Sub SetUndoPoint()
Dim TWbName$
TWbName = ThisWorkbook.Path & "" & Mid(ThisWorkbook.Name, 1, Len(ThisWorkbook.Name) - 4)
ThisWorkbook.SaveCopyAs TWbName & ".xlu"
End Sub


Sub UndoMacro()
Dim TWbName$
TWbName = ThisWorkbook.Path & "" & Mid(ThisWorkbook.Name, 1, Len(ThisWorkbook.Name) - 4)
Application.ScreenUpdating = False
Application.EnableEvents = True
ThisWorkbook.SaveAs TWbName & ".del"
Workbooks.Open TWbName & ".xlu"
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs TWbName & ".xls"
Application.DisplayAlerts = True
Kill TWbName & ".xlu"
ThisWorkbook.Close False
End Sub

Then, just add this line to the first line of the macro you'd like to be able to undo:

SetUndoPoint

The UndoMacro macro can be assigned to a custom button or menu, then run to restore workbook before the macro was run.

Aloha!
This message was edited by ]-[ /-\ \/\/ /-\ | | /-\ |\| on 2002-03-12 04:22

 

[RET79]

hey,

I like that code u just quoted. I wonder if that sort of an approach would help me with what I want with regards to the topic on saving work while u work - i.e. saving work automatically every 5 minutes in ANOTHER workbook perhaps in the temp folder so that I could retrieve something if it all crashes.

That way, I always have a backup without bothering to set one up :wink:

RET79