menu bar & chart menu bar

[Joel Mills]

I have created a menu bar and would like for it to show up in the chart
sheets too. Below is the code for my menu. I'm not sure how to revise it
to also create a "Chart Menu Bar". When I close the workbook this menu is
deleted. I'm not sure if the sub Proceedure to delete the menu should also
be revised. Any help would be appreciated.


Sub CreateMenu()
Dim NewMenu As CommandBarPopup

' Delete the menu if it already exists
Call DeleteMenu

' Find the Help Menu
Set HelpMenu = CommandBars(1).FindControl(ID:=30010)

If HelpMenu Is Nothing Then
' Add the menu to the end
Set NewMenu = CommandBars(1).Controls.Add _
(Type:=msoControlPopup, _
Temporary:=True)
Else
' Add the menu before Help
Set NewMenu = CommandBars(1).Controls.Add _
(Type:=msoControlPopup, _
Before:=HelpMenu.Index, _
Temporary:=True)
End If

' Add a caption for the menu
NewMenu.Caption = "&Chart Builder Menu"

' FIRST MENU ITEM
Set MenuItem = NewMenu.Controls.Add _
(Type:=msoControlPopup)
With MenuItem
.Caption = "Retreive Exported P3 File"
.BeginGroup = True
End With

' FIRST SUBMENU ITEM (First Menu)
Set SubMenuItem = MenuItem.Controls.Add _
(Type:=msoControlButton)
With SubMenuItem
.Caption = "From Local Drive"
.FaceId = 1021
.OnAction = "ExportCdrive"
End With

' SECOND SUBMENU ITEM (First Menu)
Set SubMenuItem = MenuItem.Controls.Add _
(Type:=msoControlButton)
With SubMenuItem
.Caption = "From Network Drive"
.FaceId = 140
.OnAction = "ExportNet"
End With

' SECOND MENU ITEM
Set MenuItem = NewMenu.Controls.Add _
(Type:=msoControlPopup)
With MenuItem
.Caption = "Populate (Pivot Tables and Charts)"
.BeginGroup = True
End With

' FIRST SUBMENU ITEM (Second Menu)
Set SubMenuItem = MenuItem.Controls.Add _
(Type:=msoControlButton)
With SubMenuItem
.Caption = "Copy Exported File (DBF)"
.FaceId = 1642
.OnAction = "CopyData"
End With
'SECOND SUBMENU ITEM (Second Menu)
Set SubMenuItem = MenuItem.Controls.Add _
(Type:=msoControlButton)
With SubMenuItem
.Caption = "Create Database"
.FaceId = 333
.OnAction = "Cleanup"
End With
' THIRD SUBMENU ITEM (Second Menu)
Set SubMenuItem = MenuItem.Controls.Add _
(Type:=msoControlButton)
With SubMenuItem
.Caption = "Create Pivot Tables"
.FaceId = 657
.OnAction = "CreatePivotTable"
End With
' FORTH SUBMENU ITEM (Second Menu)
Set SubMenuItem = MenuItem.Controls.Add _
(Type:=msoControlButton)
With SubMenuItem
.Caption = "Populate Charts"
.FaceId = 433
.OnAction = "Populate_Charts"
End With

' THIRD MENU ITEM
Set MenuItem = NewMenu.Controls.Add _
(Type:=msoControlPopup)
With MenuItem
.Caption = "Chart Settings"
.BeginGroup = True
End With

' FIRST SUBMENU ITEM (Third Menu)
Set SubMenuItem = MenuItem.Controls.Add _
(Type:=msoControlButton)
With SubMenuItem
.Caption = "Resize Chart"
.FaceId = 442
.OnAction = "Resize_Chart"
End With

' SECOND SUBMENU ITEM (Third Menu)
Set SubMenuItem = MenuItem.Controls.Add _
(Type:=msoControlButton)
With SubMenuItem
.Caption = "Set Print Range"
.FaceId = 364
.OnAction = "PrintRange"
End With
' THIRD SUBMENU ITEM (Third Menu)
Set SubMenuItem = MenuItem.Controls.Add _
(Type:=msoControlButton)
With SubMenuItem
.Caption = "Chart Options"
.FaceId = 435
.OnAction = "ShowUserForm1"
End With
' FOURTH SUBMENU ITEM (Third Menu)
Set SubMenuItem = MenuItem.Controls.Add _
(Type:=msoControlButton)
With SubMenuItem
.Caption = "Input Header and Footer Description"
.FaceId = 237
.OnAction = "Go_to_Titles"
End With

' FORTH MENU ITEM
Set MenuItem = NewMenu.Controls.Add _
(Type:=msoControlButton)
With MenuItem
.Caption = "Chart Builder Help"
.BeginGroup = True
.OnAction = "ShowHelpForm"
End With

End Sub

Sub DeleteMenu()
On Error Resume Next
CommandBars(1).Controls("Chart Builder Menu").Delete
End Sub



Private Sub Workbook_Open()
Run ([CreateMenu])
End Sub

[Bob Phillips]

Repeat it for Commandbars(2). I would parameterise it and just pass the CB
id to the routine in a caller routine.

--
HTH

Bob Phillips

"Joel Mills" <[email protected]> wrote in message
news:[email protected]...
> I have created a menu bar and would like for it to show up in the chart
> sheets too. Below is the code for my menu. I'm not sure how to revise it
> to also create a "Chart Menu Bar". When I close the workbook this menu is
> deleted. I'm not sure if the sub Proceedure to delete the menu should
also
> be revised. Any help would be appreciated.
>
>
> Sub CreateMenu()
> Dim NewMenu As CommandBarPopup
>
> ' Delete the menu if it already exists
> Call DeleteMenu
>
> ' Find the Help Menu
> Set HelpMenu = CommandBars(1).FindControl(ID:=30010)
>
> If HelpMenu Is Nothing Then
> ' Add the menu to the end
> Set NewMenu = CommandBars(1).Controls.Add _
> (Type:=msoControlPopup, _
> Temporary:=True)
> Else
> ' Add the menu before Help
> Set NewMenu = CommandBars(1).Controls.Add _
> (Type:=msoControlPopup, _
> Before:=HelpMenu.Index, _
> Temporary:=True)
> End If
>
> ' Add a caption for the menu
> NewMenu.Caption = "&Chart Builder Menu"
>
> ' FIRST MENU ITEM
> Set MenuItem = NewMenu.Controls.Add _
> (Type:=msoControlPopup)
> With MenuItem
> .Caption = "Retreive Exported P3 File"
> .BeginGroup = True
> End With
>
> ' FIRST SUBMENU ITEM (First Menu)
> Set SubMenuItem = MenuItem.Controls.Add _
> (Type:=msoControlButton)
> With SubMenuItem
> .Caption = "From Local Drive"
> .FaceId = 1021
> .OnAction = "ExportCdrive"
> End With
>
> ' SECOND SUBMENU ITEM (First Menu)
> Set SubMenuItem = MenuItem.Controls.Add _
> (Type:=msoControlButton)
> With SubMenuItem
> .Caption = "From Network Drive"
> .FaceId = 140
> .OnAction = "ExportNet"
> End With
>
> ' SECOND MENU ITEM
> Set MenuItem = NewMenu.Controls.Add _
> (Type:=msoControlPopup)
> With MenuItem
> .Caption = "Populate (Pivot Tables and Charts)"
> .BeginGroup = True
> End With
>
> ' FIRST SUBMENU ITEM (Second Menu)
> Set SubMenuItem = MenuItem.Controls.Add _
> (Type:=msoControlButton)
> With SubMenuItem
> .Caption = "Copy Exported File (DBF)"
> .FaceId = 1642
> .OnAction = "CopyData"
> End With
> 'SECOND SUBMENU ITEM (Second Menu)
> Set SubMenuItem = MenuItem.Controls.Add _
> (Type:=msoControlButton)
> With SubMenuItem
> .Caption = "Create Database"
> .FaceId = 333
> .OnAction = "Cleanup"
> End With
> ' THIRD SUBMENU ITEM (Second Menu)
> Set SubMenuItem = MenuItem.Controls.Add _
> (Type:=msoControlButton)
> With SubMenuItem
> .Caption = "Create Pivot Tables"
> .FaceId = 657
> .OnAction = "CreatePivotTable"
> End With
> ' FORTH SUBMENU ITEM (Second Menu)
> Set SubMenuItem = MenuItem.Controls.Add _
> (Type:=msoControlButton)
> With SubMenuItem
> .Caption = "Populate Charts"
> .FaceId = 433
> .OnAction = "Populate_Charts"
> End With
>
> ' THIRD MENU ITEM
> Set MenuItem = NewMenu.Controls.Add _
> (Type:=msoControlPopup)
> With MenuItem
> .Caption = "Chart Settings"
> .BeginGroup = True
> End With
>
> ' FIRST SUBMENU ITEM (Third Menu)
> Set SubMenuItem = MenuItem.Controls.Add _
> (Type:=msoControlButton)
> With SubMenuItem
> .Caption = "Resize Chart"
> .FaceId = 442
> .OnAction = "Resize_Chart"
> End With
>
> ' SECOND SUBMENU ITEM (Third Menu)
> Set SubMenuItem = MenuItem.Controls.Add _
> (Type:=msoControlButton)
> With SubMenuItem
> .Caption = "Set Print Range"
> .FaceId = 364
> .OnAction = "PrintRange"
> End With
> ' THIRD SUBMENU ITEM (Third Menu)
> Set SubMenuItem = MenuItem.Controls.Add _
> (Type:=msoControlButton)
> With SubMenuItem
> .Caption = "Chart Options"
> .FaceId = 435
> .OnAction = "ShowUserForm1"
> End With
> ' FOURTH SUBMENU ITEM (Third Menu)
> Set SubMenuItem = MenuItem.Controls.Add _
> (Type:=msoControlButton)
> With SubMenuItem
> .Caption = "Input Header and Footer Description"
> .FaceId = 237
> .OnAction = "Go_to_Titles"
> End With
>
> ' FORTH MENU ITEM
> Set MenuItem = NewMenu.Controls.Add _
> (Type:=msoControlButton)
> With MenuItem
> .Caption = "Chart Builder Help"
> .BeginGroup = True
> .OnAction = "ShowHelpForm"
> End With
>
> End Sub
>
> Sub DeleteMenu()
> On Error Resume Next
> CommandBars(1).Controls("Chart Builder Menu").Delete
> End Sub
>
>
>
> Private Sub Workbook_Open()
> Run ([CreateMenu])
> End Sub
>
>

[Joel Mills]

Bob, Thanks for the reply, but I'm not very experience with VBA, this is my
first project. I am using it to learn VBA. I probably should have mentioned
that in this post. I did a google search and determined that there are menu
bars and chart menu bars, but wasn't able to grasp how to get both from the
same code. I'm still not sure how to revise my code.


"Bob Phillips" <[email protected]> wrote in message
news:%[email protected]...
> Repeat it for Commandbars(2). I would parameterise it and just pass the CB
> id to the routine in a caller routine.
>
> --
> HTH
>
> Bob Phillips
>
> "Joel Mills" <[email protected]> wrote in message
> news:[email protected]...
>> I have created a menu bar and would like for it to show up in the chart
>> sheets too. Below is the code for my menu. I'm not sure how to revise
>> it
>> to also create a "Chart Menu Bar". When I close the workbook this menu
>> is
>> deleted. I'm not sure if the sub Proceedure to delete the menu should
> also
>> be revised. Any help would be appreciated.
>>
>>
>> Sub CreateMenu()
>> Dim NewMenu As CommandBarPopup
>>
>> ' Delete the menu if it already exists
>> Call DeleteMenu
>>
>> ' Find the Help Menu
>> Set HelpMenu = CommandBars(1).FindControl(ID:=30010)
>>
>> If HelpMenu Is Nothing Then
>> ' Add the menu to the end
>> Set NewMenu = CommandBars(1).Controls.Add _
>> (Type:=msoControlPopup, _
>> Temporary:=True)
>> Else
>> ' Add the menu before Help
>> Set NewMenu = CommandBars(1).Controls.Add _
>> (Type:=msoControlPopup, _
>> Before:=HelpMenu.Index, _
>> Temporary:=True)
>> End If
>>
>> ' Add a caption for the menu
>> NewMenu.Caption = "&Chart Builder Menu"
>>
>> ' FIRST MENU ITEM
>> Set MenuItem = NewMenu.Controls.Add _
>> (Type:=msoControlPopup)
>> With MenuItem
>> .Caption = "Retreive Exported P3 File"
>> .BeginGroup = True
>> End With
>>
>> ' FIRST SUBMENU ITEM (First Menu)
>> Set SubMenuItem = MenuItem.Controls.Add _
>> (Type:=msoControlButton)
>> With SubMenuItem
>> .Caption = "From Local Drive"
>> .FaceId = 1021
>> .OnAction = "ExportCdrive"
>> End With
>>
>> ' SECOND SUBMENU ITEM (First Menu)
>> Set SubMenuItem = MenuItem.Controls.Add _
>> (Type:=msoControlButton)
>> With SubMenuItem
>> .Caption = "From Network Drive"
>> .FaceId = 140
>> .OnAction = "ExportNet"
>> End With
>>
>> ' SECOND MENU ITEM
>> Set MenuItem = NewMenu.Controls.Add _
>> (Type:=msoControlPopup)
>> With MenuItem
>> .Caption = "Populate (Pivot Tables and Charts)"
>> .BeginGroup = True
>> End With
>>
>> ' FIRST SUBMENU ITEM (Second Menu)
>> Set SubMenuItem = MenuItem.Controls.Add _
>> (Type:=msoControlButton)
>> With SubMenuItem
>> .Caption = "Copy Exported File (DBF)"
>> .FaceId = 1642
>> .OnAction = "CopyData"
>> End With
>> 'SECOND SUBMENU ITEM (Second Menu)
>> Set SubMenuItem = MenuItem.Controls.Add _
>> (Type:=msoControlButton)
>> With SubMenuItem
>> .Caption = "Create Database"
>> .FaceId = 333
>> .OnAction = "Cleanup"
>> End With
>> ' THIRD SUBMENU ITEM (Second Menu)
>> Set SubMenuItem = MenuItem.Controls.Add _
>> (Type:=msoControlButton)
>> With SubMenuItem
>> .Caption = "Create Pivot Tables"
>> .FaceId = 657
>> .OnAction = "CreatePivotTable"
>> End With
>> ' FORTH SUBMENU ITEM (Second Menu)
>> Set SubMenuItem = MenuItem.Controls.Add _
>> (Type:=msoControlButton)
>> With SubMenuItem
>> .Caption = "Populate Charts"
>> .FaceId = 433
>> .OnAction = "Populate_Charts"
>> End With
>>
>> ' THIRD MENU ITEM
>> Set MenuItem = NewMenu.Controls.Add _
>> (Type:=msoControlPopup)
>> With MenuItem
>> .Caption = "Chart Settings"
>> .BeginGroup = True
>> End With
>>
>> ' FIRST SUBMENU ITEM (Third Menu)
>> Set SubMenuItem = MenuItem.Controls.Add _
>> (Type:=msoControlButton)
>> With SubMenuItem
>> .Caption = "Resize Chart"
>> .FaceId = 442
>> .OnAction = "Resize_Chart"
>> End With
>>
>> ' SECOND SUBMENU ITEM (Third Menu)
>> Set SubMenuItem = MenuItem.Controls.Add _
>> (Type:=msoControlButton)
>> With SubMenuItem
>> .Caption = "Set Print Range"
>> .FaceId = 364
>> .OnAction = "PrintRange"
>> End With
>> ' THIRD SUBMENU ITEM (Third Menu)
>> Set SubMenuItem = MenuItem.Controls.Add _
>> (Type:=msoControlButton)
>> With SubMenuItem
>> .Caption = "Chart Options"
>> .FaceId = 435
>> .OnAction = "ShowUserForm1"
>> End With
>> ' FOURTH SUBMENU ITEM (Third Menu)
>> Set SubMenuItem = MenuItem.Controls.Add _
>> (Type:=msoControlButton)
>> With SubMenuItem
>> .Caption = "Input Header and Footer Description"
>> .FaceId = 237
>> .OnAction = "Go_to_Titles"
>> End With
>>
>> ' FORTH MENU ITEM
>> Set MenuItem = NewMenu.Controls.Add _
>> (Type:=msoControlButton)
>> With MenuItem
>> .Caption = "Chart Builder Help"
>> .BeginGroup = True
>> .OnAction = "ShowHelpForm"
>> End With
>>
>> End Sub
>>
>> Sub DeleteMenu()
>> On Error Resume Next
>> CommandBars(1).Controls("Chart Builder Menu").Delete
>> End Sub
>>
>>
>>
>> Private Sub Workbook_Open()
>> Run ([CreateMenu])
>> End Sub
>>
>>
>
>

[Bob Phillips]

Try this

Sub CreateMenu
UpdateCB 1
UpdateCB 2
End Sub

Sub UpdateCB(CBId as Long)
Dim NewMenu As CommandBarPopup

' Delete the menu if it already exists
Call DeleteMenu

' Find the Help Menu
Set HelpMenu = CommandBars(CBId).FindControl(ID:=30010)

If HelpMenu Is Nothing Then
' Add the menu to the end
Set NewMenu = CommandBars(1).Controls.Add _
(Type:=msoControlPopup, _
Temporary:=True)
Else
' Add the menu before Help
Set NewMenu = CommandBars(1).Controls.Add _
(Type:=msoControlPopup, _
Before:=HelpMenu.Index, _
Temporary:=True)
End If

' Add a caption for the menu
NewMenu.Caption = "&Chart Builder Menu"

' FIRST MENU ITEM
Set MenuItem = NewMenu.Controls.Add _
(Type:=msoControlPopup)
With MenuItem
.Caption = "Retreive Exported P3 File"
.BeginGroup = True
End With

' FIRST SUBMENU ITEM (First Menu)
Set SubMenuItem = MenuItem.Controls.Add _
(Type:=msoControlButton)
With SubMenuItem
.Caption = "From Local Drive"
.FaceId = 1021
.OnAction = "ExportCdrive"
End With

' SECOND SUBMENU ITEM (First Menu)
Set SubMenuItem = MenuItem.Controls.Add _
(Type:=msoControlButton)
With SubMenuItem
.Caption = "From Network Drive"
.FaceId = 140
.OnAction = "ExportNet"
End With

' SECOND MENU ITEM
Set MenuItem = NewMenu.Controls.Add _
(Type:=msoControlPopup)
With MenuItem
.Caption = "Populate (Pivot Tables and Charts)"
.BeginGroup = True
End With

' FIRST SUBMENU ITEM (Second Menu)
Set SubMenuItem = MenuItem.Controls.Add _
(Type:=msoControlButton)
With SubMenuItem
.Caption = "Copy Exported File (DBF)"
.FaceId = 1642
.OnAction = "CopyData"
End With
'SECOND SUBMENU ITEM (Second Menu)
Set SubMenuItem = MenuItem.Controls.Add _
(Type:=msoControlButton)
With SubMenuItem
.Caption = "Create Database"
.FaceId = 333
.OnAction = "Cleanup"
End With
' THIRD SUBMENU ITEM (Second Menu)
Set SubMenuItem = MenuItem.Controls.Add _
(Type:=msoControlButton)
With SubMenuItem
.Caption = "Create Pivot Tables"
.FaceId = 657
.OnAction = "CreatePivotTable"
End With
' FORTH SUBMENU ITEM (Second Menu)
Set SubMenuItem = MenuItem.Controls.Add _
(Type:=msoControlButton)
With SubMenuItem
.Caption = "Populate Charts"
.FaceId = 433
.OnAction = "Populate_Charts"
End With

' THIRD MENU ITEM
Set MenuItem = NewMenu.Controls.Add _
(Type:=msoControlPopup)
With MenuItem
.Caption = "Chart Settings"
.BeginGroup = True
End With

' FIRST SUBMENU ITEM (Third Menu)
Set SubMenuItem = MenuItem.Controls.Add _
(Type:=msoControlButton)
With SubMenuItem
.Caption = "Resize Chart"
.FaceId = 442
.OnAction = "Resize_Chart"
End With

' SECOND SUBMENU ITEM (Third Menu)
Set SubMenuItem = MenuItem.Controls.Add _
(Type:=msoControlButton)
With SubMenuItem
.Caption = "Set Print Range"
.FaceId = 364
.OnAction = "PrintRange"
End With
' THIRD SUBMENU ITEM (Third Menu)
Set SubMenuItem = MenuItem.Controls.Add _
(Type:=msoControlButton)
With SubMenuItem
.Caption = "Chart Options"
.FaceId = 435
.OnAction = "ShowUserForm1"
End With
' FOURTH SUBMENU ITEM (Third Menu)
Set SubMenuItem = MenuItem.Controls.Add _
(Type:=msoControlButton)
With SubMenuItem
.Caption = "Input Header and Footer Description"
.FaceId = 237
.OnAction = "Go_to_Titles"
End With

' FORTH MENU ITEM
Set MenuItem = NewMenu.Controls.Add _
(Type:=msoControlButton)
With MenuItem
.Caption = "Chart Builder Help"
.BeginGroup = True
.OnAction = "ShowHelpForm"
End With

End Sub

Sub DeleteMenu()
On Error Resume Next
CommandBars(1).Controls("Chart Builder Menu").Delete
CommandBars(2).Controls("Chart Builder Menu").Delete
End Sub


--
HTH

Bob Phillips

