Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting OnAction of custom menu item?
I'm using a Visual Basic app to open a workbook and set a custom menu in it.
It works okay, as far as I have it (thanks to much help from the Excel and VB NG gurus!). The idea driving this is to remove all code from the workbook - it's causing errors for my users. Now I need to set the OnAction property of my menu items. Must OnAction always refer to a macro in the workbook? Can I set OnAction to refer back to a sub in the VB app? If this is possible, how would this look when coded? Ed |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting OnAction of custom menu item?
Ed,
It can't refer back to a VB App. Think about it, the VB app will run, create the toolbar and then finish. The button could be clicked at any time after, and there is no app to call back into. You can run a macro in the same workbook, another workbook, and you could even have a macro that is simply a bridge into a DLL if you so wanted, but not your parent app as far as I can see. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Ed" wrote in message ... I'm using a Visual Basic app to open a workbook and set a custom menu in it. It works okay, as far as I have it (thanks to much help from the Excel and VB NG gurus!). The idea driving this is to remove all code from the workbook - it's causing errors for my users. Now I need to set the OnAction property of my menu items. Must OnAction always refer to a macro in the workbook? Can I set OnAction to refer back to a sub in the VB app? If this is possible, how would this look when coded? Ed |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting OnAction of custom menu item?
Thanks, Bob. Time for Plan B.
Ed "Bob Phillips" wrote in message ... Ed, It can't refer back to a VB App. Think about it, the VB app will run, create the toolbar and then finish. The button could be clicked at any time after, and there is no app to call back into. You can run a macro in the same workbook, another workbook, and you could even have a macro that is simply a bridge into a DLL if you so wanted, but not your parent app as far as I can see. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Ed" wrote in message ... I'm using a Visual Basic app to open a workbook and set a custom menu in it. It works okay, as far as I have it (thanks to much help from the Excel and VB NG gurus!). The idea driving this is to remove all code from the workbook - it's causing errors for my users. Now I need to set the OnAction property of my menu items. Must OnAction always refer to a macro in the workbook? Can I set OnAction to refer back to a sub in the VB app? If this is possible, how would this look when coded? Ed |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting OnAction of custom menu item?
You could copy the macro into the workbook.
-- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Ed" wrote in message ... Thanks, Bob. Time for Plan B. Ed "Bob Phillips" wrote in message ... Ed, It can't refer back to a VB App. Think about it, the VB app will run, create the toolbar and then finish. The button could be clicked at any time after, and there is no app to call back into. You can run a macro in the same workbook, another workbook, and you could even have a macro that is simply a bridge into a DLL if you so wanted, but not your parent app as far as I can see. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Ed" wrote in message ... I'm using a Visual Basic app to open a workbook and set a custom menu in it. It works okay, as far as I have it (thanks to much help from the Excel and VB NG gurus!). The idea driving this is to remove all code from the workbook - it's causing errors for my users. Now I need to set the OnAction property of my menu items. Must OnAction always refer to a macro in the workbook? Can I set OnAction to refer back to a sub in the VB app? If this is possible, how would this look when coded? Ed |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting OnAction of custom menu item?
Well, I was trying to present a workbook with no code in it. But I think I
can get away with some. Right now, though, I set OnAction in the Visual Basic app as .OnAction = "objWkbk.Sheet1!GetTIR" where "objWkbk" is the Excel file opened by the VB app. But when I run the macro in the workbook, I get the error "Can't find "objWkbk.Sheet1.xls". The other thing I noticed is that, unlike double-clicking the icon on my desktop, when the VB app opens this file, my Personal.xls doesn't open. When I click a custom button tied to a macro in Personal, Personal has to open and then the macro runs. But this macro is in this workbook - still I wonder if something isn't messing with my VBA functions. Ed "Bob Phillips" wrote in message ... You could copy the macro into the workbook. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Ed" wrote in message ... Thanks, Bob. Time for Plan B. Ed "Bob Phillips" wrote in message ... Ed, It can't refer back to a VB App. Think about it, the VB app will run, create the toolbar and then finish. The button could be clicked at any time after, and there is no app to call back into. You can run a macro in the same workbook, another workbook, and you could even have a macro that is simply a bridge into a DLL if you so wanted, but not your parent app as far as I can see. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Ed" wrote in message ... I'm using a Visual Basic app to open a workbook and set a custom menu in it. It works okay, as far as I have it (thanks to much help from the Excel and VB NG gurus!). The idea driving this is to remove all code from the workbook - it's causing errors for my users. Now I need to set the OnAction property of my menu items. Must OnAction always refer to a macro in the workbook? Can I set OnAction to refer back to a sub in the VB app? If this is possible, how would this look when coded? Ed |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting OnAction of custom menu item?
Found the error in this:
I set OnAction in the Visual Basic app as .OnAction = "objWkbk.Sheet1!GetTIR" where "objWkbk" is the Excel file opened by the VB app. But when I run the macro in the workbook, I get the error "Can't find "objWkbk.Sheet1.xls". Of course not! objWkbk is the *object* reference, not the *filename string*! D'oh! So I changed it. Now I get an error '400', whatever that means. The macro is there, and it does run when called. I just can't get this menu item to call it. Ed |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting OnAction of custom menu item?
Ed,
When you startup Excel from Automation, it will not load any files in XLStart, so no Personal.xls, or anything else there. Same applies to installed add-ins. It's a performance thing I guess. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Ed" wrote in message ... Well, I was trying to present a workbook with no code in it. But I think I can get away with some. Right now, though, I set OnAction in the Visual Basic app as .OnAction = "objWkbk.Sheet1!GetTIR" where "objWkbk" is the Excel file opened by the VB app. But when I run the macro in the workbook, I get the error "Can't find "objWkbk.Sheet1.xls". The other thing I noticed is that, unlike double-clicking the icon on my desktop, when the VB app opens this file, my Personal.xls doesn't open. When I click a custom button tied to a macro in Personal, Personal has to open and then the macro runs. But this macro is in this workbook - still I wonder if something isn't messing with my VBA functions. Ed "Bob Phillips" wrote in message ... You could copy the macro into the workbook. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Ed" wrote in message ... Thanks, Bob. Time for Plan B. Ed "Bob Phillips" wrote in message ... Ed, It can't refer back to a VB App. Think about it, the VB app will run, create the toolbar and then finish. The button could be clicked at any time after, and there is no app to call back into. You can run a macro in the same workbook, another workbook, and you could even have a macro that is simply a bridge into a DLL if you so wanted, but not your parent app as far as I can see. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Ed" wrote in message ... I'm using a Visual Basic app to open a workbook and set a custom menu in it. It works okay, as far as I have it (thanks to much help from the Excel and VB NG gurus!). The idea driving this is to remove all code from the workbook - it's causing errors for my users. Now I need to set the OnAction property of my menu items. Must OnAction always refer to a macro in the workbook? Can I set OnAction to refer back to a sub in the VB app? If this is possible, how would this look when coded? Ed |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting OnAction of custom menu item?
<<It's a performance thing I guess.
Hi Bob, Actually it's a safety thing. If you're automating Excel on some arbitrary user's desktop you don't know what the programs they're loading on startup may do. If your program was using the default invisible instance of Excel and one of the user's startup programs ran code that required UI interaction (like clicking OK on a message box or something) you'd be locked up with nowhere to go. There are lots of other scenarios where arbitrary user applications loading on startup could really hose an automation session, so MS designed it this way to avoid any of that from happening. -- Rob Bovey, MCSE, MCSD, Excel MVP Application Professionals http://www.appspro.com/ * Please post all replies to this newsgroup * * I delete all unsolicited e-mail responses * "Bob Phillips" wrote in message ... Ed, When you startup Excel from Automation, it will not load any files in XLStart, so no Personal.xls, or anything else there. Same applies to installed add-ins. It's a performance thing I guess. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Ed" wrote in message ... Well, I was trying to present a workbook with no code in it. But I think I can get away with some. Right now, though, I set OnAction in the Visual Basic app as .OnAction = "objWkbk.Sheet1!GetTIR" where "objWkbk" is the Excel file opened by the VB app. But when I run the macro in the workbook, I get the error "Can't find "objWkbk.Sheet1.xls". The other thing I noticed is that, unlike double-clicking the icon on my desktop, when the VB app opens this file, my Personal.xls doesn't open. When I click a custom button tied to a macro in Personal, Personal has to open and then the macro runs. But this macro is in this workbook - still I wonder if something isn't messing with my VBA functions. Ed "Bob Phillips" wrote in message ... You could copy the macro into the workbook. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Ed" wrote in message ... Thanks, Bob. Time for Plan B. Ed "Bob Phillips" wrote in message ... Ed, It can't refer back to a VB App. Think about it, the VB app will run, create the toolbar and then finish. The button could be clicked at any time after, and there is no app to call back into. You can run a macro in the same workbook, another workbook, and you could even have a macro that is simply a bridge into a DLL if you so wanted, but not your parent app as far as I can see. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Ed" wrote in message ... I'm using a Visual Basic app to open a workbook and set a custom menu in it. It works okay, as far as I have it (thanks to much help from the Excel and VB NG gurus!). The idea driving this is to remove all code from the workbook - it's causing errors for my users. Now I need to set the OnAction property of my menu items. Must OnAction always refer to a macro in the workbook? Can I set OnAction to refer back to a sub in the VB app? If this is possible, how would this look when coded? Ed |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting OnAction of custom menu item?
Not tried this Ed, but if we just work it through. objWkbk is the object
variable in your VB app, which is totally meaningless to the commandbar and its OnAction property. Running a macro in another workbook is in essence of the form Application.Run "myBook.xls'!myMacro" so we need to build this up. All we have is an object and an implicit macro name. We need the workbook name, which we can get from the object. So something like .OnAction = "'" & objWkbk.Name & "'!GetTIR" -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Ed" wrote in message ... Found the error in this: I set OnAction in the Visual Basic app as .OnAction = "objWkbk.Sheet1!GetTIR" where "objWkbk" is the Excel file opened by the VB app. But when I run the macro in the workbook, I get the error "Can't find "objWkbk.Sheet1.xls". Of course not! objWkbk is the *object* reference, not the *filename string*! D'oh! So I changed it. Now I get an error '400', whatever that means. The macro is there, and it does run when called. I just can't get this menu item to call it. Ed |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting OnAction of custom menu item?
Hi Rob,
Thanks for that clarification, it makes sense. I worked on the premise that Automation took a high performance hit, so MS mitigated as best they could. As for locking up, done that many times running Excel on servers! -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Rob Bovey" wrote in message ... <<It's a performance thing I guess. Hi Bob, Actually it's a safety thing. If you're automating Excel on some arbitrary user's desktop you don't know what the programs they're loading on startup may do. If your program was using the default invisible instance of Excel and one of the user's startup programs ran code that required UI interaction (like clicking OK on a message box or something) you'd be locked up with nowhere to go. There are lots of other scenarios where arbitrary user applications loading on startup could really hose an automation session, so MS designed it this way to avoid any of that from happening. -- Rob Bovey, MCSE, MCSD, Excel MVP Application Professionals http://www.appspro.com/ * Please post all replies to this newsgroup * * I delete all unsolicited e-mail responses * "Bob Phillips" wrote in message ... Ed, When you startup Excel from Automation, it will not load any files in XLStart, so no Personal.xls, or anything else there. Same applies to installed add-ins. It's a performance thing I guess. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Ed" wrote in message ... Well, I was trying to present a workbook with no code in it. But I think I can get away with some. Right now, though, I set OnAction in the Visual Basic app as .OnAction = "objWkbk.Sheet1!GetTIR" where "objWkbk" is the Excel file opened by the VB app. But when I run the macro in the workbook, I get the error "Can't find "objWkbk.Sheet1.xls". The other thing I noticed is that, unlike double-clicking the icon on my desktop, when the VB app opens this file, my Personal.xls doesn't open. When I click a custom button tied to a macro in Personal, Personal has to open and then the macro runs. But this macro is in this workbook - still I wonder if something isn't messing with my VBA functions. Ed "Bob Phillips" wrote in message ... You could copy the macro into the workbook. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Ed" wrote in message ... Thanks, Bob. Time for Plan B. Ed "Bob Phillips" wrote in message ... Ed, It can't refer back to a VB App. Think about it, the VB app will run, create the toolbar and then finish. The button could be clicked at any time after, and there is no app to call back into. You can run a macro in the same workbook, another workbook, and you could even have a macro that is simply a bridge into a DLL if you so wanted, but not your parent app as far as I can see. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Ed" wrote in message ... I'm using a Visual Basic app to open a workbook and set a custom menu in it. It works okay, as far as I have it (thanks to much help from the Excel and VB NG gurus!). The idea driving this is to remove all code from the workbook - it's causing errors for my users. Now I need to set the OnAction property of my menu items. Must OnAction always refer to a macro in the workbook? Can I set OnAction to refer back to a sub in the VB app? If this is possible, how would this look when coded? Ed |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting OnAction of custom menu item?
Bob - sorry for the lat response. I had to bug out of work early and didn't
catch your message until now at home. I'll try to work through that first thing Monday morning. I left out (oops!) that I had tried substituting the string that held the name of the workbook to open, but I'm not sure if I've got the syntax correct, or if I'm using the wrong string (one with the file path vs. just the file name). Another Monday morning action item. But let me also clarify what you gave me: .OnAction = "'" & objWkbk.Name & "'!GetTIR" that's double-quote single-quote double quote & name & double-quote single-quote !macro double-quote ? Are there any rules to the quotes? Or do you just keep throwing them in until VB is happy?!? 8\ Thanks for keeping with me on this. Ed "Bob Phillips" wrote in message ... Not tried this Ed, but if we just work it through. objWkbk is the object variable in your VB app, which is totally meaningless to the commandbar and its OnAction property. Running a macro in another workbook is in essence of the form Application.Run "myBook.xls'!myMacro" so we need to build this up. All we have is an object and an implicit macro name. We need the workbook name, which we can get from the object. So something like .OnAction = "'" & objWkbk.Name & "'!GetTIR" -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Ed" wrote in message ... Found the error in this: I set OnAction in the Visual Basic app as .OnAction = "objWkbk.Sheet1!GetTIR" where "objWkbk" is the Excel file opened by the VB app. But when I run the macro in the workbook, I get the error "Can't find "objWkbk.Sheet1.xls". Of course not! objWkbk is the *object* reference, not the *filename string*! D'oh! So I changed it. Now I get an error '400', whatever that means. The macro is there, and it does run when called. I just can't get this menu item to call it. Ed |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting OnAction of custom menu item?
Hi Ed,
Thought we had lost you<vbg You've got it right about the quotes, and yes I am doing it to keep VB happy. The reason is that if the workbook name has embedded spaces, it will error unless you enclose it in single quotes. So myWorkboo.xls!GetTIR is okay, but my Workbook.xls!GetTir is not and is fixed by 'my Workbook.xls'getTIR So you can see that it is, in my favourite phrase, defensive programming, in case of embedded spaces. The double quotes are just to enclose the single quotes in building the string. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Ed M" wrote in message ... Bob - sorry for the lat response. I had to bug out of work early and didn't catch your message until now at home. I'll try to work through that first thing Monday morning. I left out (oops!) that I had tried substituting the string that held the name of the workbook to open, but I'm not sure if I've got the syntax correct, or if I'm using the wrong string (one with the file path vs. just the file name). Another Monday morning action item. But let me also clarify what you gave me: .OnAction = "'" & objWkbk.Name & "'!GetTIR" that's double-quote single-quote double quote & name & double-quote single-quote !macro double-quote ? Are there any rules to the quotes? Or do you just keep throwing them in until VB is happy?!? 8\ Thanks for keeping with me on this. Ed "Bob Phillips" wrote in message ... Not tried this Ed, but if we just work it through. objWkbk is the object variable in your VB app, which is totally meaningless to the commandbar and its OnAction property. Running a macro in another workbook is in essence of the form Application.Run "myBook.xls'!myMacro" so we need to build this up. All we have is an object and an implicit macro name. We need the workbook name, which we can get from the object. So something like .OnAction = "'" & objWkbk.Name & "'!GetTIR" -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Ed" wrote in message ... Found the error in this: I set OnAction in the Visual Basic app as .OnAction = "objWkbk.Sheet1!GetTIR" where "objWkbk" is the Excel file opened by the VB app. But when I run the macro in the workbook, I get the error "Can't find "objWkbk.Sheet1.xls". Of course not! objWkbk is the *object* reference, not the *filename string*! D'oh! So I changed it. Now I get an error '400', whatever that means. The macro is there, and it does run when called. I just can't get this menu item to call it. Ed |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting OnAction of custom menu item?
Got it, Bob! Thanks for all your help. I renamed the file so it has no
spaces in it, but that wasn't what did it. Apparently, all OnAction wanted was "Sheet1.GetTIR" - it didn't want the file, probably because that's the file it was looking in anyway. Whew! Glad that's over. And glad to have people who'll help like this. Ed "Bob Phillips" wrote in message ... Hi Ed, Thought we had lost you<vbg You've got it right about the quotes, and yes I am doing it to keep VB happy. The reason is that if the workbook name has embedded spaces, it will error unless you enclose it in single quotes. So myWorkboo.xls!GetTIR is okay, but my Workbook.xls!GetTir is not and is fixed by 'my Workbook.xls'getTIR So you can see that it is, in my favourite phrase, defensive programming, in case of embedded spaces. The double quotes are just to enclose the single quotes in building the string. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Ed M" wrote in message ... Bob - sorry for the lat response. I had to bug out of work early and didn't catch your message until now at home. I'll try to work through that first thing Monday morning. I left out (oops!) that I had tried substituting the string that held the name of the workbook to open, but I'm not sure if I've got the syntax correct, or if I'm using the wrong string (one with the file path vs. just the file name). Another Monday morning action item. But let me also clarify what you gave me: .OnAction = "'" & objWkbk.Name & "'!GetTIR" that's double-quote single-quote double quote & name & double-quote single-quote !macro double-quote ? Are there any rules to the quotes? Or do you just keep throwing them in until VB is happy?!? 8\ Thanks for keeping with me on this. Ed "Bob Phillips" wrote in message ... Not tried this Ed, but if we just work it through. objWkbk is the object variable in your VB app, which is totally meaningless to the commandbar and its OnAction property. Running a macro in another workbook is in essence of the form Application.Run "myBook.xls'!myMacro" so we need to build this up. All we have is an object and an implicit macro name. We need the workbook name, which we can get from the object. So something like .OnAction = "'" & objWkbk.Name & "'!GetTIR" -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Ed" wrote in message ... Found the error in this: I set OnAction in the Visual Basic app as .OnAction = "objWkbk.Sheet1!GetTIR" where "objWkbk" is the Excel file opened by the VB app. But when I run the macro in the workbook, I get the error "Can't find "objWkbk.Sheet1.xls". Of course not! objWkbk is the *object* reference, not the *filename string*! D'oh! So I changed it. Now I get an error '400', whatever that means. The macro is there, and it does run when called. I just can't get this menu item to call it. Ed |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
OnAction of Menu Bar with variable parameters | Excel Discussion (Misc queries) | |||
Excel 2000 Custom Menu Item | Excel Discussion (Misc queries) | |||
Propblem setting OnAction property at runtime | Excel Programming | |||
Custom Menu Item specific to a workbook | Excel Programming | |||
Argument with onAction in a menu. | Excel Programming |