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
"Graff" wrote:
> 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" wrote:
>
> > 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...
> >
> > --
> > HTH...
> >
> > Jim Thomlinson
> >
> >
> > "Graff" wrote:
> >
> > > 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
Similar Threads
