Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
menu bar & chart menu bar
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, _ Befo=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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
menu bar & chart menu bar
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" wrote in message ... 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, _ Befo=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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
menu bar & chart menu bar
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" wrote in message ... 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" wrote in message ... 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, _ Befo=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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
menu bar & chart menu bar
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, _ Befo=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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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, _ Befo=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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
menu bar & chart menu bar
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, _ Befo=HelpMenu.Index, _ Temporary:=True) "Bob Phillips" wrote in message ... 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, _ Befo=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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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, _ Befo=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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
menu bar & chart menu bar
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, _ Befo=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" wrote in message ... 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, _ Befo=HelpMenu.Index, _ Temporary:=True) "Bob Phillips" wrote in message ... 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, _ Befo=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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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, _ Befo=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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
menu bar & chart menu bar
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" wrote in message ... 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, _ Befo=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" wrote in message ... 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, _ Befo=HelpMenu.Index, _ Temporary:=True) "Bob Phillips" wrote in message ... 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, _ Befo=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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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, _ Befo=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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
menu bar & chart menu bar
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" wrote in message ... 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" wrote in message ... 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, _ Befo=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" wrote in message ... 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, _ Befo=HelpMenu.Index, _ Temporary:=True) "Bob Phillips" wrote in message ... 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, _ Befo=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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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, _ Befo=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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
menu bar & chart menu bar
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" wrote in message ... 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, _ Befo=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" wrote in message ... 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, _ Befo=HelpMenu.Index, _ Temporary:=True) "Bob Phillips" wrote in message ... 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, _ Befo=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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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, _ Befo=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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
menu bar & chart menu bar
Thanks, I just figured out the problem. Didn't see your solution until I
sent my reply to the group. "Bob Phillips" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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, _ Befo=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" wrote in message ... 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, _ Befo=HelpMenu.Index, _ Temporary:=True) "Bob Phillips" wrote in message ... 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, _ Befo=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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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, _ Befo=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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
menu bar & chart menu bar
I revised to your solution. Is much easier to see what is happening with
your code. Joel "Bob Phillips" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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, _ Befo=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" wrote in message ... 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, _ Befo=HelpMenu.Index, _ Temporary:=True) "Bob Phillips" wrote in message ... 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, _ Befo=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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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, _ Befo=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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Drop dwn menu. Formula to count selection frm menu in anoth cell? | Excel Worksheet Functions | |||
New Menu on Worksheet & Chart Menu Bars | Excel Programming | |||
linking chart menu bars and worksheet menu bars | Excel Programming | |||
chart menu bar vs worksheet menu bar | Excel Programming | |||
Add control to the Chart menu of "Chart Menu Bar" commandbar | Excel Programming |