Hi,

I'm using VB 6.3 with Excel 2000 in Win2000.

I am trying to create menu items that call procedures in modules. The
procedures have arguments, for example

Sub DoSomeThing(Arg1 As String, Arg2 As Boolean)

When creating the menu item, I have a pretty arcane attempt...


Set SubMenuItem = MenuItem.Controls.Add _
(Type:=msoControlButton)
With SubMenuItem
.Caption = "Process File"
.Tag = "blah"
'.OnAction = "'Module1.DoSomeThing """ & .Tag & """ False'"
End With

(picked this up from newsgroup but don't quite get it)

The message back is

The macro "C:\temp\ThisFile.xls'!'Module1.DoSomeThing "blah" False"
cannot be found.


Any ideas? Thanks in advance,

Max

Max,

The argument values must be separated a comma:

.OnAction = "'Module1.DoSomeThing """ & .Tag & """, False'"


--

John Green - Excel MVP
Sydney
Australia


"max" wrote in message om...
Hi,

I'm using VB 6.3 with Excel 2000 in Win2000.

I am trying to create menu items that call procedures in modules. The
procedures have arguments, for example

Sub DoSomeThing(Arg1 As String, Arg2 As Boolean)

When creating the menu item, I have a pretty arcane attempt...


Set SubMenuItem = MenuItem.Controls.Add _
(Type:=msoControlButton)
With SubMenuItem
.Caption = "Process File"
.Tag = "blah"
'.OnAction = "'Module1.DoSomeThing """ & .Tag & """ False'"
End With

(picked this up from newsgroup but don't quite get it)

The message back is

The macro "C:\temp\ThisFile.xls'!'Module1.DoSomeThing "blah" False"
cannot be found.


Any ideas? Thanks in advance,

Max

I'd do it slightly differently:

Creating the menu item:

Set SubMenuItem = MenuItem.Controls.Add _
(Type:=msoControlButton)
With SubMenuItem
.Caption = "Process File"
.Tag = "blah"
.Parameter = "False"
.OnAction = "DoSomeThing"
End With

In the macro:

Public Sub DoSomething()
Dim Arg1 As String
Dim Arg2 As Boolean

With Application.ActiveControl
Arg1 = .Tag
Arg2 = (.Parameter = "True")
End With



In article ,
(max) wrote:

Hi,

I'm using VB 6.3 with Excel 2000 in Win2000.

I am trying to create menu items that call procedures in modules. The
procedures have arguments, for example

Sub DoSomeThing(Arg1 As String, Arg2 As Boolean)

When creating the menu item, I have a pretty arcane attempt...


Set SubMenuItem = MenuItem.Controls.Add _
(Type:=msoControlButton)
With SubMenuItem
.Caption = "Process File"
.Tag = "blah"
'.OnAction = "'Module1.DoSomeThing """ & .Tag & """ False'"
End With

(picked this up from newsgroup but don't quite get it)

The message back is

The macro "C:\temp\ThisFile.xls'!'Module1.DoSomeThing "blah" False"
cannot be found.


Any ideas? Thanks in advance,

Max

John and JE,

Thanks for your replies. I finally got it to work.

John - I tried the comma and it didn't work on my machine (but
strangely, it did work on someone else's).

JE - I couldn't find ActiveControl documented anywhere (and the code
bombed on it).

What did work was

Application.CommandBars.ActionControl.Tag

and

Application.CommandBars.ActionControl.Parameter

Thanks for the help,

Max

thanks for the correction - that's what happens when I fail to test my
memory (doesn't happen often - I test most everything).

In article ,
(max) wrote:

JE - I couldn't find ActiveControl documented anywhere (and the code
bombed on it).

What did work was

Application.CommandBars.ActionControl.Tag

and

Application.CommandBars.ActionControl.Parameter

Thanks for the help,