PDA

View Full Version : Solved: Toolbar has no label



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

paulked
11-02-2007, 07:46 AM
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
.Style = msoButtonCaption
.Caption = "Maint"
.OnAction = "cmdRestart"
End With

End Sub

Add in the .Style = msoButtonCaption as above.

Best regards

Paul Ked

jwise
11-02-2007, 07:57 AM
Thanks Paul Ked. It now works!

I am curious how you knew to add that statement. What should I have looked for in the help system?

paulked
11-02-2007, 09:58 AM
You're welcome.

To find it I went into Customize and then right-clicked on the blank space. This gave the option of Text Only (Always) under the Default Style. I then guessed that Style would be the key factor and so looked it up in the Object Brouser and... Voila!

A bit of luck really as I'm not that well versed with VBA :blush

Best regards

Paul Ked

Aussiebear
11-03-2007, 05:19 AM
G'day Jwise,

You should give some consideration to downloading menumaker from J Walk's website.

jwise
11-05-2007, 12:45 PM
Thanks for the explanation Paul and the suggestion Aussiebear. I will study this information. I really appreciate your assistance,