"Joel Mills" <[email protected]> wrote in message
news:[email protected]...
> Bob, Thanks for the reply, but I'm not very experience with VBA, this is
my
> first project. I am using it to learn VBA. I probably should have
mentioned
> that in this post. I did a google search and determined that there are
menu
> bars and chart menu bars, but wasn't able to grasp how to get both from
the
> same code. I'm still not sure how to revise my code.
>
>
> "Bob Phillips" <[email protected]> wrote in message
> news:%[email protected]...
> > Repeat it for Commandbars(2). I would parameterise it and just pass the
CB
> > id to the routine in a caller routine.
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > "Joel Mills" <[email protected]> wrote in message
> > news:[email protected]...
> >> I have created a menu bar and would like for it to show up in the chart
> >> sheets too. Below is the code for my menu. I'm not sure how to revise
> >> it
> >> to also create a "Chart Menu Bar". When I close the workbook this menu
> >> is
> >> deleted. I'm not sure if the sub Proceedure to delete the menu should
> > also
> >> be revised. Any help would be appreciated.
> >>
> >>
> >> Sub CreateMenu()
> >> Dim NewMenu As CommandBarPopup
> >>
> >> ' Delete the menu if it already exists
> >> Call DeleteMenu
> >>
> >> ' Find the Help Menu
> >> Set HelpMenu = CommandBars(1).FindControl(ID:=30010)
> >>
> >> If HelpMenu Is Nothing Then
> >> ' Add the menu to the end
> >> Set NewMenu = CommandBars(1).Controls.Add _
> >> (Type:=msoControlPopup, _
> >> Temporary:=True)
> >> Else
> >> ' Add the menu before Help
> >> Set NewMenu = CommandBars(1).Controls.Add _
> >> (Type:=msoControlPopup, _
> >> Before:=HelpMenu.Index, _
> >> Temporary:=True)
> >> End If
> >>
> >> ' Add a caption for the menu
> >> NewMenu.Caption = "&Chart Builder Menu"
> >>
> >> ' FIRST MENU ITEM
> >> Set MenuItem = NewMenu.Controls.Add _
> >> (Type:=msoControlPopup)
> >> With MenuItem
> >> .Caption = "Retreive Exported P3 File"
> >> .BeginGroup = True
> >> End With
> >>
> >> ' FIRST SUBMENU ITEM (First Menu)
> >> Set SubMenuItem = MenuItem.Controls.Add _
> >> (Type:=msoControlButton)
> >> With SubMenuItem
> >> .Caption = "From Local Drive"
> >> .FaceId = 1021
> >> .OnAction = "ExportCdrive"
> >> End With
> >>
> >> ' SECOND SUBMENU ITEM (First Menu)
> >> Set SubMenuItem = MenuItem.Controls.Add _
> >> (Type:=msoControlButton)
> >> With SubMenuItem
> >> .Caption = "From Network Drive"
> >> .FaceId = 140
> >> .OnAction = "ExportNet"
> >> End With
> >>
> >> ' SECOND MENU ITEM
> >> Set MenuItem = NewMenu.Controls.Add _
> >> (Type:=msoControlPopup)
> >> With MenuItem
> >> .Caption = "Populate (Pivot Tables and Charts)"
> >> .BeginGroup = True
> >> End With
> >>
> >> ' FIRST SUBMENU ITEM (Second Menu)
> >> Set SubMenuItem = MenuItem.Controls.Add _
> >> (Type:=msoControlButton)
> >> With SubMenuItem
> >> .Caption = "Copy Exported File (DBF)"
> >> .FaceId = 1642
> >> .OnAction = "CopyData"
> >> End With
> >> 'SECOND SUBMENU ITEM (Second Menu)
> >> Set SubMenuItem = MenuItem.Controls.Add _
> >> (Type:=msoControlButton)
> >> With SubMenuItem
> >> .Caption = "Create Database"
> >> .FaceId = 333
> >> .OnAction = "Cleanup"
> >> End With
> >> ' THIRD SUBMENU ITEM (Second Menu)
> >> Set SubMenuItem = MenuItem.Controls.Add _
> >> (Type:=msoControlButton)
> >> With SubMenuItem
> >> .Caption = "Create Pivot Tables"
> >> .FaceId = 657
> >> .OnAction = "CreatePivotTable"
> >> End With
> >> ' FORTH SUBMENU ITEM (Second Menu)
> >> Set SubMenuItem = MenuItem.Controls.Add _
> >> (Type:=msoControlButton)
> >> With SubMenuItem
> >> .Caption = "Populate Charts"
> >> .FaceId = 433
> >> .OnAction = "Populate_Charts"
> >> End With
> >>
> >> ' THIRD MENU ITEM
> >> Set MenuItem = NewMenu.Controls.Add _
> >> (Type:=msoControlPopup)
> >> With MenuItem
> >> .Caption = "Chart Settings"
> >> .BeginGroup = True
> >> End With
> >>
> >> ' FIRST SUBMENU ITEM (Third Menu)
> >> Set SubMenuItem = MenuItem.Controls.Add _
> >> (Type:=msoControlButton)
> >> With SubMenuItem
> >> .Caption = "Resize Chart"
> >> .FaceId = 442
> >> .OnAction = "Resize_Chart"
> >> End With
> >>
> >> ' SECOND SUBMENU ITEM (Third Menu)
> >> Set SubMenuItem = MenuItem.Controls.Add _
> >> (Type:=msoControlButton)
> >> With SubMenuItem
> >> .Caption = "Set Print Range"
> >> .FaceId = 364
> >> .OnAction = "PrintRange"
> >> End With
> >> ' THIRD SUBMENU ITEM (Third Menu)
> >> Set SubMenuItem = MenuItem.Controls.Add _
> >> (Type:=msoControlButton)
> >> With SubMenuItem
> >> .Caption = "Chart Options"
> >> .FaceId = 435
> >> .OnAction = "ShowUserForm1"
> >> End With
> >> ' FOURTH SUBMENU ITEM (Third Menu)
> >> Set SubMenuItem = MenuItem.Controls.Add _
> >> (Type:=msoControlButton)
> >> With SubMenuItem
> >> .Caption = "Input Header and Footer Description"
> >> .FaceId = 237
> >> .OnAction = "Go_to_Titles"
> >> End With
> >>
> >> ' FORTH MENU ITEM
> >> Set MenuItem = NewMenu.Controls.Add _
> >> (Type:=msoControlButton)
> >> With MenuItem
> >> .Caption = "Chart Builder Help"
> >> .BeginGroup = True
> >> .OnAction = "ShowHelpForm"
> >> End With
> >>
> >> End Sub
> >>
> >> Sub DeleteMenu()
> >> On Error Resume Next
> >> CommandBars(1).Controls("Chart Builder Menu").Delete
> >> End Sub
> >>
> >>
> >>
> >> Private Sub Workbook_Open()
> >> Run ([CreateMenu])
> >> End Sub
> >>
> >>
> >
> >
>
>

[Joel Mills]

This still doesn't add a "Chart Menu Bar". What am I missing? I changed
CommandBars(1) to CommandBars(CBId) on the If then Else statements and now
it creates the Chart Menu Bar and not the Menu Bar. This must be very close
to the code I'm looking for.

Sub UpdateCB(CBId As Long)
Dim NewMenu As CommandBarPopup

' Delete the menu if it already exists
Call DeleteMenu

' Find the Help Menu
Set HelpMenu = CommandBars(CBId).FindControl(ID:=30010)

If HelpMenu Is Nothing Then
' Add the menu to the end
Set NewMenu = CommandBars(CBId).Controls.Add _
(Type:=msoControlPopup, _
Temporary:=True)
Else
' Add the menu before Help
Set NewMenu = CommandBars(CBId).Controls.Add _
(Type:=msoControlPopup, _
Before:=HelpMenu.Index, _
Temporary:=True)

"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> Try this
>
> Sub CreateMenu
> UpdateCB 1
> UpdateCB 2
> End Sub
>
> Sub UpdateCB(CBId as Long)
> Dim NewMenu As CommandBarPopup
>
> ' Delete the menu if it already exists
> Call DeleteMenu
>
> ' Find the Help Menu
> Set HelpMenu = CommandBars(CBId).FindControl(ID:=30010)
>
> If HelpMenu Is Nothing Then
> ' Add the menu to the end
> Set NewMenu = CommandBars(1).Controls.Add _
> (Type:=msoControlPopup, _
> Temporary:=True)
> Else
> ' Add the menu before Help
> Set NewMenu = CommandBars(1).Controls.Add _
> (Type:=msoControlPopup, _
> Before:=HelpMenu.Index, _
> Temporary:=True)
> End If
>
> ' Add a caption for the menu
> NewMenu.Caption = "&Chart Builder Menu"
>
> ' FIRST MENU ITEM
> Set MenuItem = NewMenu.Controls.Add _
> (Type:=msoControlPopup)
> With MenuItem
> .Caption = "Retreive Exported P3 File"
> .BeginGroup = True
> End With
>
> ' FIRST SUBMENU ITEM (First Menu)
> Set SubMenuItem = MenuItem.Controls.Add _
> (Type:=msoControlButton)
> With SubMenuItem
> .Caption = "From Local Drive"
> .FaceId = 1021
> .OnAction = "ExportCdrive"
> End With
>
> ' SECOND SUBMENU ITEM (First Menu)
> Set SubMenuItem = MenuItem.Controls.Add _
> (Type:=msoControlButton)
> With SubMenuItem
> .Caption = "From Network Drive"
> .FaceId = 140
> .OnAction = "ExportNet"
> End With
>
> ' SECOND MENU ITEM
> Set MenuItem = NewMenu.Controls.Add _
> (Type:=msoControlPopup)
> With MenuItem
> .Caption = "Populate (Pivot Tables and Charts)"
> .BeginGroup = True
> End With
>
> ' FIRST SUBMENU ITEM (Second Menu)
> Set SubMenuItem = MenuItem.Controls.Add _
> (Type:=msoControlButton)
> With SubMenuItem
> .Caption = "Copy Exported File (DBF)"
> .FaceId = 1642
> .OnAction = "CopyData"
> End With
> 'SECOND SUBMENU ITEM (Second Menu)
> Set SubMenuItem = MenuItem.Controls.Add _
> (Type:=msoControlButton)
> With SubMenuItem
> .Caption = "Create Database"
> .FaceId = 333
> .OnAction = "Cleanup"
> End With
> ' THIRD SUBMENU ITEM (Second Menu)
> Set SubMenuItem = MenuItem.Controls.Add _
> (Type:=msoControlButton)
> With SubMenuItem
> .Caption = "Create Pivot Tables"
> .FaceId = 657
> .OnAction = "CreatePivotTable"
> End With
> ' FORTH SUBMENU ITEM (Second Menu)
> Set SubMenuItem = MenuItem.Controls.Add _
> (Type:=msoControlButton)
> With SubMenuItem
> .Caption = "Populate Charts"
> .FaceId = 433
> .OnAction = "Populate_Charts"
> End With
>
> ' THIRD MENU ITEM
> Set MenuItem = NewMenu.Controls.Add _
> (Type:=msoControlPopup)
> With MenuItem
> .Caption = "Chart Settings"
> .BeginGroup = True
> End With
>
> ' FIRST SUBMENU ITEM (Third Menu)
> Set SubMenuItem = MenuItem.Controls.Add _
> (Type:=msoControlButton)
> With SubMenuItem
> .Caption = "Resize Chart"
> .FaceId = 442
> .OnAction = "Resize_Chart"
> End With
>
> ' SECOND SUBMENU ITEM (Third Menu)
> Set SubMenuItem = MenuItem.Controls.Add _
> (Type:=msoControlButton)
> With SubMenuItem
> .Caption = "Set Print Range"
> .FaceId = 364
> .OnAction = "PrintRange"
> End With
> ' THIRD SUBMENU ITEM (Third Menu)
> Set SubMenuItem = MenuItem.Controls.Add _
> (Type:=msoControlButton)
> With SubMenuItem
> .Caption = "Chart Options"
> .FaceId = 435
> .OnAction = "ShowUserForm1"
> End With
> ' FOURTH SUBMENU ITEM (Third Menu)
> Set SubMenuItem = MenuItem.Controls.Add _
> (Type:=msoControlButton)
> With SubMenuItem
> .Caption = "Input Header and Footer Description"
> .FaceId = 237
> .OnAction = "Go_to_Titles"
> End With
>
> ' FORTH MENU ITEM
> Set MenuItem = NewMenu.Controls.Add _
> (Type:=msoControlButton)
> With MenuItem
> .Caption = "Chart Builder Help"
> .BeginGroup = True
> .OnAction = "ShowHelpForm"
> End With
>
> End Sub
>
> Sub DeleteMenu()
> On Error Resume Next
> CommandBars(1).Controls("Chart Builder Menu").Delete
> CommandBars(2).Controls("Chart Builder Menu").Delete
> End Sub
>
>
> --
> HTH
>
> Bob Phillips
>
> "Joel Mills" <[email protected]> wrote in message
> news:[email protected]...
>> Bob, Thanks for the reply, but I'm not very experience with VBA, this is
> my
>> first project. I am using it to learn VBA. I probably should have
> mentioned
>> that in this post. I did a google search and determined that there are
> menu
>> bars and chart menu bars, but wasn't able to grasp how to get both from
> the
>> same code. I'm still not sure how to revise my code.
>>
>>
>> "Bob Phillips" <[email protected]> wrote in message
>> news:%[email protected]...
>> > Repeat it for Commandbars(2). I would parameterise it and just pass the
> CB
>> > id to the routine in a caller routine.
>> >
>> > --
>> > HTH
>> >
>> > Bob Phillips
>> >
>> > "Joel Mills" <[email protected]> wrote in message
>> > news:[email protected]...
>> >> I have created a menu bar and would like for it to show up in the
>> >> chart
>> >> sheets too. Below is the code for my menu. I'm not sure how to
>> >> revise
>> >> it
>> >> to also create a "Chart Menu Bar". When I close the workbook this
>> >> menu
>> >> is
>> >> deleted. I'm not sure if the sub Proceedure to delete the menu should
>> > also
>> >> be revised. Any help would be appreciated.
>> >>
>> >>
>> >> Sub CreateMenu()
>> >> Dim NewMenu As CommandBarPopup
>> >>
>> >> ' Delete the menu if it already exists
>> >> Call DeleteMenu
>> >>
>> >> ' Find the Help Menu
>> >> Set HelpMenu = CommandBars(1).FindControl(ID:=30010)
>> >>
>> >> If HelpMenu Is Nothing Then
>> >> ' Add the menu to the end
>> >> Set NewMenu = CommandBars(1).Controls.Add _
>> >> (Type:=msoControlPopup, _
>> >> Temporary:=True)
>> >> Else
>> >> ' Add the menu before Help
>> >> Set NewMenu = CommandBars(1).Controls.Add _
>> >> (Type:=msoControlPopup, _
>> >> Before:=HelpMenu.Index, _
>> >> Temporary:=True)
>> >> End If
>> >>
>> >> ' Add a caption for the menu
>> >> NewMenu.Caption = "&Chart Builder Menu"
>> >>
>> >> ' FIRST MENU ITEM
>> >> Set MenuItem = NewMenu.Controls.Add _
>> >> (Type:=msoControlPopup)
>> >> With MenuItem
>> >> .Caption = "Retreive Exported P3 File"
>> >> .BeginGroup = True
>> >> End With
>> >>
>> >> ' FIRST SUBMENU ITEM (First Menu)
>> >> Set SubMenuItem = MenuItem.Controls.Add _
>> >> (Type:=msoControlButton)
>> >> With SubMenuItem
>> >> .Caption = "From Local Drive"
>> >> .FaceId = 1021
>> >> .OnAction = "ExportCdrive"
>> >> End With
>> >>
>> >> ' SECOND SUBMENU ITEM (First Menu)
>> >> Set SubMenuItem = MenuItem.Controls.Add _
>> >> (Type:=msoControlButton)
>> >> With SubMenuItem
>> >> .Caption = "From Network Drive"
>> >> .FaceId = 140
>> >> .OnAction = "ExportNet"
>> >> End With
>> >>
>> >> ' SECOND MENU ITEM
>> >> Set MenuItem = NewMenu.Controls.Add _
>> >> (Type:=msoControlPopup)
>> >> With MenuItem
>> >> .Caption = "Populate (Pivot Tables and Charts)"
>> >> .BeginGroup = True
>> >> End With
>> >>
>> >> ' FIRST SUBMENU ITEM (Second Menu)
>> >> Set SubMenuItem = MenuItem.Controls.Add _
>> >> (Type:=msoControlButton)
>> >> With SubMenuItem
>> >> .Caption = "Copy Exported File (DBF)"
>> >> .FaceId = 1642
>> >> .OnAction = "CopyData"
>> >> End With
>> >> 'SECOND SUBMENU ITEM (Second Menu)
>> >> Set SubMenuItem = MenuItem.Controls.Add _
>> >> (Type:=msoControlButton)
>> >> With SubMenuItem
>> >> .Caption = "Create Database"
>> >> .FaceId = 333
>> >> .OnAction = "Cleanup"
>> >> End With
>> >> ' THIRD SUBMENU ITEM (Second Menu)
>> >> Set SubMenuItem = MenuItem.Controls.Add _
>> >> (Type:=msoControlButton)
>> >> With SubMenuItem
>> >> .Caption = "Create Pivot Tables"
>> >> .FaceId = 657
>> >> .OnAction = "CreatePivotTable"
>> >> End With
>> >> ' FORTH SUBMENU ITEM (Second Menu)
>> >> Set SubMenuItem = MenuItem.Controls.Add _
>> >> (Type:=msoControlButton)
>> >> With SubMenuItem
>> >> .Caption = "Populate Charts"
>> >> .FaceId = 433
>> >> .OnAction = "Populate_Charts"
>> >> End With
>> >>
>> >> ' THIRD MENU ITEM
>> >> Set MenuItem = NewMenu.Controls.Add _
>> >> (Type:=msoControlPopup)
>> >> With MenuItem
>> >> .Caption = "Chart Settings"
>> >> .BeginGroup = True
>> >> End With
>> >>
>> >> ' FIRST SUBMENU ITEM (Third Menu)
>> >> Set SubMenuItem = MenuItem.Controls.Add _
>> >> (Type:=msoControlButton)
>> >> With SubMenuItem
>> >> .Caption = "Resize Chart"
>> >> .FaceId = 442
>> >> .OnAction = "Resize_Chart"
>> >> End With
>> >>
>> >> ' SECOND SUBMENU ITEM (Third Menu)
>> >> Set SubMenuItem = MenuItem.Controls.Add _
>> >> (Type:=msoControlButton)
>> >> With SubMenuItem
>> >> .Caption = "Set Print Range"
>> >> .FaceId = 364
>> >> .OnAction = "PrintRange"
>> >> End With
>> >> ' THIRD SUBMENU ITEM (Third Menu)
>> >> Set SubMenuItem = MenuItem.Controls.Add _
>> >> (Type:=msoControlButton)
>> >> With SubMenuItem
>> >> .Caption = "Chart Options"
>> >> .FaceId = 435
>> >> .OnAction = "ShowUserForm1"
>> >> End With
>> >> ' FOURTH SUBMENU ITEM (Third Menu)
>> >> Set SubMenuItem = MenuItem.Controls.Add _
>> >> (Type:=msoControlButton)
>> >> With SubMenuItem
>> >> .Caption = "Input Header and Footer Description"
>> >> .FaceId = 237
>> >> .OnAction = "Go_to_Titles"
>> >> End With
>> >>
>> >> ' FORTH MENU ITEM
>> >> Set MenuItem = NewMenu.Controls.Add _
>> >> (Type:=msoControlButton)
>> >> With MenuItem
>> >> .Caption = "Chart Builder Help"
>> >> .BeginGroup = True
>> >> .OnAction = "ShowHelpForm"
>> >> End With
>> >>
>> >> End Sub
>> >>
>> >> Sub DeleteMenu()
>> >> On Error Resume Next
>> >> CommandBars(1).Controls("Chart Builder Menu").Delete
>> >> End Sub
>> >>
>> >>
>> >>
>> >> Private Sub Workbook_Open()
>> >> Run ([CreateMenu])
>> >> End Sub
>> >>
>> >>
>> >
>> >
>>
>>
>
>

[Bob Phillips]

Joel,

Try again with this small mod, it seems to work okay.

Sub CreateMenu()
UpdateCB 1
UpdateCB 2
End Sub

Sub UpdateCB(CBId As Long)
Dim NewMenu As CommandBarPopup

' Delete the menu if it already exists
Call DeleteMenu(CBId)

' Find the Help Menu
Set HelpMenu = CommandBars(CBId).FindControl(ID:=30010)

If HelpMenu Is Nothing Then
' Add the menu to the end
Set NewMenu = CommandBars(CBId).Controls.Add _
(Type:=msoControlPopup, _
Temporary:=True)
Else
' Add the menu before Help
Set NewMenu = CommandBars(CBId).Controls.Add _
(Type:=msoControlPopup, _
Before:=HelpMenu.Index, _
Temporary:=True)
End If

' Add a caption for the menu
NewMenu.Caption = "&Chart Builder Menu"

' FIRST MENU ITEM
Set MenuItem = NewMenu.Controls.Add _
(Type:=msoControlPopup)
With MenuItem
.Caption = "Retreive Exported P3 File"
.BeginGroup = True
End With

' FIRST SUBMENU ITEM (First Menu)
Set SubMenuItem = MenuItem.Controls.Add _
(Type:=msoControlButton)
With SubMenuItem
.Caption = "From Local Drive"
.FaceId = 1021
.OnAction = "ExportCdrive"
End With

' SECOND SUBMENU ITEM (First Menu)
Set SubMenuItem = MenuItem.Controls.Add _
(Type:=msoControlButton)
With SubMenuItem
.Caption = "From Network Drive"
.FaceId = 140
.OnAction = "ExportNet"
End With

' SECOND MENU ITEM
Set MenuItem = NewMenu.Controls.Add _
(Type:=msoControlPopup)
With MenuItem
.Caption = "Populate (Pivot Tables and Charts)"
.BeginGroup = True
End With

