Excel - Menu

Ayuda Foro…..!

este codigo genera un menu el cual se representa asi:

MENU ELIMINAR MENU
Submenu1 2007--Submenu2 2007--1 Submenu Item1 2007



Option Explicit

Sub CreateMenu()

Dim cb As CommandBar, cbMenu As CommandBarControl, cbSubMenu As CommandBarControl

DeleteCommandBar ' delete the custom menu if it already exists

Set cb = Application.CommandBars.Add("MyCommandBarName", msoBarTop, True, True)

Set cbMenu = cb.Controls.Add(msoControlPopup, , , , True)

With cbMenu
.Caption = "&Menu"
.Tag = "MyTag"
.BeginGroup = True
End With
If cbMenu Is Nothing Then Exit Sub

'''''''''' AÑO 2007 ''''''''''''''''''''''''''''''''''''''''''''''''
Set cbSubMenu = cbMenu.Controls.Add(msoControlPopup, 1, , , True)
With cbSubMenu
.Caption = "&Submenu1 2007"
.Tag = "SubMenu1"
.BeginGroup = True
End With

Set cbSubMenu = cbSubMenu.Controls.Add(msoControlPopup, 1, , , True)
With cbSubMenu
.Caption = "&Submenu2 2007"
.Tag = "SubMenu2"
.BeginGroup = True
End With

With cbSubMenu.Controls.Add(msoControlButton, 1, , , True)
.Caption = "&Submenu Item1 2007"
.OnAction = ThisWorkbook.Name & "!Macroname"
.Style = msoButtonIconAndCaption
.FaceId = 71
.State = msoButtonDown
End With


''''''''' FIN AÑO 2007 ''''''''''''''''''''''''''''''''''''''''''''''''

''''''''' AÑO 2008 ''''''''''''''''''''''''''''''''''''''''''''''''
Pero al agregar este otro codigo para el grupo siguiente Año 2008 me lo representa asi:

MENU ELIMINAR MENU
Submenu1 2007--Submenu2 2007--1 Submenu Item1 2007
Submenu3 2008--1 Submenu Item1 2008

Set cbSubMenu = cbSubMenu.Controls.Add(msoControlPopup, 1, , , True)
With cbSubMenu
.Caption = "&Submenu3 2008"
.Tag = "SubMenu2"
.BeginGroup = True
End With

With cbSubMenu.Controls.Add(msoControlButton, 1, , , True)
.Caption = "&Submenu Item1 2008"
.OnAction = ThisWorkbook.Name & "!Macroname"
.Style = msoButtonIconAndCaption
.FaceId = 71
.State = msoButtonDown
End With
''''''''' FIN AÑO 2008 ''''''''''''''''''''''''''''''''''''''''''''''''

alguien del foro me puede dicir como le hago para que me quede de esta forma agrdecere su ayuda

MENU ELIMINAR MENU
Submenu1 2007--Submenu2 2007--1 Submenu Item1 2007
Submenu3 2008--1 Submenu Item1 2008



''''''''' ELIMINAR MENU ''''''''''''''''''''''''''''''''''''''''''''''''
Set cbMenu = cb.Controls.Add(msoControlPopup, , , , True)
With cbMenu
.Caption = "&ELIMINAR MENU"
.BeginGroup = True
End With

If cbMenu Is Nothing Then Exit Sub

With cbMenu.Controls.Add(msoControlButton, 1, , , True)
.Caption = "&Eliminar este menú"
.OnAction = ThisWorkbook.Name & "!DeleteCommandBar"
.Style = msoButtonIconAndCaption
.FaceId = 463
.BeginGroup = True
End With

cb.Visible = True

Set cbSubMenu = Nothing
Set cbMenu = Nothing
Set cb = Nothing

End Sub

Sub DeleteCommandBar()
On Error Resume Next
Application.CommandBars("MyCommandBarName").Delete
Application.CommandBars("Chart Menu Bar").Visible = True
On Error GoTo 0
End Sub

Sub Macroname()
MsgBox "Agregue aquí su código VBA!", vbInformation, ThisWorkbook.Name
End Sub

Es la tercera vez que publicas el mismo dilema, y usando nombres diferentes... te repsondere lo mismo (o casi)

- Lee sobre VBA
- Trata, mas que copiar y pegar codigo, de entenderlo
- Practica mucho

Abraham