OnAction setting for menu item

[JonWayne]

If the calling function of a CommandBars menu item requires a few string
arguments, how do you enter the OnAction property setting for that menu
item? Lets say the function was declared as :

Function DownloadFile(SiteAddress$, Login$, Password$, SomeNum%, FileSpec$)

 

[Tushar Mehta]

I first saw this technique illustrated by Tom Ogilvy.

Use
Dim y As CommandBarButton
...
y.OnAction = "'ParamSub ""hello"",""there"",1'"

where ParamSub is declared as

Sub ParamSub(x As String, y As String, i As Integer)
...
End Sub

--
Regards,

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

 

[JonWayne]

I think I should post my codes just in case Iam missing something. I tried
your suggestion and it didnt work. I tried it with and without parentheses
around the arguments.

Sub AddDownloadCommandToPIDMenu()
Dim NuBar As Object, MBar As Object, cbrs As Object, PBar As Object,
DBar As Object

Const wgPopupControl% = 10, wgMenuButton% = 1

Set cbrs = CommandBars
Set MBar = CommandBars("Menu Bar")
Set PBar = MBar.FindControl(wgPopupControl, , "PID")
On Error Resume Next

With PBar.Controls
On Error Resume Next
Set DBar = PBar.Controls("Download Duval Data File")
If Not DBar Is Nothing Then DBar.Delete
Set NuBar = .Add(wgMenuButton, , , , True)

With NuBar
.Caption = "Download Data Files"
.OnAction =
"'MenuCommand_DownloadFileFromPIDSite(""somewebaddress.net"",""powerp"",""po
wer2000"",""WAC"",""Trans"",""TrafCivil????????.txt"",""Traffic"",""Jail
Data\Admits"",""20??????.txt"",""Criminal"")'"
End With
End With

Set PBar = Nothing
Set MBar = Nothing
Set NuBar = Nothing
End Sub

And my function declaration looks something like:

Function MenuCommand_DownloadFileFromPIDSite(WebSiteAddress$, Login$,
Password$, AllCountyMoniker$, _
ParamArray Location_FilePattern_Dataset_Trio())

 

[Tushar Mehta]

What does "it didn't work" mean?

I had tested the example I posted and I just tested with a paramarray,
which also worked. The parenthesis you have to lose.

You may also want to lose all the 'On Error Resume Next' statements.
All that they do is mask problems with the code.

And, the constants you are using don't look anything like the mso_
constants typically associated with Office commandbars. For example,
to add a new control in my test, I used

Set y = x.Controls.Add(msoControlButton, Temporary:=True)

You may want to check the help for more on these constants.

--
Regards,

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

 

[JonWayne]

Oh, the menu item gets created alright. It just closes the application when
I click on it. The constants therefore are correct. I did ensure the value
of them before I used them. In fact, I had a working version of the
function, identical except without arguments. I added arguments in order to
adapt it to multiple applications.

 

[JonWayne]

By the way, I redeclared the mso constants because I cant have a reference
to the MSOffice type library, because the functions run on multiple PCs some
of which dont have this reference and its simpler.

 

[JonWayne]

I figured it out, and, just in case others here run into a similar need,
I'll share the knowledge, as I have been helped here a lot. The solution is:
The whole string needs to be enclosed in double-quotes and should be set to
an equal sign (=), followed by the function name followed by the
parenthesized argument list, with each string argument enclosed in either
single single-quotes, or double double-quotes.
Example: SomeButtonControl.OnAction = "=SomeFunc('Arg1', 'Arg2')" or
"=SomeFunc(""Arg1"", ""Arg2"")". I knew I had this problem some time ago
and found the solution, but then I forgot it again.

 

[Tushar Mehta]

Thanks for sharing your experience, but I've used the syntax I shared
with you first for several years with various versions of XL now with
no problems.

After your post, I did try the variant starting with the equal sign.
It worked -- kinda. XL called the subroutine *twice.* Not what I
would want.

So, I will stick with the true and tried version. {grin}

--
Regards,

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

 

[JonWayne]

I dont know that it should make a difference, but I am using MSAccess. The
commandBars object model should be the same across board. The function runs
only once at my end