' FIRST SUBMENU ITEM (Second Menu)
Set SubMenuItem = MenuItem.Controls.Add _
(Type:=msoControlButton)
With SubMenuItem
.Caption = "Copy Exported File (DBF)"
.FaceId = 1642
.OnAction = "CopyData"
End With
'SECOND SUBMENU ITEM (Second Menu)
Set SubMenuItem = MenuItem.Controls.Add _
(Type:=msoControlButton)
With SubMenuItem
.Caption = "Create Database"
.FaceId = 333
.OnAction = "Cleanup"
End With
' THIRD SUBMENU ITEM (Second Menu)
Set SubMenuItem = MenuItem.Controls.Add _
(Type:=msoControlButton)
With SubMenuItem
.Caption = "Create Pivot Tables"
.FaceId = 657
.OnAction = "CreatePivotTable"
End With
' FORTH SUBMENU ITEM (Second Menu)
Set SubMenuItem = MenuItem.Controls.Add _
(Type:=msoControlButton)
With SubMenuItem
.Caption = "Populate Charts"
.FaceId = 433
.OnAction = "Populate_Charts"
End With

' THIRD MENU ITEM
Set MenuItem = NewMenu.Controls.Add _
(Type:=msoControlPopup)
With MenuItem
.Caption = "Chart Settings"
.BeginGroup = True
End With

' FIRST SUBMENU ITEM (Third Menu)
Set SubMenuItem = MenuItem.Controls.Add _
(Type:=msoControlButton)
With SubMenuItem
.Caption = "Resize Chart"
.FaceId = 442
.OnAction = "Resize_Chart"
End With

' SECOND SUBMENU ITEM (Third Menu)
Set SubMenuItem = MenuItem.Controls.Add _
(Type:=msoControlButton)
With SubMenuItem
.Caption = "Set Print Range"
.FaceId = 364
.OnAction = "PrintRange"
End With
' THIRD SUBMENU ITEM (Third Menu)
Set SubMenuItem = MenuItem.Controls.Add _
(Type:=msoControlButton)
With SubMenuItem
.Caption = "Chart Options"
.FaceId = 435
.OnAction = "ShowUserForm1"
End With
' FOURTH SUBMENU ITEM (Third Menu)
Set SubMenuItem = MenuItem.Controls.Add _
(Type:=msoControlButton)
With SubMenuItem
.Caption = "Input Header and Footer Description"
.FaceId = 237
.OnAction = "Go_to_Titles"
End With

' FORTH MENU ITEM
Set MenuItem = NewMenu.Controls.Add _
(Type:=msoControlButton)
With MenuItem
.Caption = "Chart Builder Help"
.BeginGroup = True
.OnAction = "ShowHelpForm"
End With

End Sub

Sub DeleteMenu(CBId As Long)
On Error Resume Next
CommandBars(CBId).Controls("Chart Builder Menu").Delete
End Sub





--
HTH

Bob Phillips

"Joel Mills" <[email protected]> wrote in message
news:[email protected]...
> This still doesn't add a "Chart Menu Bar". What am I missing? I changed
> CommandBars(1) to CommandBars(CBId) on the If then Else statements and now
> it creates the Chart Menu Bar and not the Menu Bar. This must be very
close
> to the code I'm looking for.
>
> Sub UpdateCB(CBId As Long)
> Dim NewMenu As CommandBarPopup
>
> ' Delete the menu if it already exists
> Call DeleteMenu
>
> ' Find the Help Menu
> Set HelpMenu = CommandBars(CBId).FindControl(ID:=30010)
>
> If HelpMenu Is Nothing Then
> ' Add the menu to the end
> Set NewMenu = CommandBars(CBId).Controls.Add _
> (Type:=msoControlPopup, _
> Temporary:=True)
> Else
> ' Add the menu before Help
> Set NewMenu = CommandBars(CBId).Controls.Add _
> (Type:=msoControlPopup, _
> Before:=HelpMenu.Index, _
> Temporary:=True)
>
> "Bob Phillips" <[email protected]> wrote in message
> news:[email protected]...
> > Try this
> >
> > Sub CreateMenu
> > UpdateCB 1
> > UpdateCB 2
> > End Sub
> >
> > Sub UpdateCB(CBId as Long)
> > Dim NewMenu As CommandBarPopup
> >
> > ' Delete the menu if it already exists
> > Call DeleteMenu
> >
> > ' Find the Help Menu
> > Set HelpMenu = CommandBars(CBId).FindControl(ID:=30010)
> >
> > If HelpMenu Is Nothing Then
> > ' Add the menu to the end
> > Set NewMenu = CommandBars(1).Controls.Add _
> > (Type:=msoControlPopup, _
> > Temporary:=True)
> > Else
> > ' Add the menu before Help
> > Set NewMenu = CommandBars(1).Controls.Add _
> > (Type:=msoControlPopup, _
> > Before:=HelpMenu.Index, _
> > Temporary:=True)
> > End If
> >
> > ' Add a caption for the menu
> > NewMenu.Caption = "&Chart Builder Menu"
> >
> > ' FIRST MENU ITEM
> > Set MenuItem = NewMenu.Controls.Add _
> > (Type:=msoControlPopup)
> > With MenuItem
> > .Caption = "Retreive Exported P3 File"
> > .BeginGroup = True
> > End With
> >
> > ' FIRST SUBMENU ITEM (First Menu)
> > Set SubMenuItem = MenuItem.Controls.Add _
> > (Type:=msoControlButton)
> > With SubMenuItem
> > .Caption = "From Local Drive"
> > .FaceId = 1021
> > .OnAction = "ExportCdrive"
> > End With
> >
> > ' SECOND SUBMENU ITEM (First Menu)
> > Set SubMenuItem = MenuItem.Controls.Add _
> > (Type:=msoControlButton)
> > With SubMenuItem
> > .Caption = "From Network Drive"
> > .FaceId = 140
> > .OnAction = "ExportNet"
> > End With
> >
> > ' SECOND MENU ITEM
> > Set MenuItem = NewMenu.Controls.Add _
> > (Type:=msoControlPopup)
> > With MenuItem
> > .Caption = "Populate (Pivot Tables and Charts)"
> > .BeginGroup = True
> > End With
> >
> > ' FIRST SUBMENU ITEM (Second Menu)
> > Set SubMenuItem = MenuItem.Controls.Add _
> > (Type:=msoControlButton)
> > With SubMenuItem
> > .Caption = "Copy Exported File (DBF)"
> > .FaceId = 1642
> > .OnAction = "CopyData"
> > End With
> > 'SECOND SUBMENU ITEM (Second Menu)
> > Set SubMenuItem = MenuItem.Controls.Add _
> > (Type:=msoControlButton)
> > With SubMenuItem
> > .Caption = "Create Database"
> > .FaceId = 333
> > .OnAction = "Cleanup"
> > End With
> > ' THIRD SUBMENU ITEM (Second Menu)
> > Set SubMenuItem = MenuItem.Controls.Add _
> > (Type:=msoControlButton)
> > With SubMenuItem
> > .Caption = "Create Pivot Tables"
> > .FaceId = 657
> > .OnAction = "CreatePivotTable"
> > End With
> > ' FORTH SUBMENU ITEM (Second Menu)
> > Set SubMenuItem = MenuItem.Controls.Add _
> > (Type:=msoControlButton)
> > With SubMenuItem
> > .Caption = "Populate Charts"
> > .FaceId = 433
> > .OnAction = "Populate_Charts"
> > End With
> >
> > ' THIRD MENU ITEM
> > Set MenuItem = NewMenu.Controls.Add _
> > (Type:=msoControlPopup)
> > With MenuItem
> > .Caption = "Chart Settings"
> > .BeginGroup = True
> > End With
> >
> > ' FIRST SUBMENU ITEM (Third Menu)
> > Set SubMenuItem = MenuItem.Controls.Add _
> > (Type:=msoControlButton)
> > With SubMenuItem
> > .Caption = "Resize Chart"
> > .FaceId = 442
> > .OnAction = "Resize_Chart"
> > End With
> >
> > ' SECOND SUBMENU ITEM (Third Menu)
> > Set SubMenuItem = MenuItem.Controls.Add _
> > (Type:=msoControlButton)
> > With SubMenuItem
> > .Caption = "Set Print Range"
> > .FaceId = 364
> > .OnAction = "PrintRange"
> > End With
> > ' THIRD SUBMENU ITEM (Third Menu)
> > Set SubMenuItem = MenuItem.Controls.Add _
> > (Type:=msoControlButton)
> > With SubMenuItem
> > .Caption = "Chart Options"
> > .FaceId = 435
> > .OnAction = "ShowUserForm1"
> > End With
> > ' FOURTH SUBMENU ITEM (Third Menu)
> > Set SubMenuItem = MenuItem.Controls.Add _
> > (Type:=msoControlButton)
> > With SubMenuItem
> > .Caption = "Input Header and Footer Description"
> > .FaceId = 237
> > .OnAction = "Go_to_Titles"
> > End With
> >
> > ' FORTH MENU ITEM
> > Set MenuItem = NewMenu.Controls.Add _
> > (Type:=msoControlButton)
> > With MenuItem
> > .Caption = "Chart Builder Help"
> > .BeginGroup = True
> > .OnAction = "ShowHelpForm"
> > End With
> >
> > End Sub
> >
> > Sub DeleteMenu()
> > On Error Resume Next
> > CommandBars(1).Controls("Chart Builder Menu").Delete
> > CommandBars(2).Controls("Chart Builder Menu").Delete
> > End Sub
> >
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > "Joel Mills" <[email protected]> wrote in message
> > news:[email protected]...
> >> Bob, Thanks for the reply, but I'm not very experience with VBA, this
is
> > my
> >> first project. I am using it to learn VBA. I probably should have
> > mentioned
> >> that in this post. I did a google search and determined that there are
> > menu
> >> bars and chart menu bars, but wasn't able to grasp how to get both from
> > the
> >> same code. I'm still not sure how to revise my code.
> >>
> >>
> >> "Bob Phillips" <[email protected]> wrote in message
> >> news:%[email protected]...
> >> > Repeat it for Commandbars(2). I would parameterise it and just pass
the
> > CB
> >> > id to the routine in a caller routine.
> >> >
> >> > --
> >> > HTH
> >> >
> >> > Bob Phillips
> >> >
> >> > "Joel Mills" <[email protected]> wrote in message
> >> > news:[email protected]...
> >> >> I have created a menu bar and would like for it to show up in the
> >> >> chart
> >> >> sheets too. Below is the code for my menu. I'm not sure how to
> >> >> revise
> >> >> it
> >> >> to also create a "Chart Menu Bar". When I close the workbook this
> >> >> menu
> >> >> is
> >> >> deleted. I'm not sure if the sub Proceedure to delete the menu
should
> >> > also
> >> >> be revised. Any help would be appreciated.
> >> >>
> >> >>
> >> >> Sub CreateMenu()
> >> >> Dim NewMenu As CommandBarPopup
> >> >>
> >> >> ' Delete the menu if it already exists
> >> >> Call DeleteMenu
> >> >>
> >> >> ' Find the Help Menu
> >> >> Set HelpMenu = CommandBars(1).FindControl(ID:=30010)
> >> >>
> >> >> If HelpMenu Is Nothing Then
> >> >> ' Add the menu to the end
> >> >> Set NewMenu = CommandBars(1).Controls.Add _
> >> >> (Type:=msoControlPopup, _
> >> >> Temporary:=True)
> >> >> Else
> >> >> ' Add the menu before Help
> >> >> Set NewMenu = CommandBars(1).Controls.Add _
> >> >> (Type:=msoControlPopup, _
> >> >> Before:=HelpMenu.Index, _
> >> >> Temporary:=True)
> >> >> End If
> >> >>
> >> >> ' Add a caption for the menu
> >> >> NewMenu.Caption = "&Chart Builder Menu"
> >> >>
> >> >> ' FIRST MENU ITEM
> >> >> Set MenuItem = NewMenu.Controls.Add _
> >> >> (Type:=msoControlPopup)
> >> >> With MenuItem
> >> >> .Caption = "Retreive Exported P3 File"
> >> >> .BeginGroup = True
> >> >> End With
> >> >>
> >> >> ' FIRST SUBMENU ITEM (First Menu)
> >> >> Set SubMenuItem = MenuItem.Controls.Add _
> >> >> (Type:=msoControlButton)
> >> >> With SubMenuItem
> >> >> .Caption = "From Local Drive"
> >> >> .FaceId = 1021
> >> >> .OnAction = "ExportCdrive"
> >> >> End With
> >> >>
> >> >> ' SECOND SUBMENU ITEM (First Menu)
> >> >> Set SubMenuItem = MenuItem.Controls.Add _
> >> >> (Type:=msoControlButton)
> >> >> With SubMenuItem
> >> >> .Caption = "From Network Drive"
> >> >> .FaceId = 140
> >> >> .OnAction = "ExportNet"
> >> >> End With
> >> >>
> >> >> ' SECOND MENU ITEM
> >> >> Set MenuItem = NewMenu.Controls.Add _
> >> >> (Type:=msoControlPopup)
> >> >> With MenuItem
> >> >> .Caption = "Populate (Pivot Tables and Charts)"
> >> >> .BeginGroup = True
> >> >> End With
> >> >>
> >> >> ' FIRST SUBMENU ITEM (Second Menu)
> >> >> Set SubMenuItem = MenuItem.Controls.Add _
> >> >> (Type:=msoControlButton)
> >> >> With SubMenuItem
> >> >> .Caption = "Copy Exported File (DBF)"
> >> >> .FaceId = 1642
> >> >> .OnAction = "CopyData"
> >> >> End With
> >> >> 'SECOND SUBMENU ITEM (Second Menu)
> >> >> Set SubMenuItem = MenuItem.Controls.Add _
> >> >> (Type:=msoControlButton)
> >> >> With SubMenuItem
> >> >> .Caption = "Create Database"
> >> >> .FaceId = 333
> >> >> .OnAction = "Cleanup"
> >> >> End With
> >> >> ' THIRD SUBMENU ITEM (Second Menu)
> >> >> Set SubMenuItem = MenuItem.Controls.Add _
> >> >> (Type:=msoControlButton)
> >> >> With SubMenuItem
> >> >> .Caption = "Create Pivot Tables"
> >> >> .FaceId = 657
> >> >> .OnAction = "CreatePivotTable"
> >> >> End With
> >> >> ' FORTH SUBMENU ITEM (Second Menu)
> >> >> Set SubMenuItem = MenuItem.Controls.Add _
> >> >> (Type:=msoControlButton)
> >> >> With SubMenuItem
> >> >> .Caption = "Populate Charts"
> >> >> .FaceId = 433
> >> >> .OnAction = "Populate_Charts"
> >> >> End With
> >> >>
> >> >> ' THIRD MENU ITEM
> >> >> Set MenuItem = NewMenu.Controls.Add _
> >> >> (Type:=msoControlPopup)
> >> >> With MenuItem
> >> >> .Caption = "Chart Settings"
> >> >> .BeginGroup = True
> >> >> End With
> >> >>
> >> >> ' FIRST SUBMENU ITEM (Third Menu)
> >> >> Set SubMenuItem = MenuItem.Controls.Add _
> >> >> (Type:=msoControlButton)
> >> >> With SubMenuItem
> >> >> .Caption = "Resize Chart"
> >> >> .FaceId = 442
> >> >> .OnAction = "Resize_Chart"
> >> >> End With
> >> >>
> >> >> ' SECOND SUBMENU ITEM (Third Menu)
> >> >> Set SubMenuItem = MenuItem.Controls.Add _
> >> >> (Type:=msoControlButton)
> >> >> With SubMenuItem
> >> >> .Caption = "Set Print Range"
> >> >> .FaceId = 364
> >> >> .OnAction = "PrintRange"
> >> >> End With
> >> >> ' THIRD SUBMENU ITEM (Third Menu)
> >> >> Set SubMenuItem = MenuItem.Controls.Add _
> >> >> (Type:=msoControlButton)
> >> >> With SubMenuItem
> >> >> .Caption = "Chart Options"
> >> >> .FaceId = 435
> >> >> .OnAction = "ShowUserForm1"
> >> >> End With
> >> >> ' FOURTH SUBMENU ITEM (Third Menu)
> >> >> Set SubMenuItem = MenuItem.Controls.Add _
> >> >> (Type:=msoControlButton)
> >> >> With SubMenuItem
> >> >> .Caption = "Input Header and Footer Description"
> >> >> .FaceId = 237
> >> >> .OnAction = "Go_to_Titles"
> >> >> End With
> >> >>
> >> >> ' FORTH MENU ITEM
> >> >> Set MenuItem = NewMenu.Controls.Add _
> >> >> (Type:=msoControlButton)
> >> >> With MenuItem
> >> >> .Caption = "Chart Builder Help"
> >> >> .BeginGroup = True
> >> >> .OnAction = "ShowHelpForm"
> >> >> End With
> >> >>
> >> >> End Sub
> >> >>
> >> >> Sub DeleteMenu()
> >> >> On Error Resume Next
> >> >> CommandBars(1).Controls("Chart Builder Menu").Delete
> >> >> End Sub
> >> >>
> >> >>
> >> >>
> >> >> Private Sub Workbook_Open()
> >> >> Run ([CreateMenu])
> >> >> End Sub
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
>
>

[Joel Mills]

Bob this does just what I wanted.

With one exception. I had an event before the workbook close, that deleted
the menu. Now the menu isn't deleted until I close excel. Below is the
Code I had in "ThisWorkbook" Before it had "DeleteMenu", but I got an error
upon opening the Workbook after coping your code. I revised it to
"DeleteMenu (CBId) and now I don't get the error, but the menu remains
unless I close excel and reopen it. Before I could exit the Workbook and
the menu was deleted. I t didn't appear in any of the other open workbooks.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
DeleteMenu (CBId)
ResetCellMenu
End Sub



