Macros Referenced in Custom Menu Fail after Save

[Kevin M]

Hi all,

I'm working on a macro-loaded Workbook (that's shared via a SharePoint
site), and every time I open the workbook, make some changes, then do
a simple save (Ctrl+S, or File->Save), it works fine, but any macro I
try to run from a list on the Menu bar
(CommandBars(1).Controls("&Custom Macros)....) fails, saying that it
can't find the macro. The error message references the exact http
address of the file, and the name has not changed (it's a save, not a
save as).

I've searched this and other NG's furiously, and have only run into
*similar* situations in which the person was told to add add and
delete code to AfterOpen and BeforeClose events, which I've already
done. This is a totally unique problem, and, seeing as there's no
_clean_ AfterSave method, I'm in a bit of a bind. I'm still relatively
new to VBA -- learned it out of necessity for work.

Any help getting this menu to re-generate itself after a save or save
as (cleanly!) would be fantastic.

Many thanks in advance.

 

[Kevin M]

I'll supply some of the code:

Sub Workbook_Open()
Call MakeMenu
End Sub

Sub MakeMenu()
Dim i As Integer
Dim arr1 As Variant, arr2 As Variant, arr3 As Variant

arr1 = Array("FitMergedCellHACK", "SelectForm", "SpecialSort")
arr2 = Array("Adjust Row Heights", "Send E-mail!", "Special Sort")
arr3 = Array(541, 24, 210)
With Application.CommandBars(1).Controls.Add(msoControlPopup)
.Caption = "&Custom Macros"
.TooltipText = "Select a macro from the list"
For i = 0 To 2
With .Controls.Add
.OnAction = arr1(i)
.Caption = arr2(i)
.FaceId = arr3(i)
End With
Next
End With
End Sub

 

[Dave Peterson]

I think I'd just rerun the workbook_open event so that the macros point to the
correct workbook.

I would use something like this in the .onaction line:

..OnAction = "'" & thisworkbook.name & "'!" & arr1(i)

and I'd also delete the older option:

On error resume next
Application.CommandBars(1).Controls("&Custom Macros").delete
on error goto 0

Before adding the new option.

(I'd stick it at the top of the MakeMenu routine.)

 

[Kevin M]

I think I'd just rerun the workbook_open event so that the macros point to the
correct workbook.

I would use something like this in the .onaction line:

.OnAction = "'" & thisworkbook.name & "'!" & arr1(i)

and I'd also delete the older option:

On error resume next
Application.CommandBars(1).Controls("&Custom Macros").delete
on error goto 0

Before adding the new option.

(I'd stick it at the top of the MakeMenu routine.)

Ugh.

I tried exactly what you said, Dave, and I'm still getting these pesky
errors. It really makes no sense to me how Excel can say "http://
xxx.yyy.com/foo/bar.xls!SelectForm" or whatever isn't working, when
the path and everything is perfect. Does the fact that there are
spaces (%20) in the name of the hosted file screw things up?

Many thanks for all your help.

 

[Dave Peterson]

I have no idea how this would work with those HTTP file names.

But if there are spaces, couldn't you try replacing them with %20's. (You'll
have to test it, though.)

 

[Kevin M]

I have no idea how this would work with those HTTP file names.

But if there are spaces, couldn't you try replacing them with %20's. (You'll
have to test it, though.)



Kevin M wrote:

The spaces are automatically replaced with the appropriate characters.

 

[Dave Peterson]

Then it proves I wasn't lying with:

I have no idea how this would work with those HTTP file names.

Good luck.