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

Take a look at http://www.j-walk.com/ss/excel/tips/tip53.htm

--
HTH

Bob Phillips

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

"TrevorJ" wrote in message
...
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

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

Tushar,
That's a good point.
However, the other option is to not make the transition.
I am still trying to decide.
Regards,
Jim Cone
San Francisco, USA


"Tushar Mehta"

wrote in message
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

They are supported, but they don't appear as they do in say 2003. They get
installed in the Developer tab as buttons. And it's a darn sight easier to
create commandbars than playing with the ribbon.

--
HTH

Bob Phillips

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

"TrevorJ" wrote in message
...
Ok Ok, there's always a catch with the nice Mr. Gates' software isnt
there.
As menu bars are still supported in O 2003, is it reasonably safe to
assume
that command bars will be backwards compatible with O2k7?

Thanks to the Command bar help that I have had, I've solved my immidiate
problem, but O2k7?????

Trevor

"Tushar Mehta" wrote:

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

Exactly Trevor. Many years ago I used it and added about 6 columns, tag,
parameter, a picture, etc. As I recall, adding extra columns wasn't as
simple as (I think) it should be, but it worked eventually. After a while,
it wasn't flexible enough for me (I wanted VBA menus as well, so I built my
own, but it did me well for a while.

--
HTH

Bob Phillips

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

"TrevorJ" wrote in message
...
Bob,
That link is realy good. It's more or less what I am doing at the moment,
but much better. Presumably I could add one more column to the table and
include a Tag value and modify the code slightly to include:-

Case 2 ' A Menu Item
If NextLevel = 3 Then
Set MenuItem =
MenuObject.Controls.Add(Type:=msoControlPopup)
Else
Set MenuItem =
MenuObject.Controls.Add(Type:=msoControlButton)
MenuItem.OnAction = PositionOrMacro
MenuItem.Tag = <value extracted from extra column
End If

to achieve a solution to my original problem?
I'll give it a go to see what happens.
Thanks again

Trevor

"Bob Phillips" wrote:

Take a look at http://www.j-walk.com/ss/excel/tips/tip53.htm

--
HTH

Bob Phillips

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

"TrevorJ" wrote in message
...
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