"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> Joel,
>
> Try again with this small mod, it seems to work okay.
>
> Sub CreateMenu()
> UpdateCB 1
> UpdateCB 2
> End Sub
>
> Sub UpdateCB(CBId As Long)
> Dim NewMenu As CommandBarPopup
>
> ' Delete the menu if it already exists
> Call DeleteMenu(CBId)
>
> ' Find the Help Menu
> Set HelpMenu = CommandBars(CBId).FindControl(ID:=30010)
>
> If HelpMenu Is Nothing Then
> ' Add the menu to the end
> Set NewMenu = CommandBars(CBId).Controls.Add _
> (Type:=msoControlPopup, _
> Temporary:=True)
> Else
> ' Add the menu before Help
> Set NewMenu = CommandBars(CBId).Controls.Add _
> (Type:=msoControlPopup, _
> Before:=HelpMenu.Index, _
> Temporary:=True)
> End If
>
> ' Add a caption for the menu
> NewMenu.Caption = "&Chart Builder Menu"
>
> ' FIRST MENU ITEM
> Set MenuItem = NewMenu.Controls.Add _
> (Type:=msoControlPopup)
> With MenuItem
> .Caption = "Retreive Exported P3 File"
> .BeginGroup = True
> End With
>
> ' FIRST SUBMENU ITEM (First Menu)
> Set SubMenuItem = MenuItem.Controls.Add _
> (Type:=msoControlButton)
> With SubMenuItem
> .Caption = "From Local Drive"
> .FaceId = 1021
> .OnAction = "ExportCdrive"
> End With
>
> ' SECOND SUBMENU ITEM (First Menu)
> Set SubMenuItem = MenuItem.Controls.Add _
> (Type:=msoControlButton)
> With SubMenuItem
> .Caption = "From Network Drive"
> .FaceId = 140
> .OnAction = "ExportNet"
> End With
>
> ' SECOND MENU ITEM
> Set MenuItem = NewMenu.Controls.Add _
> (Type:=msoControlPopup)
> With MenuItem
> .Caption = "Populate (Pivot Tables and Charts)"
> .BeginGroup = True
> End With
>
> ' FIRST SUBMENU ITEM (Second Menu)
> Set SubMenuItem = MenuItem.Controls.Add _
> (Type:=msoControlButton)
> With SubMenuItem
> .Caption = "Copy Exported File (DBF)"
> .FaceId = 1642
> .OnAction = "CopyData"
> End With
> 'SECOND SUBMENU ITEM (Second Menu)
> Set SubMenuItem = MenuItem.Controls.Add _
> (Type:=msoControlButton)
> With SubMenuItem
> .Caption = "Create Database"
> .FaceId = 333
> .OnAction = "Cleanup"
> End With
> ' THIRD SUBMENU ITEM (Second Menu)
> Set SubMenuItem = MenuItem.Controls.Add _
> (Type:=msoControlButton)
> With SubMenuItem
> .Caption = "Create Pivot Tables"
> .FaceId = 657
> .OnAction = "CreatePivotTable"
> End With
> ' FORTH SUBMENU ITEM (Second Menu)
> Set SubMenuItem = MenuItem.Controls.Add _
> (Type:=msoControlButton)
> With SubMenuItem
> .Caption = "Populate Charts"
> .FaceId = 433
> .OnAction = "Populate_Charts"
> End With
>
> ' THIRD MENU ITEM
> Set MenuItem = NewMenu.Controls.Add _
> (Type:=msoControlPopup)
> With MenuItem
> .Caption = "Chart Settings"
> .BeginGroup = True
> End With
>
> ' FIRST SUBMENU ITEM (Third Menu)
> Set SubMenuItem = MenuItem.Controls.Add _
> (Type:=msoControlButton)
> With SubMenuItem
> .Caption = "Resize Chart"
> .FaceId = 442
> .OnAction = "Resize_Chart"
> End With
>
> ' SECOND SUBMENU ITEM (Third Menu)
> Set SubMenuItem = MenuItem.Controls.Add _
> (Type:=msoControlButton)
> With SubMenuItem
> .Caption = "Set Print Range"
> .FaceId = 364
> .OnAction = "PrintRange"
> End With
> ' THIRD SUBMENU ITEM (Third Menu)
> Set SubMenuItem = MenuItem.Controls.Add _
> (Type:=msoControlButton)
> With SubMenuItem
> .Caption = "Chart Options"
> .FaceId = 435
> .OnAction = "ShowUserForm1"
> End With
> ' FOURTH SUBMENU ITEM (Third Menu)
> Set SubMenuItem = MenuItem.Controls.Add _
> (Type:=msoControlButton)
> With SubMenuItem
> .Caption = "Input Header and Footer Description"
> .FaceId = 237
> .OnAction = "Go_to_Titles"
> End With
>
> ' FORTH MENU ITEM
> Set MenuItem = NewMenu.Controls.Add _
> (Type:=msoControlButton)
> With MenuItem
> .Caption = "Chart Builder Help"
> .BeginGroup = True
> .OnAction = "ShowHelpForm"
> End With
>
> End Sub
>
> Sub DeleteMenu(CBId As Long)
> On Error Resume Next
> CommandBars(CBId).Controls("Chart Builder Menu").Delete
> End Sub
>
>
>
>
>
> --
> HTH
>
> Bob Phillips
>
> "Joel Mills" <[email protected]> wrote in message
> news:[email protected]...
>> This still doesn't add a "Chart Menu Bar". What am I missing? I changed
>> CommandBars(1) to CommandBars(CBId) on the If then Else statements and
>> now
>> it creates the Chart Menu Bar and not the Menu Bar. This must be very
> close
>> to the code I'm looking for.
>>
>> Sub UpdateCB(CBId As Long)
>> Dim NewMenu As CommandBarPopup
>>
>> ' Delete the menu if it already exists
>> Call DeleteMenu
>>
>> ' Find the Help Menu
>> Set HelpMenu = CommandBars(CBId).FindControl(ID:=30010)
>>
>> If HelpMenu Is Nothing Then
>> ' Add the menu to the end
>> Set NewMenu = CommandBars(CBId).Controls.Add _
>> (Type:=msoControlPopup, _
>> Temporary:=True)
>> Else
>> ' Add the menu before Help
>> Set NewMenu = CommandBars(CBId).Controls.Add _
>> (Type:=msoControlPopup, _
>> Before:=HelpMenu.Index, _
>> Temporary:=True)
>>
>> "Bob Phillips" <[email protected]> wrote in message
>> news:[email protected]...
>> > Try this
>> >
>> > Sub CreateMenu
>> > UpdateCB 1
>> > UpdateCB 2
>> > End Sub
>> >
>> > Sub UpdateCB(CBId as Long)
>> > Dim NewMenu As CommandBarPopup
>> >
>> > ' Delete the menu if it already exists
>> > Call DeleteMenu
>> >
>> > ' Find the Help Menu
>> > Set HelpMenu = CommandBars(CBId).FindControl(ID:=30010)
>> >
>> > If HelpMenu Is Nothing Then
>> > ' Add the menu to the end
>> > Set NewMenu = CommandBars(1).Controls.Add _
>> > (Type:=msoControlPopup, _
>> > Temporary:=True)
>> > Else
>> > ' Add the menu before Help
>> > Set NewMenu = CommandBars(1).Controls.Add _
>> > (Type:=msoControlPopup, _
>> > Before:=HelpMenu.Index, _
>> > Temporary:=True)
>> > End If
>> >
>> > ' Add a caption for the menu
>> > NewMenu.Caption = "&Chart Builder Menu"
>> >
>> > ' FIRST MENU ITEM
>> > Set MenuItem = NewMenu.Controls.Add _
>> > (Type:=msoControlPopup)
>> > With MenuItem
>> > .Caption = "Retreive Exported P3 File"
>> > .BeginGroup = True
>> > End With
>> >
>> > ' FIRST SUBMENU ITEM (First Menu)
>> > Set SubMenuItem = MenuItem.Controls.Add _
>> > (Type:=msoControlButton)
>> > With SubMenuItem
>> > .Caption = "From Local Drive"
>> > .FaceId = 1021
>> > .OnAction = "ExportCdrive"
>> > End With
>> >
>> > ' SECOND SUBMENU ITEM (First Menu)
>> > Set SubMenuItem = MenuItem.Controls.Add _
>> > (Type:=msoControlButton)
>> > With SubMenuItem
>> > .Caption = "From Network Drive"
>> > .FaceId = 140
>> > .OnAction = "ExportNet"
>> > End With
>> >
>> > ' SECOND MENU ITEM
>> > Set MenuItem = NewMenu.Controls.Add _
>> > (Type:=msoControlPopup)
>> > With MenuItem
>> > .Caption = "Populate (Pivot Tables and Charts)"
>> > .BeginGroup = True
>> > End With
>> >
>> > ' FIRST SUBMENU ITEM (Second Menu)
>> > Set SubMenuItem = MenuItem.Controls.Add _
>> > (Type:=msoControlButton)
>> > With SubMenuItem
>> > .Caption = "Copy Exported File (DBF)"
>> > .FaceId = 1642
>> > .OnAction = "CopyData"
>> > End With
>> > 'SECOND SUBMENU ITEM (Second Menu)
>> > Set SubMenuItem = MenuItem.Controls.Add _
>> > (Type:=msoControlButton)
>> > With SubMenuItem
>> > .Caption = "Create Database"
>> > .FaceId = 333
>> > .OnAction = "Cleanup"
>> > End With
>> > ' THIRD SUBMENU ITEM (Second Menu)
>> > Set SubMenuItem = MenuItem.Controls.Add _
>> > (Type:=msoControlButton)
>> > With SubMenuItem
>> > .Caption = "Create Pivot Tables"
>> > .FaceId = 657
>> > .OnAction = "CreatePivotTable"
>> > End With
>> > ' FORTH SUBMENU ITEM (Second Menu)
>> > Set SubMenuItem = MenuItem.Controls.Add _
>> > (Type:=msoControlButton)
>> > With SubMenuItem
>> > .Caption = "Populate Charts"
>> > .FaceId = 433
>> > .OnAction = "Populate_Charts"
>> > End With
>> >
>> > ' THIRD MENU ITEM
>> > Set MenuItem = NewMenu.Controls.Add _
>> > (Type:=msoControlPopup)
>> > With MenuItem
>> > .Caption = "Chart Settings"
>> > .BeginGroup = True
>> > End With
>> >
>> > ' FIRST SUBMENU ITEM (Third Menu)
>> > Set SubMenuItem = MenuItem.Controls.Add _
>> > (Type:=msoControlButton)
>> > With SubMenuItem
>> > .Caption = "Resize Chart"
>> > .FaceId = 442
>> > .OnAction = "Resize_Chart"
>> > End With
>> >
>> > ' SECOND SUBMENU ITEM (Third Menu)
>> > Set SubMenuItem = MenuItem.Controls.Add _
>> > (Type:=msoControlButton)
>> > With SubMenuItem
>> > .Caption = "Set Print Range"
>> > .FaceId = 364
>> > .OnAction = "PrintRange"
>> > End With
>> > ' THIRD SUBMENU ITEM (Third Menu)
>> > Set SubMenuItem = MenuItem.Controls.Add _
>> > (Type:=msoControlButton)
>> > With SubMenuItem
>> > .Caption = "Chart Options"
>> > .FaceId = 435
>> > .OnAction = "ShowUserForm1"
>> > End With
>> > ' FOURTH SUBMENU ITEM (Third Menu)
>> > Set SubMenuItem = MenuItem.Controls.Add _
>> > (Type:=msoControlButton)
>> > With SubMenuItem
>> > .Caption = "Input Header and Footer Description"
>> > .FaceId = 237
>> > .OnAction = "Go_to_Titles"
>> > End With
>> >
>> > ' FORTH MENU ITEM
>> > Set MenuItem = NewMenu.Controls.Add _
>> > (Type:=msoControlButton)
>> > With MenuItem
>> > .Caption = "Chart Builder Help"
>> > .BeginGroup = True
>> > .OnAction = "ShowHelpForm"
>> > End With
>> >
>> > End Sub
>> >
>> > Sub DeleteMenu()
>> > On Error Resume Next
>> > CommandBars(1).Controls("Chart Builder Menu").Delete
>> > CommandBars(2).Controls("Chart Builder Menu").Delete
>> > End Sub
>> >
>> >
>> > --
>> > HTH
>> >
>> > Bob Phillips
>> >
>> > "Joel Mills" <[email protected]> wrote in message
>> > news:[email protected]...
>> >> Bob, Thanks for the reply, but I'm not very experience with VBA, this
> is
>> > my
>> >> first project. I am using it to learn VBA. I probably should have
>> > mentioned
>> >> that in this post. I did a google search and determined that there
>> >> are
>> > menu
>> >> bars and chart menu bars, but wasn't able to grasp how to get both
>> >> from
>> > the
>> >> same code. I'm still not sure how to revise my code.
>> >>
>> >>
>> >> "Bob Phillips" <[email protected]> wrote in message
>> >> news:%[email protected]...
>> >> > Repeat it for Commandbars(2). I would parameterise it and just pass
> the
>> > CB
>> >> > id to the routine in a caller routine.
>> >> >
>> >> > --
>> >> > HTH
>> >> >
>> >> > Bob Phillips
>> >> >
>> >> > "Joel Mills" <[email protected]> wrote in message
>> >> > news:[email protected]...
>> >> >> I have created a menu bar and would like for it to show up in the
>> >> >> chart
>> >> >> sheets too. Below is the code for my menu. I'm not sure how to
>> >> >> revise
>> >> >> it
>> >> >> to also create a "Chart Menu Bar". When I close the workbook this
>> >> >> menu
>> >> >> is
>> >> >> deleted. I'm not sure if the sub Proceedure to delete the menu
> should
>> >> > also
>> >> >> be revised. Any help would be appreciated.
>> >> >>
>> >> >>
>> >> >> Sub CreateMenu()
>> >> >> Dim NewMenu As CommandBarPopup
>> >> >>
>> >> >> ' Delete the menu if it already exists
>> >> >> Call DeleteMenu
>> >> >>
>> >> >> ' Find the Help Menu
>> >> >> Set HelpMenu = CommandBars(1).FindControl(ID:=30010)
>> >> >>
>> >> >> If HelpMenu Is Nothing Then
>> >> >> ' Add the menu to the end
>> >> >> Set NewMenu = CommandBars(1).Controls.Add _
>> >> >> (Type:=msoControlPopup, _
>> >> >> Temporary:=True)
>> >> >> Else
>> >> >> ' Add the menu before Help
>> >> >> Set NewMenu = CommandBars(1).Controls.Add _
>> >> >> (Type:=msoControlPopup, _
>> >> >> Before:=HelpMenu.Index, _
>> >> >> Temporary:=True)
>> >> >> End If
>> >> >>
>> >> >> ' Add a caption for the menu
>> >> >> NewMenu.Caption = "&Chart Builder Menu"
>> >> >>
>> >> >> ' FIRST MENU ITEM
>> >> >> Set MenuItem = NewMenu.Controls.Add _
>> >> >> (Type:=msoControlPopup)
>> >> >> With MenuItem
>> >> >> .Caption = "Retreive Exported P3 File"
>> >> >> .BeginGroup = True
>> >> >> End With
>> >> >>
>> >> >> ' FIRST SUBMENU ITEM (First Menu)
>> >> >> Set SubMenuItem = MenuItem.Controls.Add _
>> >> >> (Type:=msoControlButton)
>> >> >> With SubMenuItem
>> >> >> .Caption = "From Local Drive"
>> >> >> .FaceId = 1021
>> >> >> .OnAction = "ExportCdrive"
>> >> >> End With
>> >> >>
>> >> >> ' SECOND SUBMENU ITEM (First Menu)
>> >> >> Set SubMenuItem = MenuItem.Controls.Add _
>> >> >> (Type:=msoControlButton)
>> >> >> With SubMenuItem
>> >> >> .Caption = "From Network Drive"
>> >> >> .FaceId = 140
>> >> >> .OnAction = "ExportNet"
>> >> >> End With
>> >> >>
>> >> >> ' SECOND MENU ITEM
>> >> >> Set MenuItem = NewMenu.Controls.Add _
>> >> >> (Type:=msoControlPopup)
>> >> >> With MenuItem
>> >> >> .Caption = "Populate (Pivot Tables and Charts)"
>> >> >> .BeginGroup = True
>> >> >> End With
>> >> >>
>> >> >> ' FIRST SUBMENU ITEM (Second Menu)
>> >> >> Set SubMenuItem = MenuItem.Controls.Add _
>> >> >> (Type:=msoControlButton)
>> >> >> With SubMenuItem
>> >> >> .Caption = "Copy Exported File (DBF)"
>> >> >> .FaceId = 1642
>> >> >> .OnAction = "CopyData"
>> >> >> End With
>> >> >> 'SECOND SUBMENU ITEM (Second Menu)
>> >> >> Set SubMenuItem = MenuItem.Controls.Add _
>> >> >> (Type:=msoControlButton)
>> >> >> With SubMenuItem
>> >> >> .Caption = "Create Database"
>> >> >> .FaceId = 333
>> >> >> .OnAction = "Cleanup"
>> >> >> End With
>> >> >> ' THIRD SUBMENU ITEM (Second Menu)
>> >> >> Set SubMenuItem = MenuItem.Controls.Add _
>> >> >> (Type:=msoControlButton)
>> >> >> With SubMenuItem
>> >> >> .Caption = "Create Pivot Tables"
>> >> >> .FaceId = 657
>> >> >> .OnAction = "CreatePivotTable"
>> >> >> End With
>> >> >> ' FORTH SUBMENU ITEM (Second Menu)
>> >> >> Set SubMenuItem = MenuItem.Controls.Add _
>> >> >> (Type:=msoControlButton)
>> >> >> With SubMenuItem
>> >> >> .Caption = "Populate Charts"
>> >> >> .FaceId = 433
>> >> >> .OnAction = "Populate_Charts"
>> >> >> End With
>> >> >>
>> >> >> ' THIRD MENU ITEM
>> >> >> Set MenuItem = NewMenu.Controls.Add _
>> >> >> (Type:=msoControlPopup)
>> >> >> With MenuItem
>> >> >> .Caption = "Chart Settings"
>> >> >> .BeginGroup = True
>> >> >> End With
>> >> >>
>> >> >> ' FIRST SUBMENU ITEM (Third Menu)
>> >> >> Set SubMenuItem = MenuItem.Controls.Add _
>> >> >> (Type:=msoControlButton)
>> >> >> With SubMenuItem
>> >> >> .Caption = "Resize Chart"
>> >> >> .FaceId = 442
>> >> >> .OnAction = "Resize_Chart"
>> >> >> End With
>> >> >>
>> >> >> ' SECOND SUBMENU ITEM (Third Menu)
>> >> >> Set SubMenuItem = MenuItem.Controls.Add _
>> >> >> (Type:=msoControlButton)
>> >> >> With SubMenuItem
>> >> >> .Caption = "Set Print Range"
>> >> >> .FaceId = 364
>> >> >> .OnAction = "PrintRange"
>> >> >> End With
>> >> >> ' THIRD SUBMENU ITEM (Third Menu)
>> >> >> Set SubMenuItem = MenuItem.Controls.Add _
>> >> >> (Type:=msoControlButton)
>> >> >> With SubMenuItem
>> >> >> .Caption = "Chart Options"
>> >> >> .FaceId = 435
>> >> >> .OnAction = "ShowUserForm1"
>> >> >> End With
>> >> >> ' FOURTH SUBMENU ITEM (Third Menu)
>> >> >> Set SubMenuItem = MenuItem.Controls.Add _
>> >> >> (Type:=msoControlButton)
>> >> >> With SubMenuItem
>> >> >> .Caption = "Input Header and Footer Description"
>> >> >> .FaceId = 237
>> >> >> .OnAction = "Go_to_Titles"
>> >> >> End With
>> >> >>
>> >> >> ' FORTH MENU ITEM
>> >> >> Set MenuItem = NewMenu.Controls.Add _
>> >> >> (Type:=msoControlButton)
>> >> >> With MenuItem
>> >> >> .Caption = "Chart Builder Help"
>> >> >> .BeginGroup = True
>> >> >> .OnAction = "ShowHelpForm"
>> >> >> End With
>> >> >>
>> >> >> End Sub
>> >> >>
>> >> >> Sub DeleteMenu()
>> >> >> On Error Resume Next
>> >> >> CommandBars(1).Controls("Chart Builder Menu").Delete
>> >> >> End Sub
>> >> >>
>> >> >>
>> >> >>
>> >> >> Private Sub Workbook_Open()
>> >> >> Run ([CreateMenu])
>> >> >> End Sub
>> >> >>
>> >> >>
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>>
>
>

[Bob Phillips]

This event doesn't know CBId, so you will need to tell the id numbers

Private Sub Workbook_BeforeClose(Cancel As Boolean)
DeleteMenu 1
DeleteMenu 2
ResetCellMenu
End Sub


--
HTH

Bob Phillips

