Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macros Referenced in Custom Menu Fail after Save
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macros Referenced in Custom Menu Fail after Save
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(msoControl Popup) .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 On Apr 12, 3:54 pm, "Kevin M" wrote: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macros Referenced in Custom Menu Fail after Save
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 wrote: 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(msoControl Popup) .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 On Apr 12, 3:54 pm, "Kevin M" wrote: 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. -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macros Referenced in Custom Menu Fail after Save
On Apr 17, 3:22 pm, Dave Peterson wrote:
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 wrote: 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(msoControl Popup) .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 On Apr 12, 3:54 pm, "Kevin M" wrote: 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. -- Dave Peterson 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macros Referenced in Custom Menu Fail after Save
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: <<snipped 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macros Referenced in Custom Menu Fail after Save
On Apr 18, 2:43 pm, Dave Peterson wrote:
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: <<snipped 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 The spaces are automatically replaced with the appropriate characters. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macros Referenced in Custom Menu Fail after Save
Then it proves I wasn't lying with:
I have no idea how this would work with those HTTP file names. Good luck. Kevin M wrote: <<snipped The spaces are automatically replaced with the appropriate characters. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Custom Menu with Macro doesn't save. | Excel Discussion (Misc queries) | |||
custom menu items not responding after initial save | Excel Programming | |||
How do i save a custom footer in the excel drop down menu? | Excel Discussion (Misc queries) | |||
Save original workbook name, referenced by macros in other books? | Excel Programming | |||
Editing Custom Worksheet Menu Bar in XL97 & Associtated Macros | Excel Programming |