Custom Toolbar (Macro path changes)

  • Thread starter Thread starter dlookup
  • Start date Start date
D

dlookup

I'm using Excel 2000
I assinged a custom macro to a custom button on a custom
toolbar in Excel and attach the toolbar to the workbook.
But when I copy the workbook to a network location the
macro's path is changed. So instead of the path
being "Macro1" it changes to "C:\MyWorkbook!Macro1" When
the button is pressed we get an error message
that "MyWorkbook is already open" because it tries to re-
open the workbook. Manually changing the path back
to "Macro1" works, but not possible for network
distribution. I've tried everything to stop this. Even
Microsoft is ignoring me.
 
There are several issues to be aware of when distributing a workbook with an attached toolbar.

Firstly, you can't update an older version of your toolbar by simply sending them a new workbook. If your users open a new version
of the workbook with an updated toolbar attached, it will not replace the toolbar that was placed in their version of Excel by a
previous version of the workbook.

Secondly, the location of the macros referred to on the toolbar might not be what you want or expect, as you have discovered.

I do not rely on attached toolbars. I use code that checks to see if the toolbar exists. If it doesn't, I create the toolbar using
code. If the toolbar does exist, I check that the buttons refer to the correct macros in the desired location.

Here is a sample of my code:

Private Sub Workbook_Open()
Dim cbList As CommandBar
Dim lngLeft As Long
Dim lngTop As Long
Dim lngPosition As Long
Dim i As Integer

On Error Resume Next
Set cbList = Application.CommandBars("PAMOptions")
On Error GoTo 0
If cbList Is Nothing Then
Set cbList = Application.CommandBars.Add(Name:="PAMOptions")
For i = 1 To 2
cbList.Controls.Add Type:=msoControlButton
Next i
End If
With cbList.Controls(1)
.OnAction = "ImportOpeningPositions"
.FaceId = 270
.TooltipText = "Read Opening Positions from PAML.txt in A: Drive"
End With
With cbList.Controls(2)
.OnAction = "TradesReport"
.FaceId = 195
.TooltipText = "Generate Option Bookings Sheet"
End With
cbList.Enabled = True
cbList.Visible = True
End Sub
 
Back
Top