Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: "undo" macro!?

  1. #1
    Guest

    Default

    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?

  2. #2
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

    _________________
    [b] Mark O'Brien

    [ This Message was edited by: Mark O'Brien on 2002-03-11 13:00 ]

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Posts
    3,184
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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


    Free Excel based Web Toolbar available here.

    Jack in the UK
    J & R Excel Solutions
    "making Excel work for you"

  4. #4
    Guest

    Default

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

  5. #5
    New Member
    Join Date
    Feb 2002
    Location
    ]-[ /-\ \/\/ /-\ | | ~~~@|_()]-[/-\!!!
    Posts
    48
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  6. #6
    Board Regular
    Join Date
    Mar 2002
    Location
    England, UK.
    Posts
    526
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

    RET79

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •