CODEWITHSUNDEEP
excelvba
Glib
Glib

Reputation: 262

Check if unsaved workbook is closed

I have a workbook that when opened, creates another blank workbook that may have some data copied across to it. I want to test when closing the workbook whether the created workbook is still open.

I tested this on a blank workbook. For the 'ThisWorkbook' code I have

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    If Refreshwkbk Is Nothing Then
        MsgBox "other wkbk closed"
    Else
        MsgBox "other wkbk open"
        Refreshwkbk.Activate
    End If
End Sub

Private Sub Workbook_Open()
    Call myRefresh
End Sub

And in a module I have

Public Refreshwkbk As Workbook

Sub myRefresh()
    Set Refreshwkbk = Workbooks.Add
End Sub

The code will break at Refreshwkbk.Activate with an automation error.
The error is Run-time error '-2147221080 (800401a8)'

How would I test that the newly created workbook is still open?

Upvotes: 0

Views: 89

Answers (2)

Pᴇʜ
Pᴇʜ

Reputation: 57683

Easiest way would be trying to .Activate it and if that throws an error it is already closed.

Private Sub Workbook_BeforeClose(Cancel As Boolean)

    On Error GoTo WKBK_CLOSED
    Refreshwkbk.Activate 'if that fails it jumps to WKBK_CLOSED
    MsgBox "other wkbk open"

    Exit Sub
WKBK_CLOSED:
    MsgBox "other wkbk closed"
End Sub

Upvotes: 2

Middle
Middle

Reputation: 143

Maybe there is a better way doing this with objects, but here is an approach looping through all opened workbooks.

Dim wb As Workbook

'looping through currently opened workbooks
For Each wb In Workbooks
    If wb.Name = refreshwkbk.Name Then
        refreshwkbk.Activate
        MsgBox "other wkbk open"
    Else
        If refreshwkbk Is Nothing Then
            MsgBox "other wkbk closed"
        End If
    End If
Next wb

Upvotes: 0

Related Questions