Calling Sub with Parameter from Menuitem
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 |
Calling Sub with Parameter from Menuitem
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 -- Regards, Tom Ogilvy "R Avery" wrote in message ... 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 |
Calling Sub with Parameter from Menuitem
Excellent. That is precisely what I needed.
Thanks! |
All times are GMT +1. The time now is 01:13 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com