how do i add dropdown menus to a menu bar?
 

Hi

I have added a menu bar with buttons which run code when clicked.
What i want to do is add dropdown menus which have their own sub
'buttons' on them. Just like when you click the File on the standard
menu in Excel, and it drops down and shows New, Open etc.

My current code is (i am only showing the code for a couple of the
buttons I am adding):

PerformCommandBar As CommandBar
Set PerformCommandBar = Application.CommandBars.Add(ToolbarName,
msoBarFloating, False, True)
PerformCommandBar.Visible = True
Set NewItem = PerformCommandBar.Controls.Add(msoControlButton)
With NewItem
.Caption = "Delete Data"
.TooltipText = "Delete all the data sheets"
.OnAction = "DeleteDataSheets"
.Style = msoButtonCaption
.Visible = True
End With
Set NewItem = PerformCommandBar.Controls.Add(msoControlButton)
With NewItem
.Caption = "Text to Formulas"
.TooltipText = "Change the formulas in the Calc sheet back
from text"
.OnAction = "Copy_Text_to_Formulas"
.Style = msoButtonCaption
.Visible = True
End With


All help appreciated

AndyC

how do i add dropdown menus to a menu bar?
 

Hi

This should get you started:

APPNAME=ToolbarName
On Error Resume Next
Application.CommandBars(APPNAME).Delete

'Create MenuBar
Set MyMenuBar = Application.CommandBars.Add(APPNAME,
Position:=msoBarTop, temporary:=False)
MyMenuBar.Visible = True

' Create a popup control
Set cbPop = Application.CommandBars(APPNAME).Controls.Add
(Type:=msoControlPopup)
cbPop.Caption = APPNAME
cbPop.Visible = True

'------------------------------------------------
' Sub Menu
'------------------------------------------------

Set cbCtl = cbPop.Controls.Add(Type:=msoControlButton)
With cbCtl
.Visible = True
.Style = msoButtonCaption
.Caption = "&Delete Data"
.OnAction = "DeleteDataSheets"
End With

Best regards,
Per

On 21 Jun., 00:57, AC wrote:
Hi

I have added a menu bar with buttons which run code when clicked.
What i want to do is add dropdown menus which have their own sub
'buttons' on them. *Just like when you click the File on the standard
menu in Excel, and it drops down and shows New, Open etc.

My current code is (i am only showing the code for a couple of the
buttons I am adding):

PerformCommandBar As CommandBar
Set PerformCommandBar = Application.CommandBars.Add(ToolbarName,
msoBarFloating, False, True)
* * * * PerformCommandBar.Visible = True
Set NewItem = PerformCommandBar.Controls.Add(msoControlButton)
* * * * With NewItem
* * * * * * .Caption = "Delete Data"
* * * * * * .TooltipText = "Delete all the data sheets"
* * * * * * .OnAction = "DeleteDataSheets"
* * * * * * .Style = msoButtonCaption
* * * * * * .Visible = True
* * * * End With
* * * * Set NewItem = PerformCommandBar.Controls.Add(msoControlButton)
* * * * With NewItem
* * * * * * .Caption = "Text to Formulas"
* * * * * * .TooltipText = "Change the formulas in the Calc sheet back
from text"
* * * * * * .OnAction = "Copy_Text_to_Formulas"
* * * * * * .Style = msoButtonCaption
* * * * * * .Visible = True
* * * * End With

All help appreciated

AndyC