"Joel Mills" <[email protected]> wrote in message
news:[email protected]...
> Bob this does just what I wanted.
>
> With one exception. I had an event before the workbook close, that
deleted
> the menu. Now the menu isn't deleted until I close excel. Below is the
> Code I had in "ThisWorkbook" Before it had "DeleteMenu", but I got an
error
> upon opening the Workbook after coping your code. I revised it to
> "DeleteMenu (CBId) and now I don't get the error, but the menu remains
> unless I close excel and reopen it. Before I could exit the Workbook and
> the menu was deleted. I t didn't appear in any of the other open
workbooks.
>
> Private Sub Workbook_BeforeClose(Cancel As Boolean)
> DeleteMenu (CBId)
> ResetCellMenu
> End Sub
>
>
>
> "Bob Phillips" <[email protected]> wrote in message
> news:[email protected]...
> > Joel,
> >
> > Try again with this small mod, it seems to work okay.
> >
> > Sub CreateMenu()
> > UpdateCB 1
> > UpdateCB 2
> > End Sub
> >
> > Sub UpdateCB(CBId As Long)
> > Dim NewMenu As CommandBarPopup
> >
> > ' Delete the menu if it already exists
> > Call DeleteMenu(CBId)
> >
> > ' Find the Help Menu
> > Set HelpMenu = CommandBars(CBId).FindControl(ID:=30010)
> >
> > If HelpMenu Is Nothing Then
> > ' Add the menu to the end
> > Set NewMenu = CommandBars(CBId).Controls.Add _
> > (Type:=msoControlPopup, _
> > Temporary:=True)
> > Else
> > ' Add the menu before Help
> > Set NewMenu = CommandBars(CBId).Controls.Add _
> > (Type:=msoControlPopup, _
> > Before:=HelpMenu.Index, _
> > Temporary:=True)
> > End If
> >
> > ' Add a caption for the menu
> > NewMenu.Caption = "&Chart Builder Menu"
> >
> > ' FIRST MENU ITEM
> > Set MenuItem = NewMenu.Controls.Add _
> > (Type:=msoControlPopup)
> > With MenuItem
> > .Caption = "Retreive Exported P3 File"
> > .BeginGroup = True
> > End With
> >
> > ' FIRST SUBMENU ITEM (First Menu)
> > Set SubMenuItem = MenuItem.Controls.Add _
> > (Type:=msoControlButton)
> > With SubMenuItem
> > .Caption = "From Local Drive"
> > .FaceId = 1021
> > .OnAction = "ExportCdrive"
> > End With
> >
> > ' SECOND SUBMENU ITEM (First Menu)
> > Set SubMenuItem = MenuItem.Controls.Add _
> > (Type:=msoControlButton)
> > With SubMenuItem
> > .Caption = "From Network Drive"
> > .FaceId = 140
> > .OnAction = "ExportNet"
> > End With
> >
> > ' SECOND MENU ITEM
> > Set MenuItem = NewMenu.Controls.Add _
> > (Type:=msoControlPopup)
> > With MenuItem
> > .Caption = "Populate (Pivot Tables and Charts)"
> > .BeginGroup = True
> > End With
> >
> > ' FIRST SUBMENU ITEM (Second Menu)
> > Set SubMenuItem = MenuItem.Controls.Add _
> > (Type:=msoControlButton)
> > With SubMenuItem
> > .Caption = "Copy Exported File (DBF)"
> > .FaceId = 1642
> > .OnAction = "CopyData"
> > End With
> > 'SECOND SUBMENU ITEM (Second Menu)
> > Set SubMenuItem = MenuItem.Controls.Add _
> > (Type:=msoControlButton)
> > With SubMenuItem
> > .Caption = "Create Database"
> > .FaceId = 333
> > .OnAction = "Cleanup"
> > End With
> > ' THIRD SUBMENU ITEM (Second Menu)
> > Set SubMenuItem = MenuItem.Controls.Add _
> > (Type:=msoControlButton)
> > With SubMenuItem
> > .Caption = "Create Pivot Tables"
> > .FaceId = 657
> > .OnAction = "CreatePivotTable"
> > End With
> > ' FORTH SUBMENU ITEM (Second Menu)
> > Set SubMenuItem = MenuItem.Controls.Add _
> > (Type:=msoControlButton)
> > With SubMenuItem
> > .Caption = "Populate Charts"
> > .FaceId = 433
> > .OnAction = "Populate_Charts"
> > End With
> >
> > ' THIRD MENU ITEM
> > Set MenuItem = NewMenu.Controls.Add _
> > (Type:=msoControlPopup)
> > With MenuItem
> > .Caption = "Chart Settings"
> > .BeginGroup = True
> > End With
> >
> > ' FIRST SUBMENU ITEM (Third Menu)
> > Set SubMenuItem = MenuItem.Controls.Add _
> > (Type:=msoControlButton)
> > With SubMenuItem
> > .Caption = "Resize Chart"
> > .FaceId = 442
> > .OnAction = "Resize_Chart"
> > End With
> >
> > ' SECOND SUBMENU ITEM (Third Menu)
> > Set SubMenuItem = MenuItem.Controls.Add _
> > (Type:=msoControlButton)
> > With SubMenuItem
> > .Caption = "Set Print Range"
> > .FaceId = 364
> > .OnAction = "PrintRange"
> > End With
> > ' THIRD SUBMENU ITEM (Third Menu)
> > Set SubMenuItem = MenuItem.Controls.Add _
> > (Type:=msoControlButton)
> > With SubMenuItem
> > .Caption = "Chart Options"
> > .FaceId = 435
> > .OnAction = "ShowUserForm1"
> > End With
> > ' FOURTH SUBMENU ITEM (Third Menu)
> > Set SubMenuItem = MenuItem.Controls.Add _
> > (Type:=msoControlButton)
> > With SubMenuItem
> > .Caption = "Input Header and Footer Description"
> > .FaceId = 237
> > .OnAction = "Go_to_Titles"
> > End With
> >
> > ' FORTH MENU ITEM
> > Set MenuItem = NewMenu.Controls.Add _
> > (Type:=msoControlButton)
> > With MenuItem
> > .Caption = "Chart Builder Help"
> > .BeginGroup = True
> > .OnAction = "ShowHelpForm"
> > End With
> >
> > End Sub
> >
> > Sub DeleteMenu(CBId As Long)
> > On Error Resume Next
> > CommandBars(CBId).Controls("Chart Builder Menu").Delete
> > End Sub
> >
> >
> >
> >
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > "Joel Mills" <[email protected]> wrote in message
> > news:[email protected]...
> >> This still doesn't add a "Chart Menu Bar". What am I missing? I
changed
> >> CommandBars(1) to CommandBars(CBId) on the If then Else statements and
> >> now
> >> it creates the Chart Menu Bar and not the Menu Bar. This must be very
> > close
> >> to the code I'm looking for.
> >>
> >> Sub UpdateCB(CBId As Long)
> >> Dim NewMenu As CommandBarPopup
> >>
> >> ' Delete the menu if it already exists
> >> Call DeleteMenu
> >>
> >> ' Find the Help Menu
> >> Set HelpMenu = CommandBars(CBId).FindControl(ID:=30010)
> >>
> >> If HelpMenu Is Nothing Then
> >> ' Add the menu to the end
> >> Set NewMenu = CommandBars(CBId).Controls.Add _
> >> (Type:=msoControlPopup, _
> >> Temporary:=True)
> >> Else
> >> ' Add the menu before Help
> >> Set NewMenu = CommandBars(CBId).Controls.Add _
> >> (Type:=msoControlPopup, _
> >> Before:=HelpMenu.Index, _
> >> Temporary:=True)
> >>
> >> "Bob Phillips" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > Try this
> >> >
> >> > Sub CreateMenu
> >> > UpdateCB 1
> >> > UpdateCB 2
> >> > End Sub
> >> >
> >> > Sub UpdateCB(CBId as Long)
> >> > Dim NewMenu As CommandBarPopup
> >> >
> >> > ' Delete the menu if it already exists
> >> > Call DeleteMenu
> >> >
> >> > ' Find the Help Menu
> >> > Set HelpMenu = CommandBars(CBId).FindControl(ID:=30010)
> >> >
> >> > If HelpMenu Is Nothing Then
> >> > ' Add the menu to the end
> >> > Set NewMenu = CommandBars(1).Controls.Add _
> >> > (Type:=msoControlPopup, _
> >> > Temporary:=True)
> >> > Else
> >> > ' Add the menu before Help
> >> > Set NewMenu = CommandBars(1).Controls.Add _
> >> > (Type:=msoControlPopup, _
> >> > Before:=HelpMenu.Index, _
> >> > Temporary:=True)
> >> > End If
> >> >
> >> > ' Add a caption for the menu
> >> > NewMenu.Caption = "&Chart Builder Menu"
> >> >
> >> > ' FIRST MENU ITEM
> >> > Set MenuItem = NewMenu.Controls.Add _
> >> > (Type:=msoControlPopup)
> >> > With MenuItem
> >> > .Caption = "Retreive Exported P3 File"
> >> > .BeginGroup = True
> >> > End With
> >> >
> >> > ' FIRST SUBMENU ITEM (First Menu)
> >> > Set SubMenuItem = MenuItem.Controls.Add _
> >> > (Type:=msoControlButton)
> >> > With SubMenuItem
> >> > .Caption = "From Local Drive"
> >> > .FaceId = 1021
> >> > .OnAction = "ExportCdrive"
> >> > End With
> >> >
> >> > ' SECOND SUBMENU ITEM (First Menu)
> >> > Set SubMenuItem = MenuItem.Controls.Add _
> >> > (Type:=msoControlButton)
> >> > With SubMenuItem
> >> > .Caption = "From Network Drive"
> >> > .FaceId = 140
> >> > .OnAction = "ExportNet"
> >> > End With
> >> >
> >> > ' SECOND MENU ITEM
> >> > Set MenuItem = NewMenu.Controls.Add _
> >> > (Type:=msoControlPopup)
> >> > With MenuItem
> >> > .Caption = "Populate (Pivot Tables and Charts)"
> >> > .BeginGroup = True
> >> > End With
> >> >
> >> > ' FIRST SUBMENU ITEM (Second Menu)
> >> > Set SubMenuItem = MenuItem.Controls.Add _
> >> > (Type:=msoControlButton)
> >> > With SubMenuItem
> >> > .Caption = "Copy Exported File (DBF)"
> >> > .FaceId = 1642
> >> > .OnAction = "CopyData"
> >> > End With
> >> > 'SECOND SUBMENU ITEM (Second Menu)
> >> > Set SubMenuItem = MenuItem.Controls.Add _
> >> > (Type:=msoControlButton)
> >> > With SubMenuItem
> >> > .Caption = "Create Database"
> >> > .FaceId = 333
> >> > .OnAction = "Cleanup"
> >> > End With
> >> > ' THIRD SUBMENU ITEM (Second Menu)
> >> > Set SubMenuItem = MenuItem.Controls.Add _
> >> > (Type:=msoControlButton)
> >> > With SubMenuItem
> >> > .Caption = "Create Pivot Tables"
> >> > .FaceId = 657
> >> > .OnAction = "CreatePivotTable"
> >> > End With
> >> > ' FORTH SUBMENU ITEM (Second Menu)
> >> > Set SubMenuItem = MenuItem.Controls.Add _
> >> > (Type:=msoControlButton)
> >> > With SubMenuItem
> >> > .Caption = "Populate Charts"
> >> > .FaceId = 433
> >> > .OnAction = "Populate_Charts"
> >> > End With
> >> >
> >> > ' THIRD MENU ITEM
> >> > Set MenuItem = NewMenu.Controls.Add _
> >> > (Type:=msoControlPopup)
> >> > With MenuItem
> >> > .Caption = "Chart Settings"
> >> > .BeginGroup = True
> >> > End With
> >> >
> >> > ' FIRST SUBMENU ITEM (Third Menu)
> >> > Set SubMenuItem = MenuItem.Controls.Add _
> >> > (Type:=msoControlButton)
> >> > With SubMenuItem
> >> > .Caption = "Resize Chart"
> >> > .FaceId = 442
> >> > .OnAction = "Resize_Chart"
> >> > End With
> >> >
> >> > ' SECOND SUBMENU ITEM (Third Menu)
> >> > Set SubMenuItem = MenuItem.Controls.Add _
> >> > (Type:=msoControlButton)
> >> > With SubMenuItem
> >> > .Caption = "Set Print Range"
> >> > .FaceId = 364
> >> > .OnAction = "PrintRange"
> >> > End With
> >> > ' THIRD SUBMENU ITEM (Third Menu)
> >> > Set SubMenuItem = MenuItem.Controls.Add _
> >> > (Type:=msoControlButton)
> >> > With SubMenuItem
> >> > .Caption = "Chart Options"
> >> > .FaceId = 435
> >> > .OnAction = "ShowUserForm1"
> >> > End With
> >> > ' FOURTH SUBMENU ITEM (Third Menu)
> >> > Set SubMenuItem = MenuItem.Controls.Add _
> >> > (Type:=msoControlButton)
> >> > With SubMenuItem
> >> > .Caption = "Input Header and Footer Description"
> >> > .FaceId = 237
> >> > .OnAction = "Go_to_Titles"
> >> > End With
> >> >
> >> > ' FORTH MENU ITEM
> >> > Set MenuItem = NewMenu.Controls.Add _
> >> > (Type:=msoControlButton)
> >> > With MenuItem
> >> > .Caption = "Chart Builder Help"
> >> > .BeginGroup = True
> >> > .OnAction = "ShowHelpForm"
> >> > End With
> >> >
> >> > End Sub
> >> >
> >> > Sub DeleteMenu()
> >> > On Error Resume Next
> >> > CommandBars(1).Controls("Chart Builder Menu").Delete
> >> > CommandBars(2).Controls("Chart Builder Menu").Delete
> >> > End Sub
> >> >
> >> >
> >> > --
> >> > HTH
> >> >
> >> > Bob Phillips
> >> >
> >> > "Joel Mills" <[email protected]> wrote in message
> >> > news:[email protected]...
> >> >> Bob, Thanks for the reply, but I'm not very experience with VBA,
this
> > is
> >> > my
> >> >> first project. I am using it to learn VBA. I probably should have
> >> > mentioned
> >> >> that in this post. I did a google search and determined that there
> >> >> are
> >> > menu
> >> >> bars and chart menu bars, but wasn't able to grasp how to get both
> >> >> from
> >> > the
> >> >> same code. I'm still not sure how to revise my code.
> >> >>
> >> >>
> >> >> "Bob Phillips" <[email protected]> wrote in message
> >> >> news:%[email protected]...
> >> >> > Repeat it for Commandbars(2). I would parameterise it and just
pass
> > the
> >> > CB
> >> >> > id to the routine in a caller routine.
> >> >> >
> >> >> > --
> >> >> > HTH
> >> >> >
> >> >> > Bob Phillips
> >> >> >
> >> >> > "Joel Mills" <[email protected]> wrote in message
> >> >> > news:[email protected]...
> >> >> >> I have created a menu bar and would like for it to show up in the
> >> >> >> chart
> >> >> >> sheets too. Below is the code for my menu. I'm not sure how to
> >> >> >> revise
> >> >> >> it
> >> >> >> to also create a "Chart Menu Bar". When I close the workbook
this
> >> >> >> menu
> >> >> >> is
> >> >> >> deleted. I'm not sure if the sub Proceedure to delete the menu
> > should
> >> >> > also
> >> >> >> be revised. Any help would be appreciated.
> >> >> >>
> >> >> >>
> >> >> >> Sub CreateMenu()
> >> >> >> Dim NewMenu As CommandBarPopup
> >> >> >>
> >> >> >> ' Delete the menu if it already exists
> >> >> >> Call DeleteMenu
> >> >> >>
> >> >> >> ' Find the Help Menu
> >> >> >> Set HelpMenu = CommandBars(1).FindControl(ID:=30010)
> >> >> >>
> >> >> >> If HelpMenu Is Nothing Then
> >> >> >> ' Add the menu to the end
> >> >> >> Set NewMenu = CommandBars(1).Controls.Add _
> >> >> >> (Type:=msoControlPopup, _
> >> >> >> Temporary:=True)
> >> >> >> Else
> >> >> >> ' Add the menu before Help
> >> >> >> Set NewMenu = CommandBars(1).Controls.Add _
> >> >> >> (Type:=msoControlPopup, _
> >> >> >> Before:=HelpMenu.Index, _
> >> >> >> Temporary:=True)
> >> >> >> End If
> >> >> >>
> >> >> >> ' Add a caption for the menu
> >> >> >> NewMenu.Caption = "&Chart Builder Menu"
> >> >> >>
> >> >> >> ' FIRST MENU ITEM
> >> >> >> Set MenuItem = NewMenu.Controls.Add _
> >> >> >> (Type:=msoControlPopup)
> >> >> >> With MenuItem
> >> >> >> .Caption = "Retreive Exported P3 File"
> >> >> >> .BeginGroup = True
> >> >> >> End With
> >> >> >>
> >> >> >> ' FIRST SUBMENU ITEM (First Menu)
> >> >> >> Set SubMenuItem = MenuItem.Controls.Add _
> >> >> >> (Type:=msoControlButton)
> >> >> >> With SubMenuItem
> >> >> >> .Caption = "From Local Drive"
> >> >> >> .FaceId = 1021
> >> >> >> .OnAction = "ExportCdrive"
> >> >> >> End With
> >> >> >>
> >> >> >> ' SECOND SUBMENU ITEM (First Menu)
> >> >> >> Set SubMenuItem = MenuItem.Controls.Add _
> >> >> >> (Type:=msoControlButton)
> >> >> >> With SubMenuItem
> >> >> >> .Caption = "From Network Drive"
> >> >> >> .FaceId = 140
> >> >> >> .OnAction = "ExportNet"
> >> >> >> End With
> >> >> >>
> >> >> >> ' SECOND MENU ITEM
> >> >> >> Set MenuItem = NewMenu.Controls.Add _
> >> >> >> (Type:=msoControlPopup)
> >> >> >> With MenuItem
> >> >> >> .Caption = "Populate (Pivot Tables and Charts)"
> >> >> >> .BeginGroup = True
> >> >> >> End With
> >> >> >>
> >> >> >> ' FIRST SUBMENU ITEM (Second Menu)
> >> >> >> Set SubMenuItem = MenuItem.Controls.Add _
> >> >> >> (Type:=msoControlButton)
> >> >> >> With SubMenuItem
> >> >> >> .Caption = "Copy Exported File (DBF)"
> >> >> >> .FaceId = 1642
> >> >> >> .OnAction = "CopyData"
> >> >> >> End With
> >> >> >> 'SECOND SUBMENU ITEM (Second Menu)
> >> >> >> Set SubMenuItem = MenuItem.Controls.Add _
> >> >> >> (Type:=msoControlButton)
> >> >> >> With SubMenuItem
> >> >> >> .Caption = "Create Database"
> >> >> >> .FaceId = 333
> >> >> >> .OnAction = "Cleanup"
> >> >> >> End With
> >> >> >> ' THIRD SUBMENU ITEM (Second Menu)
> >> >> >> Set SubMenuItem = MenuItem.Controls.Add _
> >> >> >> (Type:=msoControlButton)
> >> >> >> With SubMenuItem
> >> >> >> .Caption = "Create Pivot Tables"
> >> >> >> .FaceId = 657
> >> >> >> .OnAction = "CreatePivotTable"
> >> >> >> End With
> >> >> >> ' FORTH SUBMENU ITEM (Second Menu)
> >> >> >> Set SubMenuItem = MenuItem.Controls.Add _
> >> >> >> (Type:=msoControlButton)
> >> >> >> With SubMenuItem
> >> >> >> .Caption = "Populate Charts"
> >> >> >> .FaceId = 433
> >> >> >> .OnAction = "Populate_Charts"
> >> >> >> End With
> >> >> >>
> >> >> >> ' THIRD MENU ITEM
> >> >> >> Set MenuItem = NewMenu.Controls.Add _
> >> >> >> (Type:=msoControlPopup)
> >> >> >> With MenuItem
> >> >> >> .Caption = "Chart Settings"
> >> >> >> .BeginGroup = True
> >> >> >> End With
> >> >> >>
> >> >> >> ' FIRST SUBMENU ITEM (Third Menu)
> >> >> >> Set SubMenuItem = MenuItem.Controls.Add _
> >> >> >> (Type:=msoControlButton)
> >> >> >> With SubMenuItem
> >> >> >> .Caption = "Resize Chart"
> >> >> >> .FaceId = 442
> >> >> >> .OnAction = "Resize_Chart"
> >> >> >> End With
> >> >> >>
> >> >> >> ' SECOND SUBMENU ITEM (Third Menu)
> >> >> >> Set SubMenuItem = MenuItem.Controls.Add _
> >> >> >> (Type:=msoControlButton)
> >> >> >> With SubMenuItem
> >> >> >> .Caption = "Set Print Range"
> >> >> >> .FaceId = 364
> >> >> >> .OnAction = "PrintRange"
> >> >> >> End With
> >> >> >> ' THIRD SUBMENU ITEM (Third Menu)
> >> >> >> Set SubMenuItem = MenuItem.Controls.Add _
> >> >> >> (Type:=msoControlButton)
> >> >> >> With SubMenuItem
> >> >> >> .Caption = "Chart Options"
> >> >> >> .FaceId = 435
> >> >> >> .OnAction = "ShowUserForm1"
> >> >> >> End With
> >> >> >> ' FOURTH SUBMENU ITEM (Third Menu)
> >> >> >> Set SubMenuItem = MenuItem.Controls.Add _
> >> >> >> (Type:=msoControlButton)
> >> >> >> With SubMenuItem
> >> >> >> .Caption = "Input Header and Footer Description"
> >> >> >> .FaceId = 237
> >> >> >> .OnAction = "Go_to_Titles"
> >> >> >> End With
> >> >> >>
> >> >> >> ' FORTH MENU ITEM
> >> >> >> Set MenuItem = NewMenu.Controls.Add _
> >> >> >> (Type:=msoControlButton)
> >> >> >> With MenuItem
> >> >> >> .Caption = "Chart Builder Help"
> >> >> >> .BeginGroup = True
> >> >> >> .OnAction = "ShowHelpForm"
> >> >> >> End With
> >> >> >>
> >> >> >> End Sub
> >> >> >>
> >> >> >> Sub DeleteMenu()
> >> >> >> On Error Resume Next
> >> >> >> CommandBars(1).Controls("Chart Builder Menu").Delete
> >> >> >> End Sub
> >> >> >>
> >> >> >>
> >> >> >>
> >> >> >> Private Sub Workbook_Open()
> >> >> >> Run ([CreateMenu])
> >> >> >> End Sub
> >> >> >>
> >> >> >>
> >> >> >
> >> >> >
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
>
>

[Joel Mills]

Bob, thankyou for your help. I was able to figure out a solution to my
other post (See Below).

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.CommandBars(1).Controls("Chart Builder Menu").Delete
Application.CommandBars(2).Controls("Chart Builder Menu").Delete

