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

[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?

 

[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
'''''''''''''''''''''''''''''''''''''

 

[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

 

[Linda]

Steve B,
Thank you for this info. I must digest this code before
I try to use it. But just one question. If a user has
saved any number of files from my template and then has
more than one of them open at a time, will the tool bar
that appears work in the Current Workbook only, or how
will the toolbar buttons know which book's macros it's
running.

Do you know if there is any way to create something like
a toolbar (perhaps a "form"?) within a workbook that
would basically sit on top of each sheet as you move from
sheet to sheet? Then it would be saved in that workbook
alone, not in the .xlb file for all excel workbooks.

I guess your code would actually cause the toolbar to be
deleted before you can exit Excel and then it doesn't get
saved in the .xlb file, right?

Thanks again for your help.

Linda

 

[Linda]

Thank you for your help. I've had a similar answer from
SteveB, but had some additional concerns about this
method. If you see what I wrote in reponse to SteveB, I
wonder what you would think.

Thanks again.

Linda