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

[John Green[_2_]]

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



--

John Green - Excel MVP
Sydney
Australia


"dlookup" wrote in message ...
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.