Create/Remove toolbars

  • Thread starter Thread starter Steve Barnett
  • Start date Start date
S

Steve Barnett

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
 
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.
 
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)
 
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
 
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
 
Back
Top