Managing menus via VBA

[mddawson - ExcelForums.com]

Does anyone know if there is a way to add items to a submenu in VBA. I
have a VBA program that manipulates data for creating data sets to be
analyzed in SAS and a neural network package. The data processing
commands for adding lags to the data set, splitting the data set into
training and verification data sets and importing the results
predicted by SAS and the neural network are currently in a custom
menu that I added. As these commands are essentially data commands I
am shifting them to the Data menu that already exists in Excel.

Adding the commands to the bottom of the Data menu is a no-brainer; I
even figured out how to add a divider bar. The thing is that two of
my custom commands are import functions and it makes more sense to
add these two commands to the Data > Import External Data submenu.
VBA help provides no information for doing this. I also tried the
common sense approach:

..Menus("Data")[color=blue:b69d32f640].MenuItems[/color:b69d32f640]("Import
External Data")[color=blue:b69d32f640].Add[/color:b69d32f640] _
[color=blue:b69d32f640]Caption[/color:b69d32f640]:="Import SAS®
Predictions...", _
[color=blue:b69d32f640]OnAction[/color:b69d32f640]:="ReadSASDataSet"

The VBA compiler sees no problem with this code, but when it is
executed the addition of a specific menu item caused the program to
halt with an unsupported property or method error.

Any assistance that can be provided would be greatly appreciated.

 

[Jim Rech]

You're using the Menus method that has been hidden since Excel 97. While it
can be make to work I suppose I'd suggest using the preferred Commandbars
method.

Sub AddSASItem()
Dim NewCtrl As CommandBarButton
Set NewCtrl = CommandBars("Worksheet Menu Bar") _
.Controls("Data").Controls("Import External Data") _
.Controls.Add
NewCtrl.Caption = "Import SAS® Predictions..."
NewCtrl.OnAction = "ReadSASDataSet"
End Sub


--
Jim
"mddawson - ExcelForums.com" <[email protected]>
wrote in message | Does anyone know if there is a way to add items to a submenu in VBA. I
| have a VBA program that manipulates data for creating data sets to be
| analyzed in SAS and a neural network package. The data processing
| commands for adding lags to the data set, splitting the data set into
| training and verification data sets and importing the results
| predicted by SAS and the neural network are currently in a custom
| menu that I added. As these commands are essentially data commands I
| am shifting them to the Data menu that already exists in Excel.
|
| Adding the commands to the bottom of the Data menu is a no-brainer; I
| even figured out how to add a divider bar. The thing is that two of
| my custom commands are import functions and it makes more sense to
| add these two commands to the Data > Import External Data submenu.
| VBA help provides no information for doing this. I also tried the
| common sense approach:
|
| Menus("Data")[color=blue:b69d32f640].MenuItems[/color:b69d32f640]("Import
| External Data")[color=blue:b69d32f640].Add[/color:b69d32f640] _
| [color=blue:b69d32f640]Caption[/color:b69d32f640]:="Import SAS®
| Predictions...", _
| [color=blue:b69d32f640]OnAction[/color:b69d32f640]:="ReadSASDataSet"
|
| The VBA compiler sees no problem with this code, but when it is
| executed the addition of a specific menu item caused the program to
| halt with an unsupported property or method error.
|
| Any assistance that can be provided would be greatly appreciated.
|

 

[mddawson - ExcelForums.com]

Thanks Jim. The Menubars technique is what I learned from my faculty
advisor two years ago so I will have to let him know that we should
be using CommandBars instead. Excel does provide help text on the
MenuBars command (we have Office 2002), but there is nothing about
accesing the submenu of a menubar menu. I will try your suggestion
out and let you know how it goes.

 

[mddawson - ExcelForums.com]

I tried the CommandBars technique and it worked well. It is more code
intensive than the MenuBars method, but it does allow access to
submenus. Another difference is that the CommandBars property does
not recognize the caption “-” as a group separator. If I indicate a
hyphen as the caption using the MenuBars property I get the
horizontal rule (group separator) in the menu bar, but with
CommandBars it just appears as a hyphen. How do I get the group
separator using the CommandBars property?