I have some code on a worksheet in an .XLS which uses a .XLA with some more
VBA code in it. Is it possible for a macro in the .XLA to run another macro
in the .XLS sheet?

What would the syntax be something like? (which does not work of course)

workbooks("Main.XLS").sheet1.mymacro
--
Trefor

Hi Trefor,

Try:

Application.Run "'Main.xls'!Sheet1.MyMacro"


---
Regards,
Norman


"Trefor" wrote in message
...
I have some code on a worksheet in an .XLS which uses a .XLA with some more
VBA code in it. Is it possible for a macro in the .XLA to run another
macro
in the .XLS sheet?

What would the syntax be something like? (which does not work of course)

workbooks("Main.XLS").sheet1.mymacro
--
Trefor

Norman,

Many thanks for the prompt reply. I have used this to run a macro from an
XLS to a macro in an XLA, but not the other way round, but normally in a
Module not a worksheet.

When I run this as you describe I get an error 1004 saying the macro can not
be found.

--
Trefor


"Norman Jones" wrote:

Hi Trefor,

Try:

Application.Run "'Main.xls'!Sheet1.MyMacro"


---
Regards,
Norman


"Trefor" wrote in message
...
I have some code on a worksheet in an .XLS which uses a .XLA with some more
VBA code in it. Is it possible for a macro in the .XLA to run another
macro
in the .XLS sheet?

What would the syntax be something like? (which does not work of course)

workbooks("Main.XLS").sheet1.mymacro
--
Trefor

Hi Trefor,

Many thanks for the prompt reply. I have used this to run a macro from
an XLS to a macro in an XLA, but not the other way round, but
normally in a Module not a worksheet.

When I run this as you describe I get an error 1004 saying the macro
can not be found.

The suggested syntax works for me. Check that the workbook name is correct
and is not missing any spaces / does not include any extraneous spaces.

Note also that the suggested syntax wraps the workbook name in single
quotes. This is to allow for possible spaces in the name.

If the problem persists, paste the relevant code line in your response and
confiirm where the code is housed.


---
Regards,
Norman

Norman,

WorkbookMain = ActiveWorkbook.Name

This is in a module of the .XLA. Set_CCRF_Names is the macro name on Sheet13

Application.Run WorkbookMain & "!Sheet13.Set_CCRF_Names"


--
Trefor


"Norman Jones" wrote:

Hi Trefor,

Many thanks for the prompt reply. I have used this to run a macro from
an XLS to a macro in an XLA, but not the other way round, but
normally in a Module not a worksheet.

When I run this as you describe I get an error 1004 saying the macro
can not be found.

The suggested syntax works for me. Check that the workbook name is correct
and is not missing any spaces / does not include any extraneous spaces.

Note also that the suggested syntax wraps the workbook name in single
quotes. This is to allow for possible spaces in the name.

If the problem persists, paste the relevant code line in your response and
confiirm where the code is housed.


---
Regards,
Norman

Hi Trefor,

Perhaps the activeworkbook name includes spaces.

Try:

Dim WorkbookMain As String

WorkbookMain = "'" & ActiveWorkbook.Name & "'"

Application.Run WorkbookMain & "!Sheet13.Set_CCRF_Names"


---
Regards,
Norman


"Trefor" wrote in message
...
Norman,

WorkbookMain = ActiveWorkbook.Name

This is in a module of the .XLA. Set_CCRF_Names is the macro name on
Sheet13

Application.Run WorkbookMain & "!Sheet13.Set_CCRF_Names"


--
Trefor

Trefor,

Whilst I am sure that Norman's suggested way will work, this does not seem
like good design to me.

If you call a macro in an add-in from another worksheet, that is okay as the
add-in will always be loaded, and you can easily test for it. However, other
way around, the workbook may not be open, or worse, you may have many
workbooks with that macro (I am assuming that they might be template based).

Why do you need to have that macro in the standard workbook?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Trefor" wrote in message
...
I have some code on a worksheet in an .XLS which uses a .XLA with some
more
VBA code in it. Is it possible for a macro in the .XLA to run another
macro
in the .XLS sheet?

What would the syntax be something like? (which does not work of course)

workbooks("Main.XLS").sheet1.mymacro
--
Trefor

Bob,

Many thanks for the reply.

Your comments are very valid and understood. In my case the XLS containing
the end macro to be run will also be the same XLS that calls the macro in the
XLA. It is possible in my case to have multiple XLS's open and I keep track
of this by setting the variable WorkbookMain = ActiveWorkbook.Name. So it is
not possible for the XLA to call the Macro in the XLS, without the XLS
starting the whole process in the first place.

So why am I doing this in the first place. The XLS contains a number of
sheets, on request the user can click on various button's and these buttons
will cause in one case a sub set of these sheets to be "exported" or copied
into another workbook. I need some macro's in the new workbook, BUT both the
original XLS and the XLA are protected, so I can't simply copy a Module from
one to another. I had tried exporting the module to a .BAS and then using:

ActiveWorkBook.VBProject.VBComponents.Import (MainPath + "\CCRF.bas") to
copy the macro's into the new XLS.

But this has several disadvantages 1. It mean having an unprotected .BAS
which could then be read and/or modied. 2. It means having an external/extra
file, but worst of all 3. it means I need to check "Trust access to Visual
Basic Project", which has to be done manually by everyone that uses the XLS
for the first time.

To avoid this I thought I would copy the macro into a sheet, that way no
unprotected separate file, no need to make any special changes in Excel and
in theory I can access from the Main XLS AND from the New XLS.

Currently my work around, is to have this macro in both the sheet and the
main module of the XLA. The only catch (other than the extra code) is I have
to maintain two subs not one.

--
Trefor


"Bob Phillips" wrote:

Trefor,

Whilst I am sure that Norman's suggested way will work, this does not seem
like good design to me.

If you call a macro in an add-in from another worksheet, that is okay as the
add-in will always be loaded, and you can easily test for it. However, other
way around, the workbook may not be open, or worse, you may have many
workbooks with that macro (I am assuming that they might be template based).

Why do you need to have that macro in the standard workbook?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Trefor" wrote in message
...
I have some code on a worksheet in an .XLS which uses a .XLA with some
more
VBA code in it. Is it possible for a macro in the .XLA to run another
macro
in the .XLS sheet?

What would the syntax be something like? (which does not work of course)

workbooks("Main.XLS").sheet1.mymacro
--
Trefor

Hi Bob,

Whilst I am sure that Norman's suggested way will work, this does
not seem like good design to me.

I completely endorse your design concerns.

Somewhat lazily, I responded to Trefor's:

What would the syntax be something like? (which does not work
of course)

workbooks("Main.XLS").sheet1.mymacro


---
Regards,
Norman