Hello,

I created a custom menu with a vba macro in an xla file (excel 2003) but i
have a problem : When the code runs, it looks for the macro "mymacro"
defined in ".OnAction="mymacro" not in the xla file itself but in the active
xls workbook.

I do not understand why

Thanks for your help

Try this Thomas

..OnAction = ThisWorkbook.Name & "!mymacro"

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"thomas" <nomail> wrote in message news:(E-Mail Removed)...
> Hello,
>
> I created a custom menu with a vba macro in an xla file (excel 2003) but i
> have a problem : When the code runs, it looks for the macro "mymacro"
> defined in ".OnAction="mymacro" not in the xla file itself but in the active
> xls workbook.
>
> I do not understand why
>
> Thanks for your help
>
>

Thanks a lot. i will try it

But i thought a macro runned always within the same workbook, that's not
what should be?


"Ron de Bruin" <(E-Mail Removed)> a écrit dans le message de groupe
de discussion : uLauKB#(E-Mail Removed)...
Try this Thomas

..OnAction = ThisWorkbook.Name & "!mymacro"

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"thomas" <nomail> wrote in message
news:(E-Mail Removed)...
> Hello,
>
> I created a custom menu with a vba macro in an xla file (excel 2003) but
> i
> have a problem : When the code runs, it looks for the macro "mymacro"
> defined in ".OnAction="mymacro" not in the xla file itself but in the
> active
> xls workbook.
>
> I do not understand why
>
> Thanks for your help
>
>

Unfortunately i tried it today at the office and it does not work

mymacro is in the xla file and the menu command makes it works

if i open an xls workbook containing a copy of mymacro and then the menu
command makes that copy work. If i deleted that copy, a message says it
does not find 'mymacro'


"Ron de Bruin" <(E-Mail Removed)> a écrit dans le message de groupe
de discussion : uLauKB#(E-Mail Removed)...
Try this Thomas

..OnAction = ThisWorkbook.Name & "!mymacro"

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"thomas" <nomail> wrote in message
news:(E-Mail Removed)...
> Hello,
>
> I created a custom menu with a vba macro in an xla file (excel 2003) but
> i
> have a problem : When the code runs, it looks for the macro "mymacro"
> defined in ".OnAction="mymacro" not in the xla file itself but in the
> active
> xls workbook.
>
> I do not understand why
>
> Thanks for your help
>
>

See Debra Dalgleish's site for code to create an add-in with code to add a
Toolbar.

http://www.contextures.on.ca/xlToolbar02.html

Note the .OnAction line using Thisworkbook as Ron pointed out.


Gord Dibben MS Excel MVP

On Tue, 7 Oct 2008 20:07:49 +0200, "thomas" <nomail> wrote:

>Unfortunately i tried it today at the office and it does not work
>
>mymacro is in the xla file and the menu command makes it works
>
>if i open an xls workbook containing a copy of mymacro and then the menu
>command makes that copy work. If i deleted that copy, a message says it
>does not find 'mymacro'
>
>
>"Ron de Bruin" <(E-Mail Removed)> a écrit dans le message de groupe
>de discussion : uLauKB#(E-Mail Removed)...
>Try this Thomas
>
>.OnAction = ThisWorkbook.Name & "!mymacro"

Thanks, i will try tomorrow at office

The difference i see with Ron's code is the " ' "

..OnAction = "'" & ThisWorkbook.Name & "'!" & MacNames(iCtr)



"Gord Dibben" <gorddibbATshawDOTca> a écrit dans le message de groupe de
discussion : (E-Mail Removed)...
See Debra Dalgleish's site for code to create an add-in with code to add a
Toolbar.

http://www.contextures.on.ca/xlToolbar02.html

Note the .OnAction line using Thisworkbook as Ron pointed out.


Gord Dibben MS Excel MVP

On Tue, 7 Oct 2008 20:07:49 +0200, "thomas" <nomail> wrote:

>Unfortunately i tried it today at the office and it does not work
>
>mymacro is in the xla file and the menu command makes it works
>
>if i open an xls workbook containing a copy of mymacro and then the menu
>command makes that copy work. If i deleted that copy, a message says it
>does not find 'mymacro'
>
>
>"Ron de Bruin" <(E-Mail Removed)> a écrit dans le message de groupe
>de discussion : uLauKB#(E-Mail Removed)...
>Try this Thomas
>
>.OnAction = ThisWorkbook.Name & "!mymacro"

I tried

..OnAction = "'" & ThisWorkbook.Name & "'!mymacro"

and it's the same :

It's mymacro in the active workbook that runs and not the one in the xla
file project


"Gord Dibben" <gorddibbATshawDOTca> a écrit dans le message de groupe de
discussion : (E-Mail Removed)...
See Debra Dalgleish's site for code to create an add-in with code to add a
Toolbar.

http://www.contextures.on.ca/xlToolbar02.html

