As far as learning to work with Commandbars goes, I would suggest you factor
in your plans for Office 2007. That product uses something different
altogether -- called the Ribbon in the UI and RibbonX on the developer side.
So, if you intend transitioning to 2007 in the near future you may be able
to skip learning about Commandbars altogether. ;-)

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
Bob,
Thanks for the quick reply. I suppose that I will have to drag myself out of
the stone age and recode my menu system. I'll giv it a try in a bit, once
that I have got my head round how I can build it from a 'table of contents'
area on a worksheet.
Thanks again. I'll let you know how I got on.

Trevor

"Bob Phillips" wrote:

Time to switch Commandbars

Sub AddMenu2()
Dim FileMenu
With Application.CommandBars("Worksheet Menu Bar")
On Error Resume Next
.Controls("Test").Delete
On Error GoTo 0
Dim i
' Find the File menu
Set FileMenu = CommandBars(1).FindControl(ID:=30002)
With .Controls.Add(Type:=msoControlPopup, befo=FileMenu.Index,
temporary:=True)
.Caption = "Test"
With .Controls.Add(Type:=msoControlButton, temporary:=True)
.Caption = "Test1"
.OnAction = "Main"
.Tag = 1
End With
With .Controls.Add(Type:=msoControlButton, temporary:=True)
.Caption = "Test2"
.OnAction = "Main"
.Tag = 2
End With
'(etc.)
End With
End With
End Sub


Sub Main()
Dim intTest As Integer
'Use intTest as a row lookup parameter _
and do something with it
intTest = Application.CommandBars.ActionControl.Tag
MsgBox intTest
End Sub



--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"TrevorJ" wrote in message
...
Hi,
I have developed an Excel application that uses quite an extensive menu
system to 'work' it. I have not been able to get my head round the 'new'
command bars syntax, so have used the stoneage MenuBars code.
What I am trying to do is pass a single Proc a parameter from multiple
menu
lines. I have not achieved this and have the following working code at
present.

Sub AddMenu2()
MenuBars(xlWorksheet).Menus.Add Caption:="Test", befo="File"
With MenuBars(xlWorksheet).Menus("Test").MenuItems
.Add Caption:="Test1", OnAction:="Test1"
.Add Caption:="Test2", OnAction:="Test2"
(etc.)
End With
End Sub

Sub Test1()
Call Main(1)
End Sub

Sub Test2()
Call Main(2)
End Sub
(Etc.)

Sub Main(intTest As Integer)
'Use intTest as a row lookup parameter _
and do something with it
End Sub

What I want to do is cut out the middle Procs (Test1, Test2 etc) and call
the 'Main' proc directly from the menu, passing it the integer parameter
at
the same time. Such as like this:-

.Add Caption:="Test1", OnAction:="Main(1)"
.Add Caption:="Test2", OnAction:="Main(2)"

Which obviously does not work.
I have done this before, but:-
a. Can't remember how, and experimenting leads to failure
b. I think that it seemed to call the 'Main' twice for some reason or
another.

TIA
Trevor