End Sub
"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> Joel,
>
> Try again with this small mod, it seems to work okay.
>
> Sub CreateMenu()
> UpdateCB 1
> UpdateCB 2
> End Sub
>
> Sub UpdateCB(CBId As Long)
> Dim NewMenu As CommandBarPopup
>
> ' Delete the menu if it already exists
> Call DeleteMenu(CBId)
>
> ' Find the Help Menu
> Set HelpMenu = CommandBars(CBId).FindControl(ID:=30010)
>
> If HelpMenu Is Nothing Then
> ' Add the menu to the end
> Set NewMenu = CommandBars(CBId).Controls.Add _
> (Type:=msoControlPopup, _
> Temporary:=True)
> Else
> ' Add the menu before Help
> Set NewMenu = CommandBars(CBId).Controls.Add _
> (Type:=msoControlPopup, _
> Before:=HelpMenu.Index, _
> Temporary:=True)
> End If
>
> ' Add a caption for the menu
> NewMenu.Caption = "&Chart Builder Menu"
>
> ' FIRST MENU ITEM
> Set MenuItem = NewMenu.Controls.Add _
> (Type:=msoControlPopup)
> With MenuItem
> .Caption = "Retreive Exported P3 File"
> .BeginGroup = True
> End With
>
> ' FIRST SUBMENU ITEM (First Menu)
> Set SubMenuItem = MenuItem.Controls.Add _
> (Type:=msoControlButton)
> With SubMenuItem
> .Caption = "From Local Drive"
> .FaceId = 1021
> .OnAction = "ExportCdrive"
> End With
>
> ' SECOND SUBMENU ITEM (First Menu)
> Set SubMenuItem = MenuItem.Controls.Add _
> (Type:=msoControlButton)
> With SubMenuItem
> .Caption = "From Network Drive"
> .FaceId = 140
> .OnAction = "ExportNet"
> End With
>
> ' SECOND MENU ITEM
> Set MenuItem = NewMenu.Controls.Add _
> (Type:=msoControlPopup)
> With MenuItem
> .Caption = "Populate (Pivot Tables and Charts)"
> .BeginGroup = True
> End With
>
> ' FIRST SUBMENU ITEM (Second Menu)
> Set SubMenuItem = MenuItem.Controls.Add _
> (Type:=msoControlButton)
> With SubMenuItem
> .Caption = "Copy Exported File (DBF)"
> .FaceId = 1642
> .OnAction = "CopyData"
> End With
> 'SECOND SUBMENU ITEM (Second Menu)
> Set SubMenuItem = MenuItem.Controls.Add _
> (Type:=msoControlButton)
> With SubMenuItem
> .Caption = "Create Database"
> .FaceId = 333
> .OnAction = "Cleanup"
> End With
> ' THIRD SUBMENU ITEM (Second Menu)
> Set SubMenuItem = MenuItem.Controls.Add _
> (Type:=msoControlButton)
> With SubMenuItem
> .Caption = "Create Pivot Tables"
> .FaceId = 657
> .OnAction = "CreatePivotTable"
> End With
> ' FORTH SUBMENU ITEM (Second Menu)
> Set SubMenuItem = MenuItem.Controls.Add _
> (Type:=msoControlButton)
> With SubMenuItem
> .Caption = "Populate Charts"
> .FaceId = 433
> .OnAction = "Populate_Charts"
> End With
>
> ' THIRD MENU ITEM
> Set MenuItem = NewMenu.Controls.Add _
> (Type:=msoControlPopup)
> With MenuItem
> .Caption = "Chart Settings"
> .BeginGroup = True
> End With
>
> ' FIRST SUBMENU ITEM (Third Menu)
> Set SubMenuItem = MenuItem.Controls.Add _
> (Type:=msoControlButton)
> With SubMenuItem
> .Caption = "Resize Chart"
> .FaceId = 442
> .OnAction = "Resize_Chart"
> End With
>
> ' SECOND SUBMENU ITEM (Third Menu)
> Set SubMenuItem = MenuItem.Controls.Add _
> (Type:=msoControlButton)
> With SubMenuItem
> .Caption = "Set Print Range"
> .FaceId = 364
> .OnAction = "PrintRange"
> End With
> ' THIRD SUBMENU ITEM (Third Menu)
> Set SubMenuItem = MenuItem.Controls.Add _
> (Type:=msoControlButton)
> With SubMenuItem
> .Caption = "Chart Options"
> .FaceId = 435
> .OnAction = "ShowUserForm1"
> End With
> ' FOURTH SUBMENU ITEM (Third Menu)
> Set SubMenuItem = MenuItem.Controls.Add _
> (Type:=msoControlButton)
> With SubMenuItem
> .Caption = "Input Header and Footer Description"
> .FaceId = 237
> .OnAction = "Go_to_Titles"
> End With
>
> ' FORTH MENU ITEM
> Set MenuItem = NewMenu.Controls.Add _
> (Type:=msoControlButton)
> With MenuItem
> .Caption = "Chart Builder Help"
> .BeginGroup = True
> .OnAction = "ShowHelpForm"
> End With
>
> End Sub
>
> Sub DeleteMenu(CBId As Long)
> On Error Resume Next
> CommandBars(CBId).Controls("Chart Builder Menu").Delete
> End Sub
>
>
>
>
>
> --
> HTH
>
> Bob Phillips
>
> "Joel Mills" <[email protected]> wrote in message
> news:[email protected]...
>> This still doesn't add a "Chart Menu Bar". What am I missing? I changed
>> CommandBars(1) to CommandBars(CBId) on the If then Else statements and
>> now
>> it creates the Chart Menu Bar and not the Menu Bar. This must be very
> close
>> to the code I'm looking for.
>>
>> Sub UpdateCB(CBId As Long)
>> Dim NewMenu As CommandBarPopup
>>
>> ' Delete the menu if it already exists
>> Call DeleteMenu
>>
>> ' Find the Help Menu
>> Set HelpMenu = CommandBars(CBId).FindControl(ID:=30010)
>>
>> If HelpMenu Is Nothing Then
>> ' Add the menu to the end
>> Set NewMenu = CommandBars(CBId).Controls.Add _
>> (Type:=msoControlPopup, _
>> Temporary:=True)
>> Else
>> ' Add the menu before Help
>> Set NewMenu = CommandBars(CBId).Controls.Add _
>> (Type:=msoControlPopup, _
>> Before:=HelpMenu.Index, _
>> Temporary:=True)
>>
>> "Bob Phillips" <[email protected]> wrote in message
>> news:[email protected]...
>> > Try this
>> >
>> > Sub CreateMenu
>> > UpdateCB 1
>> > UpdateCB 2
>> > End Sub
>> >
>> > Sub UpdateCB(CBId as Long)
>> > Dim NewMenu As CommandBarPopup
>> >
>> > ' Delete the menu if it already exists
>> > Call DeleteMenu
>> >
>> > ' Find the Help Menu
>> > Set HelpMenu = CommandBars(CBId).FindControl(ID:=30010)
>> >
>> > If HelpMenu Is Nothing Then
>> > ' Add the menu to the end
>> > Set NewMenu = CommandBars(1).Controls.Add _
>> > (Type:=msoControlPopup, _
>> > Temporary:=True)
>> > Else
>> > ' Add the menu before Help
>> > Set NewMenu = CommandBars(1).Controls.Add _
>> > (Type:=msoControlPopup, _
>> > Before:=HelpMenu.Index, _
>> > Temporary:=True)
>> > End If
>> >
>> > ' Add a caption for the menu
>> > NewMenu.Caption = "&Chart Builder Menu"
>> >
>> > ' FIRST MENU ITEM
>> > Set MenuItem = NewMenu.Controls.Add _
>> > (Type:=msoControlPopup)
>> > With MenuItem
>> > .Caption = "Retreive Exported P3 File"
>> > .BeginGroup = True
>> > End With
>> >
>> > ' FIRST SUBMENU ITEM (First Menu)
>> > Set SubMenuItem = MenuItem.Controls.Add _
>> > (Type:=msoControlButton)
>> > With SubMenuItem
>> > .Caption = "From Local Drive"
>> > .FaceId = 1021
>> > .OnAction = "ExportCdrive"
>> > End With
>> >
>> > ' SECOND SUBMENU ITEM (First Menu)
>> > Set SubMenuItem = MenuItem.Controls.Add _
>> > (Type:=msoControlButton)
>> > With SubMenuItem
>> > .Caption = "From Network Drive"
>> > .FaceId = 140
>> > .OnAction = "ExportNet"
>> > End With
>> >
>> > ' SECOND MENU ITEM
>> > Set MenuItem = NewMenu.Controls.Add _
>> > (Type:=msoControlPopup)
>> > With MenuItem
>> > .Caption = "Populate (Pivot Tables and Charts)"
>> > .BeginGroup = True
>> > End With
>> >
>> > ' FIRST SUBMENU ITEM (Second Menu)
>> > Set SubMenuItem = MenuItem.Controls.Add _
>> > (Type:=msoControlButton)
>> > With SubMenuItem
>> > .Caption = "Copy Exported File (DBF)"
>> > .FaceId = 1642
>> > .OnAction = "CopyData"
>> > End With
>> > 'SECOND SUBMENU ITEM (Second Menu)
>> > Set SubMenuItem = MenuItem.Controls.Add _
>> > (Type:=msoControlButton)
>> > With SubMenuItem
>> > .Caption = "Create Database"
>> > .FaceId = 333
>> > .OnAction = "Cleanup"
>> > End With
>> > ' THIRD SUBMENU ITEM (Second Menu)
>> > Set SubMenuItem = MenuItem.Controls.Add _
>> > (Type:=msoControlButton)
>> > With SubMenuItem
>> > .Caption = "Create Pivot Tables"
>> > .FaceId = 657
>> > .OnAction = "CreatePivotTable"
>> > End With
>> > ' FORTH SUBMENU ITEM (Second Menu)
>> > Set SubMenuItem = MenuItem.Controls.Add _
>> > (Type:=msoControlButton)
>> > With SubMenuItem
>> > .Caption = "Populate Charts"
>> > .FaceId = 433
>> > .OnAction = "Populate_Charts"
>> > End With
>> >
>> > ' THIRD MENU ITEM
>> > Set MenuItem = NewMenu.Controls.Add _
>> > (Type:=msoControlPopup)
>> > With MenuItem
>> > .Caption = "Chart Settings"
>> > .BeginGroup = True
>> > End With
>> >
>> > ' FIRST SUBMENU ITEM (Third Menu)
>> > Set SubMenuItem = MenuItem.Controls.Add _
>> > (Type:=msoControlButton)
>> > With SubMenuItem
>> > .Caption = "Resize Chart"
>> > .FaceId = 442
>> > .OnAction = "Resize_Chart"
>> > End With
>> >
>> > ' SECOND SUBMENU ITEM (Third Menu)
>> > Set SubMenuItem = MenuItem.Controls.Add _
>> > (Type:=msoControlButton)
>> > With SubMenuItem
>> > .Caption = "Set Print Range"
>> > .FaceId = 364
>> > .OnAction = "PrintRange"
>> > End With
>> > ' THIRD SUBMENU ITEM (Third Menu)
>> > Set SubMenuItem = MenuItem.Controls.Add _
>> > (Type:=msoControlButton)
>> > With SubMenuItem
>> > .Caption = "Chart Options"
>> > .FaceId = 435
>> > .OnAction = "ShowUserForm1"
>> > End With
>> > ' FOURTH SUBMENU ITEM (Third Menu)
>> > Set SubMenuItem = MenuItem.Controls.Add _
>> > (Type:=msoControlButton)
>> > With SubMenuItem
>> > .Caption = "Input Header and Footer Description"
>> > .FaceId = 237
>> > .OnAction = "Go_to_Titles"
>> > End With
>> >
>> > ' FORTH MENU ITEM
>> > Set MenuItem = NewMenu.Controls.Add _
>> > (Type:=msoControlButton)
>> > With MenuItem
>> > .Caption = "Chart Builder Help"
>> > .BeginGroup = True
>> > .OnAction = "ShowHelpForm"
>> > End With
>> >
>> > End Sub
>> >
>> > Sub DeleteMenu()
>> > On Error Resume Next
>> > CommandBars(1).Controls("Chart Builder Menu").Delete
>> > CommandBars(2).Controls("Chart Builder Menu").Delete
>> > End Sub
>> >
>> >
>> > --
>> > HTH
>> >
>> > Bob Phillips
>> >
>> > "Joel Mills" <[email protected]> wrote in message
>> > news:[email protected]...
>> >> Bob, Thanks for the reply, but I'm not very experience with VBA, this
> is
>> > my
>> >> first project. I am using it to learn VBA. I probably should have
>> > mentioned
>> >> that in this post. I did a google search and determined that there
>> >> are
>> > menu
>> >> bars and chart menu bars, but wasn't able to grasp how to get both
>> >> from
>> > the
>> >> same code. I'm still not sure how to revise my code.
>> >>
>> >>
>> >> "Bob Phillips" <[email protected]> wrote in message
>> >> news:%[email protected]...
>> >> > Repeat it for Commandbars(2). I would parameterise it and just pass
> the
>> > CB
>> >> > id to the routine in a caller routine.
>> >> >
>> >> > --
>> >> > HTH
>> >> >
>> >> > Bob Phillips
>> >> >
>> >> > "Joel Mills" <[email protected]> wrote in message
>> >> > news:[email protected]...
>> >> >> I have created a menu bar and would like for it to show up in the
>> >> >> chart
>> >> >> sheets too. Below is the code for my menu. I'm not sure how to
>> >> >> revise
>> >> >> it
>> >> >> to also create a "Chart Menu Bar". When I close the workbook this
>> >> >> menu
>> >> >> is
>> >> >> deleted. I'm not sure if the sub Proceedure to delete the menu
> should
>> >> > also
>> >> >> be revised. Any help would be appreciated.
>> >> >>
>> >> >>
>> >> >> Sub CreateMenu()
>> >> >> Dim NewMenu As CommandBarPopup
>> >> >>
>> >> >> ' Delete the menu if it already exists
>> >> >> Call DeleteMenu
>> >> >>
>> >> >> ' Find the Help Menu
>> >> >> Set HelpMenu = CommandBars(1).FindControl(ID:=30010)
>> >> >>
>> >> >> If HelpMenu Is Nothing Then
>> >> >> ' Add the menu to the end
>> >> >> Set NewMenu = CommandBars(1).Controls.Add _
>> >> >> (Type:=msoControlPopup, _
>> >> >> Temporary:=True)
>> >> >> Else
>> >> >> ' Add the menu before Help
>> >> >> Set NewMenu = CommandBars(1).Controls.Add _
>> >> >> (Type:=msoControlPopup, _
>> >> >> Before:=HelpMenu.Index, _
>> >> >> Temporary:=True)
>> >> >> End If
>> >> >>
>> >> >> ' Add a caption for the menu
>> >> >> NewMenu.Caption = "&Chart Builder Menu"
>> >> >>
>> >> >> ' FIRST MENU ITEM
>> >> >> Set MenuItem = NewMenu.Controls.Add _
>> >> >> (Type:=msoControlPopup)
>> >> >> With MenuItem
>> >> >> .Caption = "Retreive Exported P3 File"
>> >> >> .BeginGroup = True
>> >> >> End With
>> >> >>
>> >> >> ' FIRST SUBMENU ITEM (First Menu)
>> >> >> Set SubMenuItem = MenuItem.Controls.Add _
>> >> >> (Type:=msoControlButton)
>> >> >> With SubMenuItem
>> >> >> .Caption = "From Local Drive"
>> >> >> .FaceId = 1021
>> >> >> .OnAction = "ExportCdrive"
>> >> >> End With
>> >> >>
>> >> >> ' SECOND SUBMENU ITEM (First Menu)
>> >> >> Set SubMenuItem = MenuItem.Controls.Add _
>> >> >> (Type:=msoControlButton)
>> >> >> With SubMenuItem
>> >> >> .Caption = "From Network Drive"
>> >> >> .FaceId = 140
>> >> >> .OnAction = "ExportNet"
>> >> >> End With
>> >> >>
>> >> >> ' SECOND MENU ITEM
>> >> >> Set MenuItem = NewMenu.Controls.Add _
>> >> >> (Type:=msoControlPopup)
>> >> >> With MenuItem
>> >> >> .Caption = "Populate (Pivot Tables and Charts)"
>> >> >> .BeginGroup = True
>> >> >> End With
>> >> >>
>> >> >> ' FIRST SUBMENU ITEM (Second Menu)
>> >> >> Set SubMenuItem = MenuItem.Controls.Add _
>> >> >> (Type:=msoControlButton)
>> >> >> With SubMenuItem
>> >> >> .Caption = "Copy Exported File (DBF)"
>> >> >> .FaceId = 1642
>> >> >> .OnAction = "CopyData"
>> >> >> End With
>> >> >> 'SECOND SUBMENU ITEM (Second Menu)
>> >> >> Set SubMenuItem = MenuItem.Controls.Add _
>> >> >> (Type:=msoControlButton)
>> >> >> With SubMenuItem
>> >> >> .Caption = "Create Database"
>> >> >> .FaceId = 333
>> >> >> .OnAction = "Cleanup"
>> >> >> End With
>> >> >> ' THIRD SUBMENU ITEM (Second Menu)
>> >> >> Set SubMenuItem = MenuItem.Controls.Add _
>> >> >> (Type:=msoControlButton)
>> >> >> With SubMenuItem
>> >> >> .Caption = "Create Pivot Tables"
>> >> >> .FaceId = 657
>> >> >> .OnAction = "CreatePivotTable"
>> >> >> End With
>> >> >> ' FORTH SUBMENU ITEM (Second Menu)
>> >> >> Set SubMenuItem = MenuItem.Controls.Add _
>> >> >> (Type:=msoControlButton)
>> >> >> With SubMenuItem
>> >> >> .Caption = "Populate Charts"
>> >> >> .FaceId = 433
>> >> >> .OnAction = "Populate_Charts"
>> >> >> End With
>> >> >>
>> >> >> ' THIRD MENU ITEM
>> >> >> Set MenuItem = NewMenu.Controls.Add _
>> >> >> (Type:=msoControlPopup)
>> >> >> With MenuItem
>> >> >> .Caption = "Chart Settings"
>> >> >> .BeginGroup = True
>> >> >> End With
>> >> >>
>> >> >> ' FIRST SUBMENU ITEM (Third Menu)
>> >> >> Set SubMenuItem = MenuItem.Controls.Add _
>> >> >> (Type:=msoControlButton)
>> >> >> With SubMenuItem
>> >> >> .Caption = "Resize Chart"
>> >> >> .FaceId = 442
>> >> >> .OnAction = "Resize_Chart"
>> >> >> End With
>> >> >>
>> >> >> ' SECOND SUBMENU ITEM (Third Menu)
>> >> >> Set SubMenuItem = MenuItem.Controls.Add _
>> >> >> (Type:=msoControlButton)
>> >> >> With SubMenuItem
>> >> >> .Caption = "Set Print Range"
>> >> >> .FaceId = 364
>> >> >> .OnAction = "PrintRange"
>> >> >> End With
>> >> >> ' THIRD SUBMENU ITEM (Third Menu)
>> >> >> Set SubMenuItem = MenuItem.Controls.Add _
>> >> >> (Type:=msoControlButton)
>> >> >> With SubMenuItem
>> >> >> .Caption = "Chart Options"
>> >> >> .FaceId = 435
>> >> >> .OnAction = "ShowUserForm1"
>> >> >> End With
>> >> >> ' FOURTH SUBMENU ITEM (Third Menu)
>> >> >> Set SubMenuItem = MenuItem.Controls.Add _
>> >> >> (Type:=msoControlButton)
>> >> >> With SubMenuItem
>> >> >> .Caption = "Input Header and Footer Description"
>> >> >> .FaceId = 237
>> >> >> .OnAction = "Go_to_Titles"
>> >> >> End With
>> >> >>
>> >> >> ' FORTH MENU ITEM
>> >> >> Set MenuItem = NewMenu.Controls.Add _
>> >> >> (Type:=msoControlButton)
>> >> >> With MenuItem
>> >> >> .Caption = "Chart Builder Help"
>> >> >> .BeginGroup = True
>> >> >> .OnAction = "ShowHelpForm"
>> >> >> End With
>> >> >>
>> >> >> End Sub
>> >> >>
>> >> >> Sub DeleteMenu()
>> >> >> On Error Resume Next
>> >> >> CommandBars(1).Controls("Chart Builder Menu").Delete
>> >> >> End Sub
>> >> >>
>> >> >>
>> >> >>
>> >> >> Private Sub Workbook_Open()
>> >> >> Run ([CreateMenu])
>> >> >> End Sub
>> >> >>
>> >> >>
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>>
>
>

[Joel Mills]

Thanks, I just figured out the problem. Didn't see your solution until I
sent my reply to the group.


