jwise
11-02-2007, 07:23 AM
This is an enhancement to a relatively large application. I have reduced the problem to this simple code. I tried to use the "macro recorder" to see how to do this, but it did not do a good job. I've tried everything else I knew about...
The code builds the added toolbar entry (this goes on the same line with "File Edit ... Help". It adds the commandbar, and it works. It is "blank", i.e. the title should be "Maint" and there is nothing there.
I found some code on www.ozgrid.com (http://www.ozgrid.com) which built a menu this way, except their code had a sub-menu with three possible actions. There is only 1 level here: to run the Sub "cmdRestart". In the real world, all of this is done in Workbook open and close events because I want this added button to be built when the workbook is opened, and deleted when the workbook is closed. All of that works.
The problem is there is no label on the toolbar. You can see the "hole" where it belongs. Here is the code:
Sub AddMenus2()
Dim cMenu1 As CommandBarControl
Dim cbMainMenuBar As CommandBar
Dim iHelpMenu As Integer
Dim cbcCustomMenu As CommandBarControl
On Error Resume Next
Application.CommandBars("Worksheet Menu Bar").Controls("&Maint").Delete
On Error GoTo 0
Set cbMainMenuBar = _
Application.CommandBars("Worksheet Menu Bar")
iHelpMenu = _
cbMainMenuBar.Controls("Help").Index
Set cbcCustomMenu = _
cbMainMenuBar.Controls.Add(Type:=msoControlButton, _
Before:=iHelpMenu)
With cbcCustomMenu
.Caption = "Maint"
.OnAction = "cmdRestart"
End With
End Sub
Sub cmdRestart()
MsgBox ("cmdRestart has been entered!")
End Sub
TIA
The code builds the added toolbar entry (this goes on the same line with "File Edit ... Help". It adds the commandbar, and it works. It is "blank", i.e. the title should be "Maint" and there is nothing there.
I found some code on www.ozgrid.com (http://www.ozgrid.com) which built a menu this way, except their code had a sub-menu with three possible actions. There is only 1 level here: to run the Sub "cmdRestart". In the real world, all of this is done in Workbook open and close events because I want this added button to be built when the workbook is opened, and deleted when the workbook is closed. All of that works.
The problem is there is no label on the toolbar. You can see the "hole" where it belongs. Here is the code:
Sub AddMenus2()
Dim cMenu1 As CommandBarControl
Dim cbMainMenuBar As CommandBar
Dim iHelpMenu As Integer
Dim cbcCustomMenu As CommandBarControl
On Error Resume Next
Application.CommandBars("Worksheet Menu Bar").Controls("&Maint").Delete
On Error GoTo 0
Set cbMainMenuBar = _
Application.CommandBars("Worksheet Menu Bar")
iHelpMenu = _
cbMainMenuBar.Controls("Help").Index
Set cbcCustomMenu = _
cbMainMenuBar.Controls.Add(Type:=msoControlButton, _
Before:=iHelpMenu)
With cbcCustomMenu
.Caption = "Maint"
.OnAction = "cmdRestart"
End With
End Sub
Sub cmdRestart()
MsgBox ("cmdRestart has been entered!")
End Sub
TIA