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

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

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

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

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

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

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