"Cannot run the macro" error after closing and reopening Workbook using macro

Question

So I wrote a really simple bit of code that would essentially undo the changes done by another macro. The other macro saves the workbook, then copies data to another sheet and deletes the original data. The "Undo" script I've written simply closes and reopens the workbook without saving it - not fool proof I know but it's better than nothing. It runs perfectly fine and does what I want - but when it reopens the workbook I get the following warning message "Cannot run the macro 'OpenMe'. The macro may not be available in this workbook or all macros may be disabled." All my macros still work fine after the reopening. Here's the code I have for the close and open macro:

Sub Yes_Click()

Application.OnTime Now + TimeValue("00:00:01"), "OpenMe"
ThisWorkbook.Close SaveChanges:=False

End Sub

Sub OpenMe()

MsgBox "Changes Undone"

End Sub

Sub No_Click()

Undo.Hide

End Sub

All I want is for this silly warning to not pop up! Any suggestions appreciated :)

Damian Damian · Accepted Answer · 2019-06-11T11:51:02

You need to reopen the workbook, else you won't have any code to run:

Sub Yes_Click()

    Application.OnTime Now + TimeValue("00:00:01"), "OpenMe"
    ThisWorkbook.Close SaveChanges:=False

End Sub
Sub OpenMe()

    Dim wb As Excel.Workbook
    Set wb = ThisWorkbook

    Dim pth As String
    pth = wb.FullName

    Application.Workbooks.Open pth
    MsgBox "Changes Undone"

End Sub
Sub No_Click()

    Undo.Hide

End Sub

"Cannot run the macro" error after closing and reopening Workbook using macro

2 Answers