Add items to VBA-created menu?

E

Ed

I managed to created a custom menu. But I can't find the Add for the new
control to add items under the new menu. Help?

Ed

Sub AddNewMenu()

Dim oCB As CommandBar
Set oCB = Application.CommandBars("Worksheet Menu Bar")
Dim newMenu As CommandBarControl

Set newMenu = oCB.Controls.Add(Type:=10)
newMenu.Caption = "MyMenu"
newMenu.Enabled = True

End Sub
 
B

Bob Phillips

Sub AddNewMenu()

Dim oCB As CommandBar
Set oCB = Application.CommandBars("Worksheet Menu Bar")
Dim newMenu As CommandBarControl

Set newMenu = oCB.Controls.Add(Type:=10)
With newMenu
.Caption = "MyMenu"
.Enabled = True
With .Controls.Add(Type:=msoControlButton)
.Caption = "Button1"
.FaceId = 29
.OnAction = "macro1"
End With
With .Controls.Add(Type:=msoControlButton)
.Caption = "Button2"
.FaceId = 29
.OnAction = "macro2"
End With
End With

End Sub


if you want button icons change the faceid property (John Walkenbach has a
utility to help you at http://j-walk.com/ss/excel/tips/tip67.htm to help
find the values of the FaceIds),

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
E

Ed

Thank you, Bob. That's got me going.

Ed

Bob Phillips said:
Sub AddNewMenu()

Dim oCB As CommandBar
Set oCB = Application.CommandBars("Worksheet Menu Bar")
Dim newMenu As CommandBarControl

Set newMenu = oCB.Controls.Add(Type:=10)
With newMenu
.Caption = "MyMenu"
.Enabled = True
With .Controls.Add(Type:=msoControlButton)
.Caption = "Button1"
.FaceId = 29
.OnAction = "macro1"
End With
With .Controls.Add(Type:=msoControlButton)
.Caption = "Button2"
.FaceId = 29
.OnAction = "macro2"
End With
End With

End Sub


if you want button icons change the faceid property (John Walkenbach has a
utility to help you at http://j-walk.com/ss/excel/tips/tip67.htm to help
find the values of the FaceIds),

--

HTH

RP
(remove nothere from the email address if mailing direct)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top