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
|