App_WorkbookBeforeClose

[Kent Prokopy]

I am using application level events to watch when workbooks close.

Private Sub App_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean)
Dim c As Object
For Each c In Application.CommandBars("worksheet Menu bar").Controls
If c.Caption = "Finops" Then
If App.Workbooks.Count = 1 Then
'''''If Workbooks("Genesis.xla").Name <> "Genesis.xla" Then
Application.CommandBars("WorkSheet Menu
Bar").Controls("Finops").Visible = False
'''''End If
End If
End If
Next c
End Sub

The problem is this fires when an xla is unloaded as well. I need to test if
there is more then one workbook open in this method. If workbooks.count = 1
then I hide my menu. The problem is that the xla is not included in the
workbooks.count...
How can I best find out if the xla is the file that is closing? and skip the
hide line if so...

 

[Kent Prokopy]

Never mind. I found the answer...

Private Sub App_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean)
Dim c As Object
For Each c In Application.CommandBars("worksheet Menu bar").Controls
If c.Caption = "Finops" Then
If App.Workbooks.Count = 1 Then
If Right(ActiveWorkbook.Name, 3) <> "xla" Then
Application.CommandBars("WorkSheet Menu
Bar").Controls("Finops").Visible = False
End If
End If
End If
Next c
End Sub