I have an application that adds a toolbar as part of it's auto_open
processing. During auto_close, it removes the toolbar.

The problem comes if I open two spreadsheets with this code in. The second
one detects that the toolbar is already there, so does not try to load it a
second time. However, if I close one of the sheets, the auto_close fires and
the toolbar is taken away. This leaves the first sheet sitting there without
it's tool bar.

Is there any way of maintaining a reference count such that I only delete
the toolbar when it's no longer needed?

Thanks
Steve

That might be one way, but would be hard to maintain. If the workbooks
that require the macro have some unique attribute you can look for, then
have your auto_close code search the open workbooks to see if there are any
others open that require the menu.

--
Regards,
Tom Ogilvy

"Steve Barnett" wrote in message
...
I have an application that adds a toolbar as part of it's auto_open
processing. During auto_close, it removes the toolbar.

The problem comes if I open two spreadsheets with this code in. The second
one detects that the toolbar is already there, so does not try to load it
a
second time. However, if I close one of the sheets, the auto_close fires
and
the toolbar is taken away. This leaves the first sheet sitting there
without
it's tool bar.

Is there any way of maintaining a reference count such that I only delete
the toolbar when it's no longer needed?

Thanks
Steve

I've added code to the auto_close to count "Form Options" worksheets; one of
the sheets that I have in my app. If I find more than one sheet with this
name, then I leave the toolbar alone. If there is only one, I dispose of the
toolbar.

Seems to be working Ok at the moment.

I may go back to the workbook_activate events later. I had tried them, but
they didn't seem to fire when I closed a workbook. For example, if I had two
books open (A & B) then closing A didn't fire the activate event in B. It
might have been my code, but I don't think so.

Many thanks
Steve


"Tom Ogilvy" wrote in message
...
That might be one way, but would be hard to maintain. If the workbooks
that require the macro have some unique attribute you can look for, then
have your auto_close code search the open workbooks to see if there are
any
others open that require the menu.

--
Regards,
Tom Ogilvy

"Steve Barnett" wrote in message
...
I have an application that adds a toolbar as part of it's auto_open
processing. During auto_close, it removes the toolbar.

The problem comes if I open two spreadsheets with this code in. The
second
one detects that the toolbar is already there, so does not try to load
it
a
second time. However, if I close one of the sheets, the auto_close fires
and
the toolbar is taken away. This leaves the first sheet sitting there
without
it's tool bar.

Is there any way of maintaining a reference count such that I only
delete
the toolbar when it's no longer needed?

Thanks
Steve

Hi Steve

Maybe this is a option for you
You can use the Activate event and the deactivate event to create and delete your toolbars

Private Sub Workbook_Activate()

End Sub

Private Sub Workbook_Deactivate()

End Sub

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"Steve Barnett" wrote in message ...
I have an application that adds a toolbar as part of it's auto_open
processing. During auto_close, it removes the toolbar.

The problem comes if I open two spreadsheets with this code in. The second
one detects that the toolbar is already there, so does not try to load it a
second time. However, if I close one of the sheets, the auto_close fires and
the toolbar is taken away. This leaves the first sheet sitting there without
it's tool bar.

Is there any way of maintaining a reference count such that I only delete
the toolbar when it's no longer needed?

Thanks
Steve

Why not just check if the other workbook is also open. Something like

Dim oWb As Workbook

On Error Resume Next
If ThisWorkbook.Name = "Workbook A" Then
Set oWb = Workbooks("Workbook B")
Else
Set oWb = Workbooks("Workbook A")
End If
On Error GoTo 0

If oWb Is Nothing Then
DeleteToolbar
End If


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Steve Barnett" wrote in message
...
I have an application that adds a toolbar as part of it's auto_open
processing. During auto_close, it removes the toolbar.

The problem comes if I open two spreadsheets with this code in. The second
one detects that the toolbar is already there, so does not try to load it
a
second time. However, if I close one of the sheets, the auto_close fires
and
the toolbar is taken away. This leaves the first sheet sitting there
without
it's tool bar.

Is there any way of maintaining a reference count such that I only delete
the toolbar when it's no longer needed?

Thanks
Steve

I can't predict how many workbooks the user will open, or their names. What
I've done is scan Excel for all worksheet names and count those with a
specific name that matches my application. It's not brilliant, but seems to
be working.

Thanks
Steve


"Bob Phillips" wrote in message
...
Why not just check if the other workbook is also open. Something like

Dim oWb As Workbook

On Error Resume Next
If ThisWorkbook.Name = "Workbook A" Then
Set oWb = Workbooks("Workbook B")
Else
Set oWb = Workbooks("Workbook A")
End If
On Error GoTo 0

If oWb Is Nothing Then
DeleteToolbar
End If


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Steve Barnett" wrote in message
...
I have an application that adds a toolbar as part of it's auto_open
processing. During auto_close, it removes the toolbar.

The problem comes if I open two spreadsheets with this code in. The
second
one detects that the toolbar is already there, so does not try to load
it
a
second time. However, if I close one of the sheets, the auto_close fires
and
the toolbar is taken away. This leaves the first sheet sitting there
without
it's tool bar.

Is there any way of maintaining a reference count such that I only
delete
the toolbar when it's no longer needed?

Thanks
Steve