how to check if more than one excel file is open?

[Graff]

Hello,

I have two questions that hopefully someone might have answers to.

First, i have created a plug in for excel that i use only when a specific
file is opened. The plug in also creates a toolbar to run this code. Is it
possible to only show the toolbar when that specific file is opened? I tried
to do it from "This workbook" in workbook_open sub, but it just doesn't show
the toolbar at all. I test for opened file, but for some reason it always
shows that the file isn't opened.

Second, when i click Exit on my userform after running the code, it closes
this workbook that i'm working with. That's fine when there's more than one
excel file open, but if this file is the only one that's open, i'd like to
use something like Application.Quit to close the application. How can i check
to see if there is more than one excel file open?

Thanks for all your help.

IB

 

[Jim Thomlinson]

With no code posted it is hard to comment, so I will just be very general.
When I create custom toolbars for a specific workbook I create one function
to create the toolbar and another oen to delete the toolbar. On open or on
workbook activeate I make a call to create the toolbar. On close or on
workbook deactiveate I make a call to delete the toolbar.

As for the second question
Workbooks.Count
will give you a count of the open workbooks. Careful with this though as you
may have a personal workbook open (where you are storing macros). You will
need to test to see if Personal.xls is open...

 

[Graff]

Thanks for the quick reply Jim,

I'll try your suggestion for the second part in a bit.

As for the first question, let me try to explain the problem in greater
detail.

My code is in an add-in that is loaded every time any excel file is open, so
naturally the toolbar gets created any time any file is open. What i want to
do is to have the toolbar created only when the file i need to work with is
opened, and do nothing if any other excel file is open.

Here's what i tried, but it didn't work. It just doesn't show the toolbar at
all.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call deleteMenu
End Sub

Sub workbook_open()

Dim bk As Workbook

On Error Resume Next
Set bk = Workbooks("CombinedBatchStandardTransactions[1]")
If Not bk Is Nothing Then GoTo goOn

Set bk = Workbooks("CombinedBatchStandardTransactions[2]")
If Not bk Is Nothing Then GoTo goOn

Set bk = Workbooks("CombinedBatchStandardTransactions[3]")
If Not bk Is Nothing Then GoTo goOn

Set bk = Workbooks("CombinedBatchStandardTransactions[4]")
If Not bk Is Nothing Then GoTo goOn

Set bk = Workbooks("CombinedBatchStandardTransactions[5]")
If Not bk Is Nothing Then GoTo goOn

Exit Sub

goOn:

Call deleteMenu

Sheet1.Shapes("NL").Copy

Set MenuObject =
Application.CommandBars(1).Controls.Add(Type:=msoControlPopup, before:=10,
temporary:=True)
MenuObject.Caption = "&NavSol"
Set MenuItem = MenuObject.Controls.Add(Type:=msoControlButton)
MenuItem.OnAction = "mdlInv.formdaily"
MenuItem.Caption = "&DBE"
MenuItem.PasteFace

End Sub

Sub deleteMenu()
On Error Resume Next
Application.CommandBars(1).Controls("NavSol").Delete
On Error GoTo 0
End Sub

Is it not working because the file is not actually open yet? Can that be the
case?

Thanks again.

 

[Jim Thomlinson]

As we are dealing with an addin this is an entirely different matter. Your
addin has to respond to other files being opened. The events you are dealing
with are the events of the addin and not the events of the files being
opened. Check out this link on application level events...

http://www.cpearson.com/excel/AppEvent.aspx
--
HTH...

Jim Thomlinson

Thanks for the quick reply Jim,

I'll try your suggestion for the second part in a bit.

As for the first question, let me try to explain the problem in greater
detail.

My code is in an add-in that is loaded every time any excel file is open, so
naturally the toolbar gets created any time any file is open. What i want to
do is to have the toolbar created only when the file i need to work with is
opened, and do nothing if any other excel file is open.

Here's what i tried, but it didn't work. It just doesn't show the toolbar at
all.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call deleteMenu
End Sub

Sub workbook_open()

Dim bk As Workbook

On Error Resume Next
Set bk = Workbooks("CombinedBatchStandardTransactions[1]")
If Not bk Is Nothing Then GoTo goOn

Set bk = Workbooks("CombinedBatchStandardTransactions[2]")
If Not bk Is Nothing Then GoTo goOn

Set bk = Workbooks("CombinedBatchStandardTransactions[3]")
If Not bk Is Nothing Then GoTo goOn

Set bk = Workbooks("CombinedBatchStandardTransactions[4]")
If Not bk Is Nothing Then GoTo goOn

Set bk = Workbooks("CombinedBatchStandardTransactions[5]")
If Not bk Is Nothing Then GoTo goOn

Exit Sub

goOn:

Call deleteMenu

Sheet1.Shapes("NL").Copy

Set MenuObject =
Application.CommandBars(1).Controls.Add(Type:=msoControlPopup, before:=10,
temporary:=True)
MenuObject.Caption = "&NavSol"
Set MenuItem = MenuObject.Controls.Add(Type:=msoControlButton)
MenuItem.OnAction = "mdlInv.formdaily"
MenuItem.Caption = "&DBE"
MenuItem.PasteFace

End Sub

Sub deleteMenu()
On Error Resume Next
Application.CommandBars(1).Controls("NavSol").Delete
On Error GoTo 0
End Sub

Is it not working because the file is not actually open yet? Can that be the
case?

Thanks again.

With no code posted it is hard to comment, so I will just be very general.
When I create custom toolbars for a specific workbook I create one function
to create the toolbar and another oen to delete the toolbar. On open or on
workbook activeate I make a call to create the toolbar. On close or on
workbook deactiveate I make a call to delete the toolbar.

As for the second question
Workbooks.Count
will give you a count of the open workbooks. Careful with this though as you
may have a personal workbook open (where you are storing macros). You will
need to test to see if Personal.xls is open...