Workbook Menu screen tips?

[Sean]

Hi,

When you hover over a toolbar button a screen tip (yellow box that appears
when you hover over a command button) is visible.

Is it possible to get this to happen on custom worksheet menus?

Can you get something to appear on the status bar when you hover over the
menu item?

Any comments will be appreciated.

Sean

 

[Ron de Bruin]

Hi Sean

If you create the button with code you can use the Caption

With .Controls.Add(Type:=msoControlButton)
.Caption = "Delete the ToolBar"
.FaceId = 72
.OnAction = "DeleteToolBar"
End With

See

http://support.microsoft.com/default.aspx?scid=kb;en-us;830502&Product=xlw
How to customize menus and menu bars in Excel

http://www.j-walk.com/ss/excel/tips/tip53.htm
Creating Custom Menus (John Walkenbach) TIP

http://www.erlandsendata.no/english/index.php?d=endownloadcommandbars
Ole P. Erlandsen's Web Site (Example workbooks)

 

[Sean]

Thanks Ron,

I am trying to find out if there is a similar thing for a Menu item, not a
button.

Sean

 

[Ron de Bruin]

Hi Sean

Sorry, use
.TooltipText = "your text"

 

[Sean]

Ron,

Thanks again, but these are all for toolbars and not menu's. Is there a
similar command for menus?

Sean

 

[Ron de Bruin]

Hi Hi Sean

It is working if you add a new menubar but not for a menu item you add to a original menu
Never try this. When I have time I see if it is possible.

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

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

With .Controls.Add(Type:=msoControlPopup)
.Caption = "Click me"
.TooltipText = "your text 1"
.OnAction = "MenuBarMacro"
End With
With .Controls.Add(Type:=msoControlPopup)
.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

 

[Sean]

Hi,

Mmm, it seems to work on your menu but not on mine (Code below). Your menu
seems to crash my Excel when I select the delete menu item.
Even when I add the tooltip thing to my menu, the tooltip does not appear
when I hover over the menu.

I see that I am using a (Type:=msoControlButton)
and you are using a (Type:=msoControlPopup)
I am not sure what the differences. When I use the (Type:=msoControlPopup)
on my menu I do not get a chance to click a menu item as when I hover over
the item the macro is executed without me clicking on it.

Sub CreateMyMenu()
DeleteMyMenu 'Calls the Sub below
Dim M1 As CommandBarPopup
Dim M2 As CommandBarPopup
Set M1 = Application.CommandBars(1).Controls. _
Add(Type:=msoControlPopup, before:=10, temporary:=True)
M1.Caption = "&My Tools"
'*****************************
'Run Macro Test
'*****************************
With M1.Controls.Add(Type:=msoControlButton)
.Caption = "&Test Macro"
.TooltipText = "My Test Macro tooltip"
.FaceId = 123
.BeginGroup = False
.OnAction = "TTest"
End With
End Sub

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

 

[Ron de Bruin]

Hi Sean

I not test it good

seems to crash my Excel when I select the delete menu item.

Me to

I test more for you today but must go now

--
Regards Ron de Bruin
http://www.rondebruin.nl

Hi,

Mmm, it seems to work on your menu but not on mine (Code below). Your menu seems to crash my Excel when I select the delete menu
item.
Even when I add the tooltip thing to my menu, the tooltip does not appear when I hover over the menu.

I see that I am using a (Type:=msoControlButton)
and you are using a (Type:=msoControlPopup)
I am not sure what the differences. When I use the (Type:=msoControlPopup) on my menu I do not get a chance to click a menu item
as when I hover over the item the macro is executed without me clicking on it.

Sub CreateMyMenu()
DeleteMyMenu 'Calls the Sub below
Dim M1 As CommandBarPopup
Dim M2 As CommandBarPopup
Set M1 = Application.CommandBars(1).Controls. _
Add(Type:=msoControlPopup, before:=10, temporary:=True)
M1.Caption = "&My Tools"
'*****************************
'Run Macro Test
'*****************************
With M1.Controls.Add(Type:=msoControlButton)
.Caption = "&Test Macro"
.TooltipText = "My Test Macro tooltip"
.FaceId = 123
.BeginGroup = False
.OnAction = "TTest"
End With
End Sub

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

 

[Ron de Bruin]

This is working Sean for me

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

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

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



--
Regards Ron de Bruin
http://www.rondebruin.nl

Hi Sean

I not test it good

seems to crash my Excel when I select the delete menu item.

Me to

I test more for you today but must go now

 

[Sean]

Ron,

Thanks for all the help, it is really appreciated.

Your menu does have the tooltips, mine does not. Once difference I can see
is that your menu is a separate menu and mine has been added to the main
excel menu. Any ideas as to how I can get a menu item added to the Excel
menu to have tooltips?

Sean

 

[Ron de Bruin]

Hi Sean

I try to help you tomorrow