Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
New Menu on Worksheet & Chart Menu Bars
Hi All:
The following code was created to add a new item on the tools menu bar from the worksheet menu bar, I soon found the need for using it while inside a chart so I tryed adding the "For Loop" so that the item would be created in both Worksheet & Chart Menu Bar... I can't figure out why this code won't cycle to create the item in both menubars... it works with simpler requirements but this in particular search first for the menu inside the tools menu and if it exists it uses it, if not it creates it... this is because I have more than one addinn that place a tool inside the same menu... I know that if I replace this part: On Error Resume Next Set NewMenuInXLMenu = XLMenu.Controls(NewMenuName) On Error GoTo 0 If NewMenuInXLMenu Is Nothing Then Set NewMenuInXLMenu = XLMenu.Controls.Add (Type:=msoControlPopup, temporary:=True) Else End If With On Error Resume Next XLMenu.Controls(NewMenuName).Delete On Error GoTo 0 Set NewMenuInXLMenu = XLMenu.Controls.Add it works... but then I can't use the existing menu if it exists...and I will delete existing tools if already there... Any help is greatly appreciated... Regards JS... here's the code: Sub CreateMenu() Dim XLCB(1) As CommandBar Dim XLMenu As CommandBarControl Dim NewMenuInXLMenu As CommandBarControl Dim NewItemInNewMenu As CommandBarButton Dim NewMenuName As String Dim NewItemName As String Dim Count As Integer NewMenuName = "New Menu Name" NewItemName = "New Item" Set XLCB(0) = Application.CommandBars("Worksheet Menu Bar") Set XLCB(1) = Application.CommandBars("Chart Menu Bar") For Count = 0 To 1 Set XLMenu = XLCB(Count).FindControl(msoControlPopup, 30007) On Error Resume Next Set NewMenuInXLMenu = XLMenu.Controls(NewMenuName) On Error GoTo 0 If NewMenuInXLMenu Is Nothing Then Set NewMenuInXLMenu = XLMenu.Controls.Add (Type:=msoControlPopup, temporary:=True) Else End If With NewMenuInXLMenu .Caption = NewMenuName .BeginGroup = True End With On Error Resume Next NewMenuInXLMenu.Controls(NewItemName).Delete On Error GoTo 0 Set NewItemInNewMenu = NewMenuInXLMenu.Controls.Add With NewItemInNewMenu .Caption = NewItemName .OnAction = "Macro to perform!" End With Next Count End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
New Menu on Worksheet & Chart Menu Bars
JS,
Set the variable NewMenuInXLMenu to Nothing at the end of the For Next loop. The second time through, it is not Nothing, so it doesn't get reset, but it is not pointing to the latest commandbar either. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Juan" wrote in message ... Hi All: The following code was created to add a new item on the tools menu bar from the worksheet menu bar, I soon found the need for using it while inside a chart so I tryed adding the "For Loop" so that the item would be created in both Worksheet & Chart Menu Bar... I can't figure out why this code won't cycle to create the item in both menubars... it works with simpler requirements but this in particular search first for the menu inside the tools menu and if it exists it uses it, if not it creates it... this is because I have more than one addinn that place a tool inside the same menu... I know that if I replace this part: On Error Resume Next Set NewMenuInXLMenu = XLMenu.Controls(NewMenuName) On Error GoTo 0 If NewMenuInXLMenu Is Nothing Then Set NewMenuInXLMenu = XLMenu.Controls.Add (Type:=msoControlPopup, temporary:=True) Else End If With On Error Resume Next XLMenu.Controls(NewMenuName).Delete On Error GoTo 0 Set NewMenuInXLMenu = XLMenu.Controls.Add it works... but then I can't use the existing menu if it exists...and I will delete existing tools if already there... Any help is greatly appreciated... Regards JS... here's the code: Sub CreateMenu() Dim XLCB(1) As CommandBar Dim XLMenu As CommandBarControl Dim NewMenuInXLMenu As CommandBarControl Dim NewItemInNewMenu As CommandBarButton Dim NewMenuName As String Dim NewItemName As String Dim Count As Integer NewMenuName = "New Menu Name" NewItemName = "New Item" Set XLCB(0) = Application.CommandBars("Worksheet Menu Bar") Set XLCB(1) = Application.CommandBars("Chart Menu Bar") For Count = 0 To 1 Set XLMenu = XLCB(Count).FindControl(msoControlPopup, 30007) On Error Resume Next Set NewMenuInXLMenu = XLMenu.Controls(NewMenuName) On Error GoTo 0 If NewMenuInXLMenu Is Nothing Then Set NewMenuInXLMenu = XLMenu.Controls.Add (Type:=msoControlPopup, temporary:=True) Else End If With NewMenuInXLMenu .Caption = NewMenuName .BeginGroup = True End With On Error Resume Next NewMenuInXLMenu.Controls(NewItemName).Delete On Error GoTo 0 Set NewItemInNewMenu = NewMenuInXLMenu.Controls.Add With NewItemInNewMenu .Caption = NewItemName .OnAction = "Macro to perform!" End With Next Count End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
New Menu on Worksheet & Chart Menu Bars
Yes, of course... thanks Bob...its working great now...
"obvious scapes the mind"... ;-) JS -----Original Message----- JS, Set the variable NewMenuInXLMenu to Nothing at the end of the For Next loop. The second time through, it is not Nothing, so it doesn't get reset, but it is not pointing to the latest commandbar either. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Juan" wrote in message ... Hi All: The following code was created to add a new item on the tools menu bar from the worksheet menu bar, I soon found the need for using it while inside a chart so I tryed adding the "For Loop" so that the item would be created in both Worksheet & Chart Menu Bar... I can't figure out why this code won't cycle to create the item in both menubars... it works with simpler requirements but this in particular search first for the menu inside the tools menu and if it exists it uses it, if not it creates it... this is because I have more than one addinn that place a tool inside the same menu... I know that if I replace this part: On Error Resume Next Set NewMenuInXLMenu = XLMenu.Controls(NewMenuName) On Error GoTo 0 If NewMenuInXLMenu Is Nothing Then Set NewMenuInXLMenu = XLMenu.Controls.Add (Type:=msoControlPopup, temporary:=True) Else End If With On Error Resume Next XLMenu.Controls(NewMenuName).Delete On Error GoTo 0 Set NewMenuInXLMenu = XLMenu.Controls.Add it works... but then I can't use the existing menu if it exists...and I will delete existing tools if already there... Any help is greatly appreciated... Regards JS... here's the code: Sub CreateMenu() Dim XLCB(1) As CommandBar Dim XLMenu As CommandBarControl Dim NewMenuInXLMenu As CommandBarControl Dim NewItemInNewMenu As CommandBarButton Dim NewMenuName As String Dim NewItemName As String Dim Count As Integer NewMenuName = "New Menu Name" NewItemName = "New Item" Set XLCB(0) = Application.CommandBars("Worksheet Menu Bar") Set XLCB(1) = Application.CommandBars("Chart Menu Bar") For Count = 0 To 1 Set XLMenu = XLCB(Count).FindControl(msoControlPopup, 30007) On Error Resume Next Set NewMenuInXLMenu = XLMenu.Controls(NewMenuName) On Error GoTo 0 If NewMenuInXLMenu Is Nothing Then Set NewMenuInXLMenu = XLMenu.Controls.Add (Type:=msoControlPopup, temporary:=True) Else End If With NewMenuInXLMenu .Caption = NewMenuName .BeginGroup = True End With On Error Resume Next NewMenuInXLMenu.Controls(NewItemName).Delete On Error GoTo 0 Set NewItemInNewMenu = NewMenuInXLMenu.Controls.Add With NewItemInNewMenu .Caption = NewItemName .OnAction = "Macro to perform!" End With Next Count End Sub . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to open a worksheet without Menu, Formula, Scroll Bars etc... | Excel Discussion (Misc queries) | |||
Lost all my menu bars and tool bars | Excel Discussion (Misc queries) | |||
linking chart menu bars and worksheet menu bars | Excel Programming | |||
menu bars | Excel Programming | |||
chart menu bar vs worksheet menu bar | Excel Programming |