Note the .OnAction line using Thisworkbook as Ron pointed out.


Gord Dibben MS Excel MVP

On Tue, 7 Oct 2008 20:07:49 +0200, "thomas" <nomail> wrote:

>Unfortunately i tried it today at the office and it does not work
>
>mymacro is in the xla file and the menu command makes it works
>
>if i open an xls workbook containing a copy of mymacro and then the menu
>command makes that copy work. If i deleted that copy, a message says it
>does not find 'mymacro'
>
>
>"Ron de Bruin" <(E-Mail Removed)> a écrit dans le message de groupe
>de discussion : uLauKB#(E-Mail Removed)...
>Try this Thomas
>
>.OnAction = ThisWorkbook.Name & "!mymacro"

I think you have confused Excel by having "mymacro" in two separate
workbooks.

Delete the "mymacro" from the workbook then save.

Close Excel then reopen with your saved workbook.

Where does the toolbar button point to?


Gord

On Tue, 7 Oct 2008 23:22:47 +0200, "thomas" <nomail> wrote:

>I tried
>
>.OnAction = "'" & ThisWorkbook.Name & "'!mymacro"
>
>and it's the same :
>
>It's mymacro in the active workbook that runs and not the one in the xla
>file project
>
>
>"Gord Dibben" <gorddibbATshawDOTca> a écrit dans le message de groupe de
>discussion : (E-Mail Removed)...
>See Debra Dalgleish's site for code to create an add-in with code to add a
>Toolbar.
>
>http://www.contextures.on.ca/xlToolbar02.html
>
>Note the .OnAction line using Thisworkbook as Ron pointed out.
>
>
>Gord Dibben MS Excel MVP
>
>On Tue, 7 Oct 2008 20:07:49 +0200, "thomas" <nomail> wrote:
>
>>Unfortunately i tried it today at the office and it does not work
>>
>>mymacro is in the xla file and the menu command makes it works
>>
>>if i open an xls workbook containing a copy of mymacro and then the menu
>>command makes that copy work. If i deleted that copy, a message says it
>>does not find 'mymacro'
>>
>>
>>"Ron de Bruin" <(E-Mail Removed)> a écrit dans le message de groupe
>>de discussion : uLauKB#(E-Mail Removed)...
>>Try this Thomas
>>
>>.OnAction = ThisWorkbook.Name & "!mymacro"

Yes originally the macro was duplicated in several xls workbooks.
I wanted to rationalize by using an xla file instead

i kept "mymacro" (the name for the example) in the xls workbooks but
unactivated their menus.

The point is that if i delete the entire projects in theses xls worbooks,
the xla menu still points to them if they are the active workbooks, even
with

.OnAction = "'" & ThisWorkbook.Name & "'!mymacro"

(i thought ThisWorkbook.Name referred to the xla in which the menus are)

I did something else : i renamed mymacro in the xla workbooks and now it
works, as if even with deleted projects in xls workbooks, there was still
something left with the older code.

What is strange too is that other macros in theses xls workbooks also had
the same names as in the xla workbook and no problem with them

Hope my english is clear. I'm french ;-)

Thanks

"Gord Dibben" <gorddibbATshawDOTca> a écrit dans le message de groupe de
discussion : (E-Mail Removed)...
I think you have confused Excel by having "mymacro" in two separate
workbooks.

Delete the "mymacro" from the workbook then save.

Close Excel then reopen with your saved workbook.

Where does the toolbar button point to?


Gord

On Tue, 7 Oct 2008 23:22:47 +0200, "thomas" <nomail> wrote:

>I tried
>
>.OnAction = "'" & ThisWorkbook.Name & "'!mymacro"
>
>and it's the same :
>
>It's mymacro in the active workbook that runs and not the one in the xla
>file project
>
>
>"Gord Dibben" <gorddibbATshawDOTca> a écrit dans le message de groupe de
>discussion : (E-Mail Removed)...
>See Debra Dalgleish's site for code to create an add-in with code to add a
>Toolbar.
>
>http://www.contextures.on.ca/xlToolbar02.html
>
>Note the .OnAction line using Thisworkbook as Ron pointed out.
>
>
>Gord Dibben MS Excel MVP
>
>On Tue, 7 Oct 2008 20:07:49 +0200, "thomas" <nomail> wrote:
>
>>Unfortunately i tried it today at the office and it does not work
>>
>>mymacro is in the xla file and the menu command makes it works
>>
>>if i open an xls workbook containing a copy of mymacro and then the menu
>>command makes that copy work. If i deleted that copy, a message says it
>>does not find 'mymacro'
>>
>>
>>"Ron de Bruin" <(E-Mail Removed)> a écrit dans le message de groupe
>>de discussion : uLauKB#(E-Mail Removed)...
>>Try this Thomas
>>
>>.OnAction = ThisWorkbook.Name & "!mymacro"