Macros and Toolbar

[JJ]

I've created a set of a subroutines for a specific Excel file and an
associated toolbar.

All of the subroutines contained within this Excel file or any copy of
this file. Is there any way to make it so the associated toolbar is
only visible (or available) when one of these files are opened?

I know you can attach a toolbar to a file, but the toolbar remains
available even if I have another file open.

Is there any way to make it so that a toolbar moves with the Excel
file?

Thanks!

 

[Bob Phillips]

You could generate the toolbar dynamically, and create it on workbook open
and workbook activate, and delete it on workbook close and workbook
deactivate.



Private Sub Workbook_Open()
Call CreateMenu
End Sub

Private Sub Workbook_Activate()
Call CreateMenu
End Sub

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

Private Sub Workbook_DeActivate()
Call DeleteMenu
End Sub

Private Sub CreateMenu()
Dim oCb As CommandBar
Dim oCtl As CommandBarPopup
Dim oCtlBtn As CommandBarButton

Set oCb = Application.CommandBars("Worksheet Menu Bar")
With oCb
Set oCtl = .Controls("Tools").Controls.Add( _
Type:=msoControlPopup, _
temporary:=True)
oCtl.Caption = "myButton"
With oCtl
Set oCtlBtn = .Controls.Add( _
Type:=msoControlButton, _
temporary:=True)
oCtlBtn.Caption = "myMacroButton"
oCtlBtn.FaceId = 161
oCtlBtn.OnAction = "myMacro"
End With
With oCtl
Set oCtlBtn = .Controls.Add( _
Type:=msoControlButton, _
temporary:=True)
oCtlBtn.Caption = "myMacroButton2"
oCtlBtn.FaceId = 161
oCtlBtn.OnAction = "myMacro2"
End With
'etc.
End With
End Sub

Private Sub DeleteMenu()

Dim oCb As CommandBar
On Error Resume Next
Set oCb = Application.CommandBars("Worksheet Menu Bar")
oCb.Controls("Tools").Controls("myButton").Delete
On Error GoTo 0
End Sub


'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)