"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> This event doesn't know CBId, so you will need to tell the id numbers
>
> Private Sub Workbook_BeforeClose(Cancel As Boolean)
> DeleteMenu 1
> DeleteMenu 2
> ResetCellMenu
> End Sub
>
>
> --
> HTH
>
> Bob Phillips
>
> "Joel Mills" <[email protected]> wrote in message
> news:[email protected]...
>> Bob this does just what I wanted.
>>
>> With one exception. I had an event before the workbook close, that
> deleted
>> the menu. Now the menu isn't deleted until I close excel. Below is the
>> Code I had in "ThisWorkbook" Before it had "DeleteMenu", but I got an
> error
>> upon opening the Workbook after coping your code. I revised it to
>> "DeleteMenu (CBId) and now I don't get the error, but the menu remains
>> unless I close excel and reopen it. Before I could exit the Workbook and
>> the menu was deleted. I t didn't appear in any of the other open
> workbooks.
>>
>> Private Sub Workbook_BeforeClose(Cancel As Boolean)
>> DeleteMenu (CBId)
>> ResetCellMenu
>> End Sub
>>
>>
>>
>> "Bob Phillips" <[email protected]> wrote in message
>> news:[email protected]...
>> > Joel,
>> >
>> > Try again with this small mod, it seems to work okay.
>> >
>> > Sub CreateMenu()
>> > UpdateCB 1
>> > UpdateCB 2
>> > End Sub
>> >
>> > Sub UpdateCB(CBId As Long)
>> > Dim NewMenu As CommandBarPopup
>> >
>> > ' Delete the menu if it already exists
>> > Call DeleteMenu(CBId)
>> >
>> > ' Find the Help Menu
>> > Set HelpMenu = CommandBars(CBId).FindControl(ID:=30010)
>> >
>> > If HelpMenu Is Nothing Then
>> > ' Add the menu to the end
>> > Set NewMenu = CommandBars(CBId).Controls.Add _
>> > (Type:=msoControlPopup, _
>> > Temporary:=True)
>> > Else
>> > ' Add the menu before Help
>> > Set NewMenu = CommandBars(CBId).Controls.Add _
>> > (Type:=msoControlPopup, _
>> > Before:=HelpMenu.Index, _
>> > Temporary:=True)
>> > End If
>> >
>> > ' Add a caption for the menu
>> > NewMenu.Caption = "&Chart Builder Menu"
>> >
>> > ' FIRST MENU ITEM
>> > Set MenuItem = NewMenu.Controls.Add _
>> > (Type:=msoControlPopup)
>> > With MenuItem
>> > .Caption = "Retreive Exported P3 File"
>> > .BeginGroup = True
>> > End With
>> >
>> > ' FIRST SUBMENU ITEM (First Menu)
>> > Set SubMenuItem = MenuItem.Controls.Add _
>> > (Type:=msoControlButton)
>> > With SubMenuItem
>> > .Caption = "From Local Drive"
>> > .FaceId = 1021
>> > .OnAction = "ExportCdrive"
>> > End With
>> >
>> > ' SECOND SUBMENU ITEM (First Menu)
>> > Set SubMenuItem = MenuItem.Controls.Add _
>> > (Type:=msoControlButton)
>> > With SubMenuItem
>> > .Caption = "From Network Drive"
>> > .FaceId = 140
>> > .OnAction = "ExportNet"
>> > End With
>> >
>> > ' SECOND MENU ITEM
>> > Set MenuItem = NewMenu.Controls.Add _
>> > (Type:=msoControlPopup)
>> > With MenuItem
>> > .Caption = "Populate (Pivot Tables and Charts)"
>> > .BeginGroup = True
>> > End With
>> >
>> > ' FIRST SUBMENU ITEM (Second Menu)
>> > Set SubMenuItem = MenuItem.Controls.Add _
>> > (Type:=msoControlButton)
>> > With SubMenuItem
>> > .Caption = "Copy Exported File (DBF)"
>> > .FaceId = 1642
>> > .OnAction = "CopyData"
>> > End With
>> > 'SECOND SUBMENU ITEM (Second Menu)
>> > Set SubMenuItem = MenuItem.Controls.Add _
>> > (Type:=msoControlButton)
>> > With SubMenuItem
>> > .Caption = "Create Database"
>> > .FaceId = 333
>> > .OnAction = "Cleanup"
>> > End With
>> > ' THIRD SUBMENU ITEM (Second Menu)
>> > Set SubMenuItem = MenuItem.Controls.Add _
>> > (Type:=msoControlButton)
>> > With SubMenuItem
>> > .Caption = "Create Pivot Tables"
>> > .FaceId = 657
>> > .OnAction = "CreatePivotTable"
>> > End With
>> > ' FORTH SUBMENU ITEM (Second Menu)
>> > Set SubMenuItem = MenuItem.Controls.Add _
>> > (Type:=msoControlButton)
>> > With SubMenuItem
>> > .Caption = "Populate Charts"
>> > .FaceId = 433
>> > .OnAction = "Populate_Charts"
>> > End With
>> >
>> > ' THIRD MENU ITEM
>> > Set MenuItem = NewMenu.Controls.Add _
>> > (Type:=msoControlPopup)
>> > With MenuItem
>> > .Caption = "Chart Settings"
>> > .BeginGroup = True
>> > End With
>> >
>> > ' FIRST SUBMENU ITEM (Third Menu)
>> > Set SubMenuItem = MenuItem.Controls.Add _
>> > (Type:=msoControlButton)
>> > With SubMenuItem
>> > .Caption = "Resize Chart"
>> > .FaceId = 442
>> > .OnAction = "Resize_Chart"
>> > End With
>> >
>> > ' SECOND SUBMENU ITEM (Third Menu)
>> > Set SubMenuItem = MenuItem.Controls.Add _
>> > (Type:=msoControlButton)
>> > With SubMenuItem
>> > .Caption = "Set Print Range"
>> > .FaceId = 364
>> > .OnAction = "PrintRange"
>> > End With
>> > ' THIRD SUBMENU ITEM (Third Menu)
>> > Set SubMenuItem = MenuItem.Controls.Add _
>> > (Type:=msoControlButton)
>> > With SubMenuItem
>> > .Caption = "Chart Options"
>> > .FaceId = 435
>> > .OnAction = "ShowUserForm1"
>> > End With
>> > ' FOURTH SUBMENU ITEM (Third Menu)
>> > Set SubMenuItem = MenuItem.Controls.Add _
>> > (Type:=msoControlButton)
>> > With SubMenuItem
>> > .Caption = "Input Header and Footer Description"
>> > .FaceId = 237
>> > .OnAction = "Go_to_Titles"
>> > End With
>> >
>> > ' FORTH MENU ITEM
>> > Set MenuItem = NewMenu.Controls.Add _
>> > (Type:=msoControlButton)
>> > With MenuItem
>> > .Caption = "Chart Builder Help"
>> > .BeginGroup = True
>> > .OnAction = "ShowHelpForm"
>> > End With
>> >
>> > End Sub
>> >
>> > Sub DeleteMenu(CBId As Long)
>> > On Error Resume Next
>> > CommandBars(CBId).Controls("Chart Builder Menu").Delete
>> > End Sub
>> >
>> >
>> >
>> >
>> >
>> > --
>> > HTH
>> >
>> > Bob Phillips
>> >
>> > "Joel Mills" <[email protected]> wrote in message
>> > news:[email protected]...
>> >> This still doesn't add a "Chart Menu Bar". What am I missing? I
> changed
>> >> CommandBars(1) to CommandBars(CBId) on the If then Else statements and
>> >> now
>> >> it creates the Chart Menu Bar and not the Menu Bar. This must be very
>> > close
>> >> to the code I'm looking for.
>> >>
>> >> Sub UpdateCB(CBId As Long)
>> >> Dim NewMenu As CommandBarPopup
>> >>
>> >> ' Delete the menu if it already exists
>> >> Call DeleteMenu
>> >>
>> >> ' Find the Help Menu
>> >> Set HelpMenu = CommandBars(CBId).FindControl(ID:=30010)
>> >>
>> >> If HelpMenu Is Nothing Then
>> >> ' Add the menu to the end
>> >> Set NewMenu = CommandBars(CBId).Controls.Add _
>> >> (Type:=msoControlPopup, _
>> >> Temporary:=True)
>> >> Else
>> >> ' Add the menu before Help
>> >> Set NewMenu = CommandBars(CBId).Controls.Add _
>> >> (Type:=msoControlPopup, _
>> >> Before:=HelpMenu.Index, _
>> >> Temporary:=True)
>> >>
>> >> "Bob Phillips" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> > Try this
>> >> >
>> >> > Sub CreateMenu
>> >> > UpdateCB 1
>> >> > UpdateCB 2
>> >> > End Sub
>> >> >
>> >> > Sub UpdateCB(CBId as Long)
>> >> > Dim NewMenu As CommandBarPopup
>> >> >
>> >> > ' Delete the menu if it already exists
>> >> > Call DeleteMenu
>> >> >
>> >> > ' Find the Help Menu
>> >> > Set HelpMenu = CommandBars(CBId).FindControl(ID:=30010)
>> >> >
>> >> > If HelpMenu Is Nothing Then
>> >> > ' Add the menu to the end
>> >> > Set NewMenu = CommandBars(1).Controls.Add _
>> >> > (Type:=msoControlPopup, _
>> >> > Temporary:=True)
>> >> > Else
>> >> > ' Add the menu before Help
>> >> > Set NewMenu = CommandBars(1).Controls.Add _
>> >> > (Type:=msoControlPopup, _
>> >> > Before:=HelpMenu.Index, _
>> >> > Temporary:=True)
>> >> > End If
>> >> >
>> >> > ' Add a caption for the menu
>> >> > NewMenu.Caption = "&Chart Builder Menu"
>> >> >
>> >> > ' FIRST MENU ITEM
>> >> > Set MenuItem = NewMenu.Controls.Add _
>> >> > (Type:=msoControlPopup)
>> >> > With MenuItem
>> >> > .Caption = "Retreive Exported P3 File"
>> >> > .BeginGroup = True
>> >> > End With
>> >> >
>> >> > ' FIRST SUBMENU ITEM (First Menu)
>> >> > Set SubMenuItem = MenuItem.Controls.Add _
>> >> > (Type:=msoControlButton)
>> >> > With SubMenuItem
>> >> > .Caption = "From Local Drive"
>> >> > .FaceId = 1021
>> >> > .OnAction = "ExportCdrive"
>> >> > End With
>> >> >
>> >> > ' SECOND SUBMENU ITEM (First Menu)
>> >> > Set SubMenuItem = MenuItem.Controls.Add _
>> >> > (Type:=msoControlButton)
>> >> > With SubMenuItem
>> >> > .Caption = "From Network Drive"
>> >> > .FaceId = 140
>> >> > .OnAction = "ExportNet"
>> >> > End With
>> >> >
>> >> > ' SECOND MENU ITEM
>> >> > Set MenuItem = NewMenu.Controls.Add _
>> >> > (Type:=msoControlPopup)
>> >> > With MenuItem
>> >> > .Caption = "Populate (Pivot Tables and Charts)"
>> >> > .BeginGroup = True
>> >> > End With
>> >> >
>> >> > ' FIRST SUBMENU ITEM (Second Menu)
>> >> > Set SubMenuItem = MenuItem.Controls.Add _
>> >> > (Type:=msoControlButton)
>> >> > With SubMenuItem
>> >> > .Caption = "Copy Exported File (DBF)"
>> >> > .FaceId = 1642
>> >> > .OnAction = "CopyData"
>> >> > End With
>> >> > 'SECOND SUBMENU ITEM (Second Menu)
>> >> > Set SubMenuItem = MenuItem.Controls.Add _
>> >> > (Type:=msoControlButton)
>> >> > With SubMenuItem
>> >> > .Caption = "Create Database"
>> >> > .FaceId = 333
>> >> > .OnAction = "Cleanup"
>> >> > End With
>> >> > ' THIRD SUBMENU ITEM (Second Menu)
>> >> > Set SubMenuItem = MenuItem.Controls.Add _
>> >> > (Type:=msoControlButton)
>> >> > With SubMenuItem
>> >> > .Caption = "Create Pivot Tables"
>> >> > .FaceId = 657
>> >> > .OnAction = "CreatePivotTable"
>> >> > End With
>> >> > ' FORTH SUBMENU ITEM (Second Menu)
>> >> > Set SubMenuItem = MenuItem.Controls.Add _
>> >> > (Type:=msoControlButton)
>> >> > With SubMenuItem
>> >> > .Caption = "Populate Charts"
>> >> > .FaceId = 433
>> >> > .OnAction = "Populate_Charts"
>> >> > End With
>> >> >
>> >> > ' THIRD MENU ITEM
>> >> > Set MenuItem = NewMenu.Controls.Add _
>> >> > (Type:=msoControlPopup)
>> >> > With MenuItem
>> >> > .Caption = "Chart Settings"
>> >> > .BeginGroup = True
>> >> > End With
>> >> >
>> >> > ' FIRST SUBMENU ITEM (Third Menu)
>> >> > Set SubMenuItem = MenuItem.Controls.Add _
>> >> > (Type:=msoControlButton)
>> >> > With SubMenuItem
>> >> > .Caption = "Resize Chart"
>> >> > .FaceId = 442
>> >> > .OnAction = "Resize_Chart"
>> >> > End With
>> >> >
>> >> > ' SECOND SUBMENU ITEM (Third Menu)
>> >> > Set SubMenuItem = MenuItem.Controls.Add _
>> >> > (Type:=msoControlButton)
>> >> > With SubMenuItem
>> >> > .Caption = "Set Print Range"
>> >> > .FaceId = 364
>> >> > .OnAction = "PrintRange"
>> >> > End With
>> >> > ' THIRD SUBMENU ITEM (Third Menu)
>> >> > Set SubMenuItem = MenuItem.Controls.Add _
>> >> > (Type:=msoControlButton)
>> >> > With SubMenuItem
>> >> > .Caption = "Chart Options"
>> >> > .FaceId = 435
>> >> > .OnAction = "ShowUserForm1"
>> >> > End With
>> >> > ' FOURTH SUBMENU ITEM (Third Menu)
>> >> > Set SubMenuItem = MenuItem.Controls.Add _
>> >> > (Type:=msoControlButton)
>> >> > With SubMenuItem
>> >> > .Caption = "Input Header and Footer Description"
>> >> > .FaceId = 237
>> >> > .OnAction = "Go_to_Titles"
>> >> > End With
>> >> >
>> >> > ' FORTH MENU ITEM
>> >> > Set MenuItem = NewMenu.Controls.Add _
>> >> > (Type:=msoControlButton)
>> >> > With MenuItem
>> >> > .Caption = "Chart Builder Help"
>> >> > .BeginGroup = True
>> >> > .OnAction = "ShowHelpForm"
>> >> > End With
>> >> >
>> >> > End Sub
>> >> >
>> >> > Sub DeleteMenu()
>> >> > On Error Resume Next
>> >> > CommandBars(1).Controls("Chart Builder Menu").Delete
>> >> > CommandBars(2).Controls("Chart Builder Menu").Delete
>> >> > End Sub
>> >> >
>> >> >
>> >> > --
>> >> > HTH
>> >> >
>> >> > Bob Phillips
>> >> >
>> >> > "Joel Mills" <[email protected]> wrote in message
>> >> > news:[email protected]...
>> >> >> Bob, Thanks for the reply, but I'm not very experience with VBA,
> this
>> > is
>> >> > my
>> >> >> first project. I am using it to learn VBA. I probably should have
>> >> > mentioned
>> >> >> that in this post. I did a google search and determined that there
>> >> >> are
>> >> > menu
>> >> >> bars and chart menu bars, but wasn't able to grasp how to get both
>> >> >> from
>> >> > the
>> >> >> same code. I'm still not sure how to revise my code.
>> >> >>
>> >> >>
>> >> >> "Bob Phillips" <[email protected]> wrote in message
>> >> >> news:%[email protected]...
>> >> >> > Repeat it for Commandbars(2). I would parameterise it and just
> pass
>> > the
>> >> > CB
>> >> >> > id to the routine in a caller routine.
>> >> >> >
>> >> >> > --
>> >> >> > HTH
>> >> >> >
>> >> >> > Bob Phillips
>> >> >> >
>> >> >> > "Joel Mills" <[email protected]> wrote in message
>> >> >> > news:[email protected]...
>> >> >> >> I have created a menu bar and would like for it to show up in
>> >> >> >> the
>> >> >> >> chart
>> >> >> >> sheets too. Below is the code for my menu. I'm not sure how to
>> >> >> >> revise
>> >> >> >> it
>> >> >> >> to also create a "Chart Menu Bar". When I close the workbook
> this
>> >> >> >> menu
>> >> >> >> is
>> >> >> >> deleted. I'm not sure if the sub Proceedure to delete the menu
>> > should
>> >> >> > also
>> >> >> >> be revised. Any help would be appreciated.
>> >> >> >>
>> >> >> >>
>> >> >> >> Sub CreateMenu()
>> >> >> >> Dim NewMenu As CommandBarPopup
>> >> >> >>
>> >> >> >> ' Delete the menu if it already exists
>> >> >> >> Call DeleteMenu
>> >> >> >>
>> >> >> >> ' Find the Help Menu
>> >> >> >> Set HelpMenu = CommandBars(1).FindControl(ID:=30010)
>> >> >> >>
>> >> >> >> If HelpMenu Is Nothing Then
>> >> >> >> ' Add the menu to the end
>> >> >> >> Set NewMenu = CommandBars(1).Controls.Add _
>> >> >> >> (Type:=msoControlPopup, _
>> >> >> >> Temporary:=True)
>> >> >> >> Else
>> >> >> >> ' Add the menu before Help
>> >> >> >> Set NewMenu = CommandBars(1).Controls.Add _
>> >> >> >> (Type:=msoControlPopup, _
>> >> >> >> Before:=HelpMenu.Index, _
>> >> >> >> Temporary:=True)
>> >> >> >> End If
>> >> >> >>
>> >> >> >> ' Add a caption for the menu
>> >> >> >> NewMenu.Caption = "&Chart Builder Menu"
>> >> >> >>
>> >> >> >> ' FIRST MENU ITEM
>> >> >> >> Set MenuItem = NewMenu.Controls.Add _
>> >> >> >> (Type:=msoControlPopup)
>> >> >> >> With MenuItem
>> >> >> >> .Caption = "Retreive Exported P3 File"
>> >> >> >> .BeginGroup = True
>> >> >> >> End With
>> >> >> >>
>> >> >> >> ' FIRST SUBMENU ITEM (First Menu)
>> >> >> >> Set SubMenuItem = MenuItem.Controls.Add _
>> >> >> >> (Type:=msoControlButton)
>> >> >> >> With SubMenuItem
>> >> >> >> .Caption = "From Local Drive"
>> >> >> >> .FaceId = 1021
>> >> >> >> .OnAction = "ExportCdrive"
>> >> >> >> End With
>> >> >> >>
>> >> >> >> ' SECOND SUBMENU ITEM (First Menu)
>> >> >> >> Set SubMenuItem = MenuItem.Controls.Add _
>> >> >> >> (Type:=msoControlButton)
>> >> >> >> With SubMenuItem
>> >> >> >> .Caption = "From Network Drive"
>> >> >> >> .FaceId = 140
>> >> >> >> .OnAction = "ExportNet"
>> >> >> >> End With
>> >> >> >>
>> >> >> >> ' SECOND MENU ITEM
>> >> >> >> Set MenuItem = NewMenu.Controls.Add _
>> >> >> >> (Type:=msoControlPopup)
>> >> >> >> With MenuItem
>> >> >> >> .Caption = "Populate (Pivot Tables and Charts)"
>> >> >> >> .BeginGroup = True
>> >> >> >> End With
>> >> >> >>
>> >> >> >> ' FIRST SUBMENU ITEM (Second Menu)
>> >> >> >> Set SubMenuItem = MenuItem.Controls.Add _
>> >> >> >> (Type:=msoControlButton)
>> >> >> >> With SubMenuItem
>> >> >> >> .Caption = "Copy Exported File (DBF)"
>> >> >> >> .FaceId = 1642
>> >> >> >> .OnAction = "CopyData"
>> >> >> >> End With
>> >> >> >> 'SECOND SUBMENU ITEM (Second Menu)
>> >> >> >> Set SubMenuItem = MenuItem.Controls.Add _
>> >> >> >> (Type:=msoControlButton)
>> >> >> >> With SubMenuItem
>> >> >> >> .Caption = "Create Database"
>> >> >> >> .FaceId = 333
>> >> >> >> .OnAction = "Cleanup"
>> >> >> >> End With
>> >> >> >> ' THIRD SUBMENU ITEM (Second Menu)
>> >> >> >> Set SubMenuItem = MenuItem.Controls.Add _
>> >> >> >> (Type:=msoControlButton)
>> >> >> >> With SubMenuItem
>> >> >> >> .Caption = "Create Pivot Tables"
>> >> >> >> .FaceId = 657
>> >> >> >> .OnAction = "CreatePivotTable"
>> >> >> >> End With
>> >> >> >> ' FORTH SUBMENU ITEM (Second Menu)
>> >> >> >> Set SubMenuItem = MenuItem.Controls.Add _
>> >> >> >> (Type:=msoControlButton)
>> >> >> >> With SubMenuItem
>> >> >> >> .Caption = "Populate Charts"
>> >> >> >> .FaceId = 433
>> >> >> >> .OnAction = "Populate_Charts"
>> >> >> >> End With
>> >> >> >>
>> >> >> >> ' THIRD MENU ITEM
>> >> >> >> Set MenuItem = NewMenu.Controls.Add _
>> >> >> >> (Type:=msoControlPopup)
>> >> >> >> With MenuItem
>> >> >> >> .Caption = "Chart Settings"
>> >> >> >> .BeginGroup = True
>> >> >> >> End With
>> >> >> >>
>> >> >> >> ' FIRST SUBMENU ITEM (Third Menu)
>> >> >> >> Set SubMenuItem = MenuItem.Controls.Add _
>> >> >> >> (Type:=msoControlButton)
>> >> >> >> With SubMenuItem
>> >> >> >> .Caption = "Resize Chart"
>> >> >> >> .FaceId = 442
>> >> >> >> .OnAction = "Resize_Chart"
>> >> >> >> End With
>> >> >> >>
>> >> >> >> ' SECOND SUBMENU ITEM (Third Menu)
>> >> >> >> Set SubMenuItem = MenuItem.Controls.Add _
>> >> >> >> (Type:=msoControlButton)
>> >> >> >> With SubMenuItem
>> >> >> >> .Caption = "Set Print Range"
>> >> >> >> .FaceId = 364
>> >> >> >> .OnAction = "PrintRange"
>> >> >> >> End With
>> >> >> >> ' THIRD SUBMENU ITEM (Third Menu)
>> >> >> >> Set SubMenuItem = MenuItem.Controls.Add _
>> >> >> >> (Type:=msoControlButton)
>> >> >> >> With SubMenuItem
>> >> >> >> .Caption = "Chart Options"
>> >> >> >> .FaceId = 435
>> >> >> >> .OnAction = "ShowUserForm1"
>> >> >> >> End With
>> >> >> >> ' FOURTH SUBMENU ITEM (Third Menu)
>> >> >> >> Set SubMenuItem = MenuItem.Controls.Add _
>> >> >> >> (Type:=msoControlButton)
>> >> >> >> With SubMenuItem
>> >> >> >> .Caption = "Input Header and Footer Description"
>> >> >> >> .FaceId = 237
>> >> >> >> .OnAction = "Go_to_Titles"
>> >> >> >> End With
>> >> >> >>
>> >> >> >> ' FORTH MENU ITEM
>> >> >> >> Set MenuItem = NewMenu.Controls.Add _
>> >> >> >> (Type:=msoControlButton)
>> >> >> >> With MenuItem
>> >> >> >> .Caption = "Chart Builder Help"
>> >> >> >> .BeginGroup = True
>> >> >> >> .OnAction = "ShowHelpForm"
>> >> >> >> End With
>> >> >> >>
>> >> >> >> End Sub
>> >> >> >>
>> >> >> >> Sub DeleteMenu()
>> >> >> >> On Error Resume Next
>> >> >> >> CommandBars(1).Controls("Chart Builder Menu").Delete
>> >> >> >> End Sub
>> >> >> >>
>> >> >> >>
>> >> >> >>
>> >> >> >> Private Sub Workbook_Open()
>> >> >> >> Run ([CreateMenu])
>> >> >> >> End Sub
>> >> >> >>
>> >> >> >>
>> >> >> >
>> >> >> >
>> >> >>
>> >> >>
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>>
>
>

[Joel Mills]

I revised to your solution. Is much easier to see what is happening with
your code.

Joel

