Calling Sub with Parameter from Menuitem

[R Avery]

My problem is the following:
I want to create something similar to the "recent file list" in Excel,
except that I want my file list to be fairly constant (I know how to take
care of the updating, etc). But I cannot figure out how to make the
menuitem open the file specified by the path in the menuitem caption.

I have tried making the .OnAction property =
"myFile!DispMyName(""C:\...path..."")", with strange results. Whenever I
run the menuitem, it runs part of the procedure specified. The parameter
passing worked correctly. The sub printed the myName variable to the
immediate window. However, the 2nd statement did not work.

Strangely, this sub was run twice, printing the myName variable twice (and
when i substittuted MsgBox for debug.print, it msgbox'ed me twice). More
strangely, I put break points on every line in the code, but the code just
executed (twice) without stopping.

Is there a better way to do what I am thinking of? I only want there to be
a single procedure, if possible, that is called by the menuitems. If this
is not possible, having 9 almost-identical procedures is ok, but then I
would still need a way to properly pass the file path... perhaps if i had 9
procedures, they could each find the n'th menuitem, parse its caption, and
use that... but I am not even sure how to do that either.

Any help would be appreciated.


Public Sub DispMyName(Optional myName As String = "")
Debug.Print myName
Workbooks.Open myName
End Sub

 

[Tom Ogilvy]

You don't need to pass a parameter: Assuming the caption contains the fully
qualified filename:

Public Sub DispMyName()
Debug.Print CommandBars.ActionControl.Caption
Workbooks.Open CommandBars.ActionControl.Caption
End Sub

 

[R Avery]

Excellent. That is precisely what I needed.

Thanks!