Closing a workbook without stopping vba?

Question

I keep running into an issue where the worksheet in question gets updated, and then doesn't reflect the changes until I close and re-open the book. It's not really a big deal, I just want to simplify it a bit into a single button click.

I was told that this is impossible to do because closing a workbook will immediately stop execution of code, so you can't reopen the book. But since, I have learned about the PERSONAL.xlsb, and figured since it's a persistent workbook, it could handle the code execution to save, close, and reopen the workbook.

Basically, here's what I've got in the main worksheet:

Public Sub Refresh()
    ActiveWorkbook.Save
    Application.Run "PERSONAL.xlsb!Module1.RefreshCurrentSheet",ThisWorkbook.Name,_
                     ThisWorkbook.FullName
End Sub

Which then calls the personal.xlsb macro "Refreshcurrentsheet":

Private Sub RefreshCurrentSheet(ByVal sheetname, Optional ByVal sheetfullname = 0)
    Workbooks(sheetname).Close
    MsgBox "So far so good!" 'I never see this box
    Workbooks(sheetfullname).Open
    MsgBox "No errors here..." 'I never see this box
End Sub

This successfully closes the workbook in question, but code execution stops and it doesn't reopen. Is there a way around this? Some way to run the second macro in the persistent sheet without code execution stopping?

Is the Refresh sub in a different workbook then the Personal.xlsb? — Scott Holtzman
Yes, it is on a separate book. I feel like this is where the problem is, because all of the code is called from this book, even though the rest is written in PERSONAL.XLSB — C-Love511
Once you reopen the book from another book (macro) you have to restart whatever code you want run in the newly opened book. — Chrismas007
your problem is with the Workbooks(sheetfullname).Open statement. It's incorrect syntax. Change it to Workbooks.Open sheetfullname. — Scott Holtzman

Fadi Fadi · Accepted Answer · 2016-02-01T18:46:46

We can reopen the workbook using this code inside it (there is no need to use other workbook):

Sub test()
 Application.DisplayAlerts = False
 Workbooks.Open ThisWorkbook.FullName
 Application.DisplayAlerts = True
End Sub

Closing a workbook without stopping vba?

1 Answers