CODEWITHSUNDEEP
excelvba
C-Love511
C-Love511

Reputation: 358

Closing a workbook without stopping vba?

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?

Upvotes: 1

Views: 1879

Answers (1)

Fadi
Fadi

Reputation: 3322

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

Upvotes: 1

Related Questions