Macros in a template run from Custom Toolbar attaching to wrong file.

L

Linda

I've created a Excel template with a number of macros to
run functions in this system. I've created a custom
toolbar to run a number of these macros. The problem
I've encountered is that the toolbar will become attached
to a workbook saved from this template and when the
template is opened next, the toolbar buttons cause the
old worksheet to open and the macros running from the
toolbar are in the old book and not the new one.

I've tried "attaching" the toolbar; opening and closing
the toolbar when the workbooks open and close; and
deleting the toolbar every time a workbook closes. None
of these things have solved the problem.

Does anyone have a fuller understanding of how to make a
custom toolbar run properly with templates and workbooks
created from this template?
 
S

steveB

Linda,

I find that having the workbook activate or workbook open macro (in the
ThisWorkbook module) avoids a lot of hassel. Try the below code.
When the toolbar only needs be used for one workbook out of many - the
activate and deactivate events work great...

..OnAction is the name of macros in the workbook.
'''''''''''''''''''''''''''''''
Private Sub Workbook_BeforeClose(Cancel As Boolean)
' destroy the toolbar before closing
On Error Resume Next
Application.CommandBars("myBar").Delete
On Error GoTo 0
End Sub

Private Sub Workbook_Open()
Dim oCBMenuBar As CommandBar
Dim oCBCLeave As CommandBarControl
Dim iMenu As Integer
Dim i As Integer

' remove toolbar if it exists
On Error Resume Next
Application.CommandBars("myBar").Delete
On Error GoTo 0

' build toolbar
Set oCBMenuBar = Application.CommandBars.Add(Name:="myBar")
With oCBMenuBar
With .Controls.Add(Type:=msoControlButton)
.BeginGroup = True
.Caption = "My Toolbar"
.Style = msoButtonCaption
End With
With .Controls.Add(Type:=msoControlButton)
.FaceId = 155
.TooltipText = "Previous month"
.OnAction = "prevMonth"
End With
With .Controls.Add(Type:=msoControlButton)
.FaceId = 156
.TooltipText = "Next month"
.OnAction = "nextMonth"
End With
With .Controls.Add(Type:=msoControlButton)
.FaceId = 157
.TooltipText = "Last month"
.OnAction = "lastMonth"
End With
With .Controls.Add(Type:=msoControlButton)
.BeginGroup = True
.Caption = "Summary"
.Style = msoButtonCaption
.TooltipText = "Show summary sheet"
.OnAction = "gotoSummary"
End With
.Position = msoBarLeft
.Protection = msoBarNoMove
.Visible = True
End With
End Sub
'''''''''''''''''''''''''''''''''''''
 
G

Greg Koppel

Assign and unassign the macros when the workbook is opened or closed.

Sub Auto_open()
Toolbars("LogForm").Visible = True
Toolbars("LogForm").ToolbarButtons(1).OnAction = "PhoneLogger"
Toolbars("LogForm").ToolbarButtons(2).OnAction = "LateLogger"
End Sub
Sub Auto_close()
On Error Resume Next
Toolbars("LogForm").ToolbarButtons(1).OnAction = ""
Toolbars("LogForm").ToolbarButtons(2).OnAction = ""
Toolbars("LogForm").Visible = False
Toolbars("LogForm").Delete
On Error GoTo 0
End Sub

HTH, Greg
 
Top