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

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

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



.