Excel 2013 - issue when closing multiple workbooks if one workbook is hidden

Question

I've written a program in Excel VBA which uses a UserForm to enter data by the users. Specifically, it is a Telemarketing Tracker tool: the user fills in the details of the call in a text box on the UserForm and then clicks the relevant button to indicate whether it was a good or bad call, and can then continue with the next call.

This data is stored on a worksheet and our users often prefer to hide the workbook and just view the UserForm. I have developed a couple of methods of hiding the workbook. If there is just one workbook open, I hide the Excel Application. If there is more than one workbook open, I just hide the window. Here is the code I use for this:

Private Sub HideUnhideButton_Click() 'User clicks Hide/Unhide button

If Workbooks.Count > 1 Then
    Windows(ThisWorkbook.Name).Visible = Not Windows(ThisWorkbook.Name).Visible
    HideUnhideButton.Tag = Windows(ThisWorkbook.Name).Visible
Else
    ThisWorkbook.Application.Visible = Not ThisWorkbook.Application.Visible
    HideUnhideButton.Tag = ThisWorkbook.Application.Visible
End If

ThisWorkbook.Activate

End Sub

This works well but obviously certain issues arise when the user has the workbook hidden and then open a different Excel Workbook. I've worked round most of these issues, but there is one thing I can't seem to work out: if the Telemarketing workbook is hidden, with another workbook open, if I click the Close button, both workbooks try to close.

I've tried creating a class module with an Application Level event tracker so that all workbooks' close events are monitored. But my problem is that when I click the close button, the first workbook that tries to close is the hidden workbook. So I can catch the close event and prevent the hidden workbook from closing but if I set Cancel to True, it prevents all the workbooks from closing!

The only workaround I can think of is when the user tries to close a workbook, I cancel the Close Event and Unhide the hidden workbook. But I don't know how to identify which workbook the user was attempting to close - so I can't work out how to automatically close the correct workbook.

I have currently set up the WorkbookBeforeClose event as follows:

Public WithEvents A As Excel.Application

Private Sub A_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean)
If Workbooks.Count > 1 Then
    If Windows(ThisWorkbook.Name).Visible = False Then
        If Wb.Name = ThisWorkbook.Name Then
            Cancel = True
        End If
    End If
End If
End Sub

If I step through this code, I find that the Wb.Name is the name of the Telemarketing Workbook (even though it's hidden) and the name of the workbook that the user is actually trying to close does not appear at all - as far as I can work out.

Can anyone make any further suggestions?

The other thing I should mention is that it needs to work over Excel 2013 and Excel 2010.

I-J I-J · Accepted Answer · 2015-07-16T11:42:05

I'm sorry to post an answer to my own question so quickly. It sort of indicates I didn't do quite enough research beforehand. However, for anyone who has a similar problem, here's my solution. This code needs to be posted in a class module and an instance of the class needs to be created before it will work, of course.

Note: in the below example, "TT" relates to the Telemarketing Tracker

Public WithEvents A As Excel.Application

Private Sub A_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean)

Dim VIS As Boolean, myAW As Workbook

If Workbooks.Count > 1 Then 'if there is more than one workbook open...
    If Windows(ThisWorkbook.Name).Visible = False Then 'and if TT is invisible...
        If ActiveWorkbook.Name = ThisWorkbook.Name Then 'and if the workbook being closed is the TT.
            Windows(ThisWorkbook.Name).Visible = True
        Else 'more than one wb open, TT is invisible, and the workbook being closed is NOT the TT.
            Set myAW = ActiveWorkbook
            Cancel = True
            Windows(ThisWorkbook.Name).Visible = True
            Application.EnableEvents = False
            myAW.Close
            Application.EnableEvents = True
            If TelesalesForm.HideUnhideButton.Tag = "False" Then 'NB: I use a tag on the Hide/Unhide button on the UserForm to store whether the workbook should be hidden or not. 
                If Workbooks.Count > 1 Then
                    Windows(ThisWorkbook.Name).Visible = False
                Else
                    ThisWorkbook.Application.Visible = False
                End If
            End If
            Exit Sub
        End If

    ElseIf ActiveWorkbook.Name <> ThisWorkbook.Name Then
        'more than one workbook open and the TT is visible and the workbook being closed is NOT the TT
        Exit Sub
    End If
End If

'code gets to this point ONLY under the following circumstances:
    'There is only one workbook open (i.e. the TT) OR
    'There is more than one WB open and the WB being closed is the TT.

'The rest of the code goes here for managing the closing of the TT. 

End Sub

If anyone thinks of any other embellishments or improvements to the code then I would be very glad to hear of them!

Excel 2013 - issue when closing multiple workbooks if one workbook is hidden

1 Answers