CommandBars vs CommandBars(1).Controls

[Sean]

Hi,

I am trying to get tooltips on my menu that I have added to the standard
Excel menu.
I have created my menu with Application.CommandBars(1).Controls. _
Add(Type:=msoControlPopup, before:=10, temporary:=True)
The .TooltipText="My tooltip" does not seem to work.

When the menu is created with:
Application.CommandBars.Add(Name:="myMenuBar", Position:=msoBarTop, _
MenuBar:=False)
The .TooltipText="My tooltip" does work.

Samples of two codes are below

What is the difference between
Application.CommandBars.Add
Application.CommandBars(1).Controls.Add

I have tried to read the Excel VBA help but am struggling to make sense of
it.
It seems that the one is adding another Command bar and the second is adding
more controls to an existing command bar.
Why does the .TooltipText work in the one and not the other?
I cannot find .TooltipText as a member of either the CommandBars or Controls
Class in the object browser.

---------------------------------------------------
'CommandBars(1).Controls. menu
Sub CreateMyMenuTest()
DeleteMyMenu 'Calls the Sub below
Dim M1 As CommandBarPopup
Set M1 = Application.CommandBars(1).Controls. _
Add(Type:=msoControlPopup, before:=10, temporary:=True)
M1.Caption = "&My Tools"
With M1.Controls.Add(Type:=msoControlButton)
.Caption = "&Test Macro"
.TooltipText = "My Test Macro tooltip"
.FaceId = 123
.BeginGroup = False
.OnAction = "TTest2"
End With
With M1.Controls.Add(Type:=msoControlButton)
.Caption = "&Delete Mennu"
.TooltipText = "Delete menu item"
.FaceId = 123
.BeginGroup = False
.OnAction = "DeleteMyMenuTest"
End With
End Sub

Sub TTest2()
'MsgBox ("SKB Test Macro")
MsgBox "Hello" & vbLf & "World"
End Sub

Sub DeleteMyMenuTest()
Dim MU As CommandBarPopup
On Error Resume Next
Set MU = Application.CommandBars(1).Controls("&My Tools")
MU.Delete
End Sub

---------------------------------------------------
'CommandBars. menu
Sub MakeMenuBar()
On Error Resume Next
Application.CommandBars("MyMenuBar").Delete
On Error GoTo 0

With Application.CommandBars.Add(Name:="myMenuBar", Position:=msoBarTop,
_
MenuBar:=False)

With .Controls.Add(Type:=msoControlButton)
.Style = msoButtonCaption
.Caption = "Click me"
.TooltipText = "your text 1"
.OnAction = "MenuBarMacro"
End With

With .Controls.Add(Type:=msoControlButton)
.Style = msoButtonCaption
.BeginGroup = True
.Caption = "Delete the MenuBar"
.TooltipText = "your text 2"
.OnAction = "DeleteMenuBar"
End With

.Visible = True
End With
End Sub

Sub MenuBarMacro()
MsgBox "Hi"
End Sub

Sub DeleteMenuBar()
On Error Resume Next
Application.CommandBars("MyMenuBar").Delete
On Error GoTo 0
End Sub

Any assistance will be appreciated.

Sean

 

[papou]

Hello Sean

Application.CommandBars.Add:
As you mentioned it, this syntax adds a new commandbar to the Excel
CommandBars collection
and
Application.CommandBars(1).Controls.Add
there again you are right in assuming that this syntax adds a new control in
an existing Commandbar (in this case it is the Worksheet Menu Bar)

The ToolTipText is a commandbar control property NOT a commandbar property
so this is why you cannot make it work with
Application.CommandBars.Add(Name:="myMenuBar", Position:=msoBarTop, _
MenuBar:=False)

HTH
Cordially
Pascal

 

[Bob Phillips]

This works for me

Set ctl = Application.CommandBars(1).Controls. _
Add(Type:=msoControlPopup, before:=10, temporary:=True)
With ctl
.Caption = "Test"
.TooltipText = "test"
End With


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Peter T]

Hi Sean,

You already have good answers from Pascal and Bob but re your other
comment -

I cannot find .TooltipText as a member of either the CommandBars or Controls
Class in the object browser.

It's not a property of CommandBars and "Controls" is a generic type of
object, so look at CommandBarPopup & CommandBarButton which are specific
types of controls.

Sub test()

Dim cmdBar As CommandBar
Dim cmdPopup As CommandBarPopup
Dim cmdButton As CommandBarButton

Set cmdBar = Application.CommandBars(1)

Set cmdPopup = cmdBar.Controls.Add(Type:=msoControlPopup)
cmdPopup.Caption = "Popup"
cmdPopup.TooltipText = "Tool tip"


Set cmdButton = cmdPopup.Controls.Add(Type:=msoControlButton)
With cmdButton
.Caption = "Button"
.TooltipText = "Tool tip" ' this won't show within a popup
.OnAction = "MyMacro"
.Style = msoButtonCaption
End With

Set cmdButton = cmdBar.Controls.Add(Type:=msoControlButton)
With cmdButton
.Caption = "Button"
.TooltipText = "Tool tip"
.OnAction = "MyMacro"
.Style = msoButtonCaption
End With

Stop

'press ctrl-v and look in locals

' use customise toolbars to manually delete these

End Sub

You don't need to do it this way but you can get the intellisense after
typing the dots.

Regards,
Peter T