"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> This event doesn't know CBId, so you will need to tell the id numbers
>
> Private Sub Workbook_BeforeClose(Cancel As Boolean)
> DeleteMenu 1
> DeleteMenu 2
> ResetCellMenu
> End Sub
>
>
> --
> HTH
>
> Bob Phillips
>
> "Joel Mills" <[email protected]> wrote in message
> news:[email protected]...
>> Bob this does just what I wanted.
>>
>> With one exception. I had an event before the workbook close, that
> deleted
>> the menu. Now the menu isn't deleted until I close excel. Below is the
>> Code I had in "ThisWorkbook" Before it had "DeleteMenu", but I got an
> error
>> upon opening the Workbook after coping your code. I revised it to
>> "DeleteMenu (CBId) and now I don't get the error, but the menu remains
>> unless I close excel and reopen it. Before I could exit the Workbook and
>> the menu was deleted. I t didn't appear in any of the other open
> workbooks.
>>
>> Private Sub Workbook_BeforeClose(Cancel As Boolean)
>> DeleteMenu (CBId)
>> ResetCellMenu
>> End Sub
>>
>>
>>
>> "Bob Phillips" <[email protected]> wrote in message
>> news:[email protected]...
>> > Joel,
>> >
>> > Try again with this small mod, it seems to work okay.
>> >
>> > Sub CreateMenu()
>> > UpdateCB 1
>> > UpdateCB 2
>> > End Sub
>> >
>> > Sub UpdateCB(CBId As Long)
>> > Dim NewMenu As CommandBarPopup
>> >
>> > ' Delete the menu if it already exists
>> > Call DeleteMenu(CBId)
>> >
>> > ' Find the Help Menu
>> > Set HelpMenu = CommandBars(CBId).FindControl(ID:=30010)
>> >
>> > If HelpMenu Is Nothing Then
>> > ' Add the menu to the end
>> > Set NewMenu = CommandBars(CBId).Controls.Add _
>> > (Type:=msoControlPopup, _
>> > Temporary:=True)
>> > Else
>> > ' Add the menu before Help
>> > Set NewMenu = CommandBars(CBId).Controls.Add _
>> > (Type:=msoControlPopup, _
>> > Before:=HelpMenu.Index, _
>> > Temporary:=True)
>> > End If
>> >
>> > ' Add a caption for the menu
>> > NewMenu.Caption = "&Chart Builder Menu"
>> >
>> > ' FIRST MENU ITEM
>> > Set MenuItem = NewMenu.Controls.Add _
>> > (Type:=msoControlPopup)
>> > With MenuItem
>> > .Caption = "Retreive Exported P3 File"
>> > .BeginGroup = True
>> > End With
>> >
>> > ' FIRST SUBMENU ITEM (First Menu)
>> > Set SubMenuItem = MenuItem.Controls.Add _
>> > (Type:=msoControlButton)
>> > With SubMenuItem
>> > .Caption = "From Local Drive"
>> > .FaceId = 1021
>> > .OnAction = "ExportCdrive"
>> > End With
>> >
>> > ' SECOND SUBMENU ITEM (First Menu)
>> > Set SubMenuItem = MenuItem.Controls.Add _
>> > (Type:=msoControlButton)
>> > With SubMenuItem
>> > .Caption = "From Network Drive"
>> > .FaceId = 140
>> > .OnAction = "ExportNet"
>> > End With
>> >
>> > ' SECOND MENU ITEM
>> > Set MenuItem = NewMenu.Controls.Add _
>> > (Type:=msoControlPopup)
>> > With MenuItem
>> > .Caption = "Populate (Pivot Tables and Charts)"
>> > .BeginGroup = True
>> > End With
>> >
>> > ' FIRST SUBMENU ITEM (Second Menu)
>> > Set SubMenuItem = MenuItem.Controls.Add _
>> > (Type:=msoControlButton)
>> > With SubMenuItem
>> > .Caption = "Copy Exported File (DBF)"
>> > .FaceId = 1642
>> > .OnAction = "CopyData"
>> > End With
>> > 'SECOND SUBMENU ITEM (Second Menu)
>> > Set SubMenuItem = MenuItem.Controls.Add _
>> > (Type:=msoControlButton)
>> > With SubMenuItem
>> > .Caption = "Create Database"
>> > .FaceId = 333
>> > .OnAction = "Cleanup"
>> > End With
>> > ' THIRD SUBMENU ITEM (Second Menu)
>> > Set SubMenuItem = MenuItem.Controls.Add _
>> > (Type:=msoControlButton)
>> > With SubMenuItem
>> > .Caption = "Create Pivot Tables"
>> > .FaceId = 657
>> > .OnAction = "CreatePivotTable"
>> > End With
>> > ' FORTH SUBMENU ITEM (Second Menu)
>> > Set SubMenuItem = MenuItem.Controls.Add _
>> > (Type:=msoControlButton)
>> > With SubMenuItem
>> > .Caption = "Populate Charts"
>> > .FaceId = 433
>> > .OnAction = "Populate_Charts"
>> > End With
>> >
>> > ' THIRD MENU ITEM
>> > Set MenuItem = NewMenu.Controls.Add _
>> > (Type:=msoControlPopup)
>> > With MenuItem
>> > .Caption = "Chart Settings"
>> > .BeginGroup = True
>> > End With
>> >
>> > ' FIRST SUBMENU ITEM (Third Menu)
>> > Set SubMenuItem = MenuItem.Controls.Add _
>> > (Type:=msoControlButton)
>> > With SubMenuItem
>> > .Caption = "Resize Chart"
>> > .FaceId = 442
>> > .OnAction = "Resize_Chart"
>> > End With
>> >
>> > ' SECOND SUBMENU ITEM (Third Menu)
>> > Set SubMenuItem = MenuItem.Controls.Add _
>> > (Type:=msoControlButton)
>> > With SubMenuItem
>> > .Caption = "Set Print Range"
>> > .FaceId = 364
>> > .OnAction = "PrintRange"
>> > End With
>> > ' THIRD SUBMENU ITEM (Third Menu)
>> > Set SubMenuItem = MenuItem.Controls.Add _
>> > (Type:=msoControlButton)
>> > With SubMenuItem
>> > .Caption = "Chart Options"
>> > .FaceId = 435
>> > .OnAction = "ShowUserForm1"
>> > End With
>> > ' FOURTH SUBMENU ITEM (Third Menu)
>> > Set SubMenuItem = MenuItem.Controls.Add _
>> > (Type:=msoControlButton)
>> > With SubMenuItem
>> > .Caption = "Input Header and Footer Description"
>> > .FaceId = 237
>> > .OnAction = "Go_to_Titles"
>> > End With
>> >
>> > ' FORTH MENU ITEM
>> > Set MenuItem = NewMenu.Controls.Add _
>> > (Type:=msoControlButton)
>> > With MenuItem
>> > .Caption = "Chart Builder Help"
>> > .BeginGroup = True
>> > .OnAction = "ShowHelpForm"
>> > End With
>> >
>> > End Sub
>> >
>> > Sub DeleteMenu(CBId As Long)
>> > On Error Resume Next
>> > CommandBars(CBId).Controls("Chart Builder Menu").Delete
>> > End Sub
>> >
>> >
>> >
>> >
>> >
>> > --
>> > HTH
>> >
>> > Bob Phillips
>> >
>> > "Joel Mills" <[email protected]> wrote in message
>> > news:[email protected]...
>> >> This still doesn't add a "Chart Menu Bar". What am I missing? I
> changed
>> >> CommandBars(1) to CommandBars(CBId) on the If then Else statements and
>> >> now
>> >> it creates the Chart Menu Bar and not the Menu Bar. This must be very
>> > close
>> >> to the code I'm looking for.
>> >>
>> >> Sub UpdateCB(CBId As Long)
>> >> Dim NewMenu As CommandBarPopup
>> >>
>> >> ' Delete the menu if it already exists
>> >> Call DeleteMenu
>> >>
>> >> ' Find the Help Menu
>> >> Set HelpMenu = CommandBars(CBId).FindControl(ID:=30010)
>> >>
>> >> If HelpMenu Is Nothing Then
>> >> ' Add the menu to the end
>> >> Set NewMenu = CommandBars(CBId).Controls.Add _
>> >> (Type:=msoControlPopup, _
>> >> Temporary:=True)
>> >> Else
>> >> ' Add the menu before Help
>> >> Set NewMenu = CommandBars(CBId).Controls.Add _
>> >> (Type:=msoControlPopup, _
>> >> Before:=HelpMenu.Index, _
>> >> Temporary:=True)
>> >>
>> >> "Bob Phillips" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> > Try this
>> >> >
>> >> > Sub CreateMenu
>> >> > UpdateCB 1
>> >> > UpdateCB 2
>> >> > End Sub
>> >> >
>> >> > Sub UpdateCB(CBId as Long)
>> >> > Dim NewMenu As CommandBarPopup
>> >> >
>> >> > ' Delete the menu if it already exists
>> >> > Call DeleteMenu
>> >> >
>> >> > ' Find the Help Menu
>> >> > Set HelpMenu = CommandBars(CBId).FindControl(ID:=30010)
>> >> >
>> >> > If HelpMenu Is Nothing Then
>> >> > ' Add the menu to the end
>> >> > Set NewMenu = CommandBars(1).Controls.Add _
>> >> > (Type:=msoControlPopup, _
>> >> > Temporary:=True)
>> >> > Else
>> >> > ' Add the menu before Help
>> >> > Set NewMenu = CommandBars(1).Controls.Add _
>> >> > (Type:=msoControlPopup, _
>> >> > Before:=HelpMenu.Index, _
>> >> > Temporary:=True)
>> >> > End If
>> >> >
>> >> > ' Add a caption for the menu
>> >> > NewMenu.Caption = "&Chart Builder Menu"
>> >> >
>> >> > ' FIRST MENU ITEM
>> >> > Set MenuItem = NewMenu.Controls.Add _
>> >> > (Type:=msoControlPopup)
>> >> > With MenuItem
>> >> > .Caption = "Retreive Exported P3 File"
>> >> > .BeginGroup = True
>> >> > End With
>> >> >
>> >> > ' FIRST SUBMENU ITEM (First Menu)
>> >> > Set SubMenuItem = MenuItem.Controls.Add _
>> >> > (Type:=msoControlButton)
>> >> > With SubMenuItem
>> >> > .Caption = "From Local Drive"
>> >> > .FaceId = 1021
>> >> > .OnAction = "ExportCdrive"
>> >> > End With
>> >> >
>> >> > ' SECOND SUBMENU ITEM (First Menu)
>> >> > Set SubMenuItem = MenuItem.Controls.Add _
>> >> > (Type:=msoControlButton)
>> >> > With SubMenuItem
>> >> > .Caption = "From Network Drive"
>> >> > .FaceId = 140
>> >> > .OnAction = "ExportNet"
>> >> > End With
>> >> >
>> >> > ' SECOND MENU ITEM
>> >> > Set MenuItem = NewMenu.Controls.Add _
>> >> > (Type:=msoControlPopup)
>> >> > With MenuItem
>> >> > .Caption = "Populate (Pivot Tables and Charts)"
>> >> > .BeginGroup = True
>> >> > End With
>> >> >
>> >> > ' FIRST SUBMENU ITEM (Second Menu)
>> >> > Set SubMenuItem = MenuItem.Controls.Add _
>> >> > (Type:=msoControlButton)
>> >> > With SubMenuItem
>> >> > .Caption = "Copy Exported File (DBF)"
>> >> > .FaceId = 1642
>> >> > .OnAction = "CopyData"
>> >> > End With
>> >> > 'SECOND SUBMENU ITEM (Second Menu)
>> >> > Set SubMenuItem = MenuItem.Controls.Add _
>> >> > (Type:=msoControlButton)
>> >> > With SubMenuItem
>> >> > .Caption = "Create Database"
>> >> > .FaceId = 333
>> >> > .OnAction = "Cleanup"
>> >> > End With
>> >> > ' THIRD SUBMENU ITEM (Second Menu)
>> >> > Set SubMenuItem = MenuItem.Controls.Add _
>> >> > (Type:=msoControlButton)
>> >> > With SubMenuItem
>> >> > .Caption = "Create Pivot Tables"
>> >> > .FaceId = 657
>> >> > .OnAction = "CreatePivotTable"
>> >> > End With
>> >> > ' FORTH SUBMENU ITEM (Second Menu)
>> >> > Set SubMenuItem = MenuItem.Controls.Add _
>> >> > (Type:=msoControlButton)
>> >> > With SubMenuItem
>> >> > .Caption = "Populate Charts"
>> >> > .FaceId = 433
>> >> > .OnAction = "Populate_Charts"
>> >> > End With
>> >> >
>> >> > ' THIRD MENU ITEM
>> >> > Set MenuItem = NewMenu.Controls.Add _
>> >> > (Type:=msoControlPopup)
>> >> > With MenuItem
>> >> > .Caption = "Chart Settings"
>> >> > .BeginGroup = True
>> >> > End With
>> >> >
>> >> > ' FIRST SUBMENU ITEM (Third Menu)
>> >> > Set SubMenuItem = MenuItem.Controls.Add _
>> >> > (Type:=msoControlButton)
>> >> > With SubMenuItem
>> >> > .Caption = "Resize Chart"
>> >> > .FaceId = 442
>> >> > .OnAction = "Resize_Chart"
>> >> > End With
>> >> >
>> >> > ' SECOND SUBMENU ITEM (Third Menu)
>> >> > Set SubMenuItem = MenuItem.Controls.Add _
>> >> > (Type:=msoControlButton)
>> >> > With SubMenuItem
>> >> > .Caption = "Set Print Range"
>> >> > .FaceId = 364
>> >> > .OnAction = "PrintRange"
>> >> > End With
>> >> > ' THIRD SUBMENU ITEM (Third Menu)
>> >> > Set SubMenuItem = MenuItem.Controls.Add _
>> >> > (Type:=msoControlButton)
>> >> > With SubMenuItem
>> >> > .Caption = "Chart Options"
>> >> > .FaceId = 435
>> >> > .OnAction = "ShowUserForm1"
>> >> > End With
>> >> > ' FOURTH SUBMENU ITEM (Third Menu)
>> >> > Set SubMenuItem = MenuItem.Controls.Add _
>> >> > (Type:=msoControlButton)
>> >> > With SubMenuItem
>> >> > .Caption = "Input Header and Footer Description"
>> >> > .FaceId = 237
>> >> > .OnAction = "Go_to_Titles"
>> >> > End With
>> >> >
>> >> > ' FORTH MENU ITEM
>> >> > Set MenuItem = NewMenu.Controls.Add _
>> >> > (Type:=msoControlButton)
>> >> > With MenuItem
>> >> > .Caption = "Chart Builder Help"
>> >> > .BeginGroup = True
>> >> > .OnAction = "ShowHelpForm"
>> >> > End With
>> >> >
>> >> > End Sub
>> >> >
>> >> > Sub DeleteMenu()
>> >> > On Error Resume Next
>> >> > CommandBars(1).Controls("Chart Builder Menu").Delete
>> >> > CommandBars(2).Controls("Chart Builder Menu").Delete
>> >> > End Sub
>> >> >
>> >> >
>> >> > --
>> >> > HTH
>> >> >
>> >> > Bob Phillips
>> >> >
>> >> > "Joel Mills" <[email protected]> wrote in message
>> >> > news:[email protected]...
>> >> >> Bob, Thanks for the reply, but I'm not very experience with VBA,
> this
>> > is
>> >> > my
>> >> >> first project. I am using it to learn VBA. I probably should have
>> >> > mentioned
>> >> >> that in this post. I did a google search and determined that there
>> >> >> are
>> >> > menu
>> >> >> bars and chart menu bars, but wasn't able to grasp how to get both
>> >> >> from
>> >> > the
>> >> >> same code. I'm still not sure how to revise my code.
>> >> >>
>> >> >>
>> >> >> "Bob Phillips" <[email protected]> wrote in message
>> >> >> news:%[email protected]...
>> >> >> > Repeat it for Commandbars(2). I would parameterise it and just
> pass
>> > the
>> >> > CB
>> >> >> > id to the routine in a caller routine.
>> >> >> >
>> >> >> > --
>> >> >> > HTH
>> >> >> >
>> >> >> > Bob Phillips
>> >> >> >
>> >> >> > "Joel Mills" <[email protected]> wrote in message
>> >> >> > news:[email protected]...
>> >> >> >> I have created a menu bar and would like for it to show up in
>> >> >> >> the
>> >> >> >> chart
>> >> >> >> sheets too. Below is the code for my menu. I'm not sure how to
>> >> >> >> revise
>> >> >> >> it
>> >> >> >> to also create a "Chart Menu Bar". When I close the workbook
> this
>> >> >> >> menu
>> >> >> >> is
>> >> >> >> deleted. I'm not sure if the sub Proceedure to delete the menu
>> > should
>> >> >> > also
>> >> >> >> be revised. Any help would be appreciated.
>> >> >> >>
>> >> >> >>
>> >> >> >> Sub CreateMenu()
>> >> >> >> Dim NewMenu As CommandBarPopup
>> >> >> >>
>> >> >> >> ' Delete the menu if it already exists
>> >> >> >> Call DeleteMenu
>> >> >> >>
>> >> >> >> ' Find the Help Menu
>> >> >> >> Set HelpMenu = CommandBars(1).FindControl(ID:=30010)
>> >> >> >>
>> >> >> >> If HelpMenu Is Nothing Then
>> >> >> >> ' Add the menu to the end
>> >> >> >> Set NewMenu = CommandBars(1).Controls.Add _
>> >> >> >> (Type:=msoControlPopup, _
>> >> >> >> Temporary:=True)
>> >> >> >> Else
>> >> >> >> ' Add the menu before Help
>> >> >> >> Set NewMenu = CommandBars(1).Controls.Add _
>> >> >> >> (Type:=msoControlPopup, _
>> >> >> >> Before:=HelpMenu.Index, _
>> >> >> >> Temporary:=True)
>> >> >> >> End If
>> >> >> >>
>> >> >> >> ' Add a caption for the menu
>> >> >> >> NewMenu.Caption = "&Chart Builder Menu"
>> >> >> >>
>> >> >> >> ' FIRST MENU ITEM
>> >> >> >> Set MenuItem = NewMenu.Controls.Add _
>> >> >> >> (Type:=msoControlPopup)
>> >> >> >> With MenuItem
>> >> >> >> .Caption = "Retreive Exported P3 File"
>> >> >> >> .BeginGroup = True
>> >> >> >> End With
>> >> >> >>
>> >> >> >> ' FIRST SUBMENU ITEM (First Menu)
>> >> >> >> Set SubMenuItem = MenuItem.Controls.Add _
>> >> >> >> (Type:=msoControlButton)
>> >> >> >> With SubMenuItem
>> >> >> >> .Caption = "From Local Drive"
>> >> >> >> .FaceId = 1021
>> >> >> >> .OnAction = "ExportCdrive"
>> >> >> >> End With
>> >> >> >>
>> >> >> >> ' SECOND SUBMENU ITEM (First Menu)
>> >> >> >> Set SubMenuItem = MenuItem.Controls.Add _
>> >> >> >> (Type:=msoControlButton)
>> >> >> >> With SubMenuItem
>> >> >> >> .Caption = "From Network Drive"
>> >> >> >> .FaceId = 140
>> >> >> >> .OnAction = "ExportNet"
>> >> >> >> End With
>> >> >> >>
>> >> >> >> ' SECOND MENU ITEM
>> >> >> >> Set MenuItem = NewMenu.Controls.Add _
>> >> >> >> (Type:=msoControlPopup)
>> >> >> >> With MenuItem
>> >> >> >> .Caption = "Populate (Pivot Tables and Charts)"
>> >> >> >> .BeginGroup = True
>> >> >> >> End With
>> >> >> >>
>> >> >> >> ' FIRST SUBMENU ITEM (Second Menu)
>> >> >> >> Set SubMenuItem = MenuItem.Controls.Add _
>> >> >> >> (Type:=msoControlButton)
>> >> >> >> With SubMenuItem
>> >> >> >> .Caption = "Copy Exported File (DBF)"
>> >> >> >> .FaceId = 1642
>> >> >> >> .OnAction = "CopyData"
>> >> >> >> End With
>> >> >> >> 'SECOND SUBMENU ITEM (Second Menu)
>> >> >> >> Set SubMenuItem = MenuItem.Controls.Add _
>> >> >> >> (Type:=msoControlButton)
>> >> >> >> With SubMenuItem
>> >> >> >> .Caption = "Create Database"
>> >> >> >> .FaceId = 333
>> >> >> >> .OnAction = "Cleanup"
>> >> >> >> End With
>> >> >> >> ' THIRD SUBMENU ITEM (Second Menu)
>> >> >> >> Set SubMenuItem = MenuItem.Controls.Add _
>> >> >> >> (Type:=msoControlButton)
>> >> >> >> With SubMenuItem
>> >> >> >> .Caption = "Create Pivot Tables"
>> >> >> >> .FaceId = 657
>> >> >> >> .OnAction = "CreatePivotTable"
>> >> >> >> End With
>> >> >> >> ' FORTH SUBMENU ITEM (Second Menu)
>> >> >> >> Set SubMenuItem = MenuItem.Controls.Add _
>> >> >> >> (Type:=msoControlButton)
>> >> >> >> With SubMenuItem
>> >> >> >> .Caption = "Populate Charts"
>> >> >> >> .FaceId = 433
>> >> >> >> .OnAction = "Populate_Charts"
>> >> >> >> End With
>> >> >> >>
>> >> >> >> ' THIRD MENU ITEM
>> >> >> >> Set MenuItem = NewMenu.Controls.Add _
>> >> >> >> (Type:=msoControlPopup)
>> >> >> >> With MenuItem
>> >> >> >> .Caption = "Chart Settings"
>> >> >> >> .BeginGroup = True
>> >> >> >> End With
>> >> >> >>
>> >> >> >> ' FIRST SUBMENU ITEM (Third Menu)
>> >> >> >> Set SubMenuItem = MenuItem.Controls.Add _
>> >> >> >> (Type:=msoControlButton)
>> >> >> >> With SubMenuItem
>> >> >> >> .Caption = "Resize Chart"
>> >> >> >> .FaceId = 442
>> >> >> >> .OnAction = "Resize_Chart"
>> >> >> >> End With
>> >> >> >>
>> >> >> >> ' SECOND SUBMENU ITEM (Third Menu)
>> >> >> >> Set SubMenuItem = MenuItem.Controls.Add _
>> >> >> >> (Type:=msoControlButton)
>> >> >> >> With SubMenuItem
>> >> >> >> .Caption = "Set Print Range"
>> >> >> >> .FaceId = 364
>> >> >> >> .OnAction = "PrintRange"
>> >> >> >> End With
>> >> >> >> ' THIRD SUBMENU ITEM (Third Menu)
>> >> >> >> Set SubMenuItem = MenuItem.Controls.Add _
>> >> >> >> (Type:=msoControlButton)
>> >> >> >> With SubMenuItem
>> >> >> >> .Caption = "Chart Options"
>> >> >> >> .FaceId = 435
>> >> >> >> .OnAction = "ShowUserForm1"
>> >> >> >> End With
>> >> >> >> ' FOURTH SUBMENU ITEM (Third Menu)
>> >> >> >> Set SubMenuItem = MenuItem.Controls.Add _
>> >> >> >> (Type:=msoControlButton)
>> >> >> >> With SubMenuItem
>> >> >> >> .Caption = "Input Header and Footer Description"
>> >> >> >> .FaceId = 237
>> >> >> >> .OnAction = "Go_to_Titles"
>> >> >> >> End With
>> >> >> >>
>> >> >> >> ' FORTH MENU ITEM
>> >> >> >> Set MenuItem = NewMenu.Controls.Add _
>> >> >> >> (Type:=msoControlButton)
>> >> >> >> With MenuItem
>> >> >> >> .Caption = "Chart Builder Help"
>> >> >> >> .BeginGroup = True
>> >> >> >> .OnAction = "ShowHelpForm"
>> >> >> >> End With
>> >> >> >>
>> >> >> >> End Sub
>> >> >> >>
>> >> >> >> Sub DeleteMenu()
>> >> >> >> On Error Resume Next
>> >> >> >> CommandBars(1).Controls("Chart Builder Menu").Delete
>> >> >> >> End Sub
>> >> >> >>
>> >> >> >>
>> >> >> >>
>> >> >> >> Private Sub Workbook_Open()
>> >> >> >> Run ([CreateMenu])
>> >> >> >> End Sub
>> >> >> >>
>> >> >> >>
>> >> >> >
>> >> >> >
>> >> >>
>> >> >>
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>>
>
>