Having to re-assign macro to menu item

[thomasks]

I have a custom menu that is loaded when the sheet is opened in excel. The menu items are assigned to macros in VBA, and the master worksheet is stored on a network drive.
The problem I am having, is that every time you open the workbook from the network drive the macros in the menu items don't work.
When you select the menu item to run it, excel says it cannot find the macro. If I go to "customize" then "rearrange commands" and select the menu, then "assign macro" and reassign them, they work fine until I open the workbook again, and it can't find them. What am I doing wrong?!

 

[N1GHTEYES]

I've had a similar problem with an add-in which includes macros called by the buttons of an associated commandbar. You may not have exactly the same problem, but with mine the issue was that Excel seems to want to assign absolute paths to the macros. If each user has his own copy of the add-in, when they open the shared spreadsheet, the button macros will point to the wrong location - i.e. the directory appropriate to the last user who "fixed" them.

I got round this by adding a routine to the Workbook_Open event for the add-in. It goes through all the buttons on the commandbar in question and modifies the .OnAction parameter by deleting everything up to, and including, the !. This strips any path and leaves just the name of the procedure.

 

[CBasicAsslember]

You should create on opening and delete on closing the custom menu item. If you don't you will have it appear on every spread sheet. That might annoy some users.

N1GHTEYES is correct about the absolute path.

 

[thomasks]

Thanks Nighteyes.
Could you possibly post the code for this procedure for me?
It would help to see exactly what you did.

 

[thomasks]

Yes, I am creating on open and deleting on close, just have the problem with the macro not found error. It keeps putting the spreadsheet name with ! before the macro instead of Sheet2.cmdTotal for example.

 

[CBasicAsslember]

thomsaks,

Could you post the code?

 

[N1GHTEYES]

Here is what I use. As I said, your need may be slightly different from mine, but here it is anyway.

Private Sub Workbook_Open()
'This procedure ensures that the buttons on the AL_XL toolbar
'point to the macros in the running instance of AL_XL_Tools addin,
'and not to a spurious fixed path version.

Dim macroname As String
Dim bangpos   As Integer
Dim btn       As CommandBarButton
On Error Resume Next
With Application.CommandBars("AL_XL")
    For Each btn In .Controls
        macroname = btn.OnAction
        bangpos = InStr(1, macroname, "!")
        If bangpos > 0 Then
            btn.OnAction = Mid(macroname, bangpos + 1, Len(macroname) - bangpos)
        End If
        If Err.Number <> 0 Then Err.Clear
    Next btn
    .Enabled = True
    .Visible = True
End With
End Sub

Note that I set the enabled and visible properties of the toolbar to true. That is because I know the user will want the toolbar visible if he has this addin loaded. You might want to play things differently.

I hope it helps,

Tony

 

[thomasks]

Thanks everyone, I found that I had to add to the
.on action:
"Sheet2.cmdtotal"
versus just:
"cmdtotal"