I have built a workbook that call subroutines in an add-in (.xla). If the
workbook is run on another PC for the first time I get the above compile
error, because the add-in is missing and my workbook code crashes. Is it
possible to check in a macro that the add-in is present and exit gracefully
(perhaps displaying a message saying the Add-in is missing)?

--
Trefor

If you've set a reference in your workbook to the add-in there is no way to
trap an error if it is not present, as far as I know. But if you're running
add-in code in some other way (like using RUN) you could check whether the
add-in is open like this first:

Function AddinPresent() As Boolean
Dim WBName As String
On Error GoTo NotPresent
WBName = Workbooks("YourAddin.xla").Name
AddinPresent = True
NotPresent:
End Function

--
Jim
"Trefor" wrote in message
...
|I have built a workbook that call subroutines in an add-in (.xla). If the
| workbook is run on another PC for the first time I get the above compile
| error, because the add-in is missing and my workbook code crashes. Is it
| possible to check in a macro that the add-in is present and exit
gracefully
| (perhaps displaying a message saying the Add-in is missing)?
|
| --
| Trefor

Jim,

Thankyou for the reply. I have set a reference to a .xla, I have not used
the RUN method before, if I did what are the disadvantages if I changed
everytrhing to use RUN? (I presume you are talking about a .xlm?) I have no
idea how to even create one of these.

Life wasn't meant to be easy was it?

Trefor
--
Trefor


"Jim Rech" wrote:

If you've set a reference in your workbook to the add-in there is no way to
trap an error if it is not present, as far as I know. But if you're running
add-in code in some other way (like using RUN) you could check whether the
add-in is open like this first:

Function AddinPresent() As Boolean
Dim WBName As String
On Error GoTo NotPresent
WBName = Workbooks("YourAddin.xla").Name
AddinPresent = True
NotPresent:
End Function

--
Jim
"Trefor" wrote in message
...
|I have built a workbook that call subroutines in an add-in (.xla). If the
| workbook is run on another PC for the first time I get the above compile
| error, because the add-in is missing and my workbook code crashes. Is it
| possible to check in a macro that the add-in is present and exit
gracefully
| (perhaps displaying a message saying the Add-in is missing)?
|
| --
| Trefor

I don't know of any disadvantage except that it's a little more cumbersome
to call a sub via Run than directly. In theory Run is a little slower but I
don't see a difference as a practical matter.

I don't know what you mean by "xlm". All I'm saying is replace a direct
call to the add-in's sub:

SubInXLA

with

Run "MyAddin.xla!SubInXLA"

Of course this requires that the add-in is open in Excel.

It will take you only a few seconds to switch one or two call and test how
it works for you. check out the Run method for moe on it.

--
Jim
"Trefor" wrote in message
...
| Jim,
|
| Thankyou for the reply. I have set a reference to a .xla, I have not used
| the RUN method before, if I did what are the disadvantages if I changed
| everytrhing to use RUN? (I presume you are talking about a .xlm?) I have
no
| idea how to even create one of these.
|
| Life wasn't meant to be easy was it?
|
| Trefor
| --
| Trefor
|
|
| "Jim Rech" wrote:
|
| If you've set a reference in your workbook to the add-in there is no way
to
| trap an error if it is not present, as far as I know. But if you're
running
| add-in code in some other way (like using RUN) you could check whether
the
| add-in is open like this first:
|
| Function AddinPresent() As Boolean
| Dim WBName As String
| On Error GoTo NotPresent
| WBName = Workbooks("YourAddin.xla").Name
| AddinPresent = True
| NotPresent:
| End Function
|
| --
| Jim
| "Trefor" wrote in message
| ...
| |I have built a workbook that call subroutines in an add-in (.xla). If
the
| | workbook is run on another PC for the first time I get the above
compile
| | error, because the add-in is missing and my workbook code crashes. Is
it
| | possible to check in a macro that the add-in is present and exit
| gracefully
| | (perhaps displaying a message saying the Add-in is missing)?
| |
| | --
| | Trefor
|
|
|

Jim,

Thank you again for your reply. But I am still having problems:

This is the main workbook:

Option Explicit
Option Private Module
Public Const DCMaster = "Customer Data Collect Master v6.37.xla"

Sub Auto_Open()
If AddinPresent Then
Application.Run DCMaster & "!test" <----- This says it can't find
the macro
Else
MsgBox "Addin Not Present"
End If
End Sub

Function AddinPresent() As Boolean <----- This works great thankyou
Dim WBName As String
On Error GoTo NotPresent
WBName = Workbooks(DCMaster).Name
AddinPresent = True
NotPresent:
End Function

This is the macro in the .xla:

Public Sub test()
MsgBox "test"
End Sub

Trefor
--
Trefor


"Jim Rech" wrote:

I don't know of any disadvantage except that it's a little more cumbersome
to call a sub via Run than directly. In theory Run is a little slower but I
don't see a difference as a practical matter.

I don't know what you mean by "xlm". All I'm saying is replace a direct
call to the add-in's sub:

SubInXLA

with

Run "MyAddin.xla!SubInXLA"

Of course this requires that the add-in is open in Excel.

It will take you only a few seconds to switch one or two call and test how
it works for you. check out the Run method for moe on it.

--
Jim
"Trefor" wrote in message
...
| Jim,
|
| Thankyou for the reply. I have set a reference to a .xla, I have not used
| the RUN method before, if I did what are the disadvantages if I changed
| everytrhing to use RUN? (I presume you are talking about a .xlm?) I have
no
| idea how to even create one of these.
|
| Life wasn't meant to be easy was it?
|
| Trefor
| --
| Trefor
|
|
| "Jim Rech" wrote:
|
| If you've set a reference in your workbook to the add-in there is no way
to
| trap an error if it is not present, as far as I know. But if you're
running
| add-in code in some other way (like using RUN) you could check whether
the
| add-in is open like this first:
|
| Function AddinPresent() As Boolean
| Dim WBName As String
| On Error GoTo NotPresent
| WBName = Workbooks("YourAddin.xla").Name
| AddinPresent = True
| NotPresent:
| End Function
|
| --
| Jim
| "Trefor" wrote in message
| ...
| |I have built a workbook that call subroutines in an add-in (.xla). If
the
| | workbook is run on another PC for the first time I get the above
compile
| | error, because the add-in is missing and my workbook code crashes. Is
it
| | possible to check in a macro that the add-in is present and exit
| gracefully
| | (perhaps displaying a message saying the Add-in is missing)?
| |
| | --
| | Trefor
|
|
|

Hi Tefor,

Application.Run DCMaster & "!test" <----- This says it can't find

Look at the syntax suggested by Jim and look at the Run method in VBA help.

Try:

Application.Run "DCMaster.xla!test"

---
Regards,
Norman



"Trefor" wrote in message
...
Jim,

Thank you again for your reply. But I am still having problems:

This is the main workbook:

Option Explicit
Option Private Module
Public Const DCMaster = "Customer Data Collect Master v6.37.xla"

Sub Auto_Open()
If AddinPresent Then
Application.Run DCMaster & "!test" <----- This says it can't find
the macro
Else
MsgBox "Addin Not Present"
End If
End Sub

Function AddinPresent() As Boolean <----- This works great thankyou
Dim WBName As String
On Error GoTo NotPresent
WBName = Workbooks(DCMaster).Name
AddinPresent = True
NotPresent:
End Function

This is the macro in the .xla:

Public Sub test()
MsgBox "test"
End Sub

Trefor
--
Trefor


"Jim Rech" wrote:

I don't know of any disadvantage except that it's a little more
cumbersome
to call a sub via Run than directly. In theory Run is a little slower
but I
don't see a difference as a practical matter.

I don't know what you mean by "xlm". All I'm saying is replace a direct
call to the add-in's sub:

SubInXLA

with

Run "MyAddin.xla!SubInXLA"

Of course this requires that the add-in is open in Excel.

It will take you only a few seconds to switch one or two call and test
how
it works for you. check out the Run method for moe on it.

--
Jim

Hi Tefor,

Please ignore my previous post; I did not see your public constant.

Try instead changing:

Public Const DCMaster = "Customer Data Collect Master v6.37.xla"
to:
Public Const DCMaster = "'Customer Data Collect Master v6.37.xla'"


The additional single quotes are required because of the spaces in the file
name.

---
Regards,
Norman



"Norman Jones" wrote in message
...
Hi Tefor,

Application.Run DCMaster & "!test" <----- This says it can't find

Look at the syntax suggested by Jim and look at the Run method in VBA
help.

Try:

Application.Run "DCMaster.xla!test"

---
Regards,
Norman



"Trefor" wrote in message
...
Jim,

Thank you again for your reply. But I am still having problems:

This is the main workbook:

Option Explicit
Option Private Module
Public Const DCMaster = "Customer Data Collect Master v6.37.xla"

Sub Auto_Open()
If AddinPresent Then
Application.Run DCMaster & "!test" <----- This says it can't find
the macro
Else
MsgBox "Addin Not Present"
End If
End Sub

Function AddinPresent() As Boolean <----- This works great thankyou
Dim WBName As String
On Error GoTo NotPresent
WBName = Workbooks(DCMaster).Name
AddinPresent = True
NotPresent:
End Function

This is the macro in the .xla:

Public Sub test()
MsgBox "test"
End Sub

Trefor
--
Trefor


"Jim Rech" wrote:

I don't know of any disadvantage except that it's a little more
cumbersome
to call a sub via Run than directly. In theory Run is a little slower
but I
don't see a difference as a practical matter.

I don't know what you mean by "xlm". All I'm saying is replace a direct
call to the add-in's sub:

SubInXLA

with

Run "MyAddin.xla!SubInXLA"

Of course this requires that the add-in is open in Excel.

It will take you only a few seconds to switch one or two call and test
how
it works for you. check out the Run method for moe on it.

--
Jim

Norman/Jim,

Yes I worked that bit out, found it another thread, and you were spot on. I
was just coming back to let Jim know and saw your reply.

Ok, my next problem in converting my files to use RUN, is calling a macro
from a command button. Originally the button said:

'Customer Data Collect v6.37 test.xls'!Menu_New_Site

I tried changing it to the .xla as per the RUN:

'Customer Data Collect Master v6.37.xla'!Menu_New_Site

In both cases it says it can't find the macro, any ideas?

--
Trefor


"Norman Jones" wrote:

Hi Tefor,

Please ignore my previous post; I did not see your public constant.

Try instead changing:

Public Const DCMaster = "Customer Data Collect Master v6.37.xla"
to:
Public Const DCMaster = "'Customer Data Collect Master v6.37.xla'"


The additional single quotes are required because of the spaces in the file
name.

---
Regards,
Norman



"Norman Jones" wrote in message
...
Hi Tefor,

Application.Run DCMaster & "!test" <----- This says it can't find

Look at the syntax suggested by Jim and look at the Run method in VBA
help.

Try:

Application.Run "DCMaster.xla!test"

---
Regards,
Norman



"Trefor" wrote in message
...
Jim,

Thank you again for your reply. But I am still having problems:

This is the main workbook:

Option Explicit
Option Private Module
Public Const DCMaster = "Customer Data Collect Master v6.37.xla"

Sub Auto_Open()
If AddinPresent Then
Application.Run DCMaster & "!test" <----- This says it can't find
the macro
Else
MsgBox "Addin Not Present"
End If
End Sub

Function AddinPresent() As Boolean <----- This works great thankyou
Dim WBName As String
On Error GoTo NotPresent
WBName = Workbooks(DCMaster).Name
AddinPresent = True
NotPresent:
End Function

This is the macro in the .xla:

Public Sub test()
MsgBox "test"
End Sub

Trefor
--
Trefor


"Jim Rech" wrote:

I don't know of any disadvantage except that it's a little more
cumbersome
to call a sub via Run than directly. In theory Run is a little slower
but I
don't see a difference as a practical matter.

I don't know what you mean by "xlm". All I'm saying is replace a direct
call to the add-in's sub:

SubInXLA

with

Run "MyAddin.xla!SubInXLA"

Of course this requires that the add-in is open in Excel.

It will take you only a few seconds to switch one or two call and test
how
it works for you. check out the Run method for moe on it.

--
Jim

Hi Trefor,

With the XLA file open, try:

Menu_New_Site

---
Regards,
Norman



"Trefor" wrote in message
...
Norman/Jim,

Yes I worked that bit out, found it another thread, and you were spot on.
I
was just coming back to let Jim know and saw your reply.

Ok, my next problem in converting my files to use RUN, is calling a macro
from a command button. Originally the button said:

'Customer Data Collect v6.37 test.xls'!Menu_New_Site

I tried changing it to the .xla as per the RUN:

'Customer Data Collect Master v6.37.xla'!Menu_New_Site

In both cases it says it can't find the macro, any ideas?

--
Trefor


"Norman Jones" wrote:

Hi Tefor,

Please ignore my previous post; I did not see your public constant.

Try instead changing:

Public Const DCMaster = "Customer Data Collect Master v6.37.xla"
to:
Public Const DCMaster = "'Customer Data Collect Master v6.37.xla'"


The additional single quotes are required because of the spaces in the
file
name.

---
Regards,
Norman



"Norman Jones" wrote in message
...
Hi Tefor,

Application.Run DCMaster & "!test" <----- This says it can't
find

Look at the syntax suggested by Jim and look at the Run method in VBA
help.

Try:

Application.Run "DCMaster.xla!test"

---
Regards,
Norman



"Trefor" wrote in message
...
Jim,

Thank you again for your reply. But I am still having problems:

This is the main workbook:

Option Explicit
Option Private Module
Public Const DCMaster = "Customer Data Collect Master v6.37.xla"

Sub Auto_Open()
If AddinPresent Then
Application.Run DCMaster & "!test" <----- This says it can't
find
the macro
Else
MsgBox "Addin Not Present"
End If
End Sub

Function AddinPresent() As Boolean <----- This works great thankyou
Dim WBName As String
On Error GoTo NotPresent
WBName = Workbooks(DCMaster).Name
AddinPresent = True
NotPresent:
End Function

This is the macro in the .xla:

Public Sub test()
MsgBox "test"
End Sub

Trefor
--
Trefor


"Jim Rech" wrote:

I don't know of any disadvantage except that it's a little more
cumbersome
to call a sub via Run than directly. In theory Run is a little
slower
but I
don't see a difference as a practical matter.

I don't know what you mean by "xlm". All I'm saying is replace a
direct
call to the add-in's sub:

SubInXLA

with

Run "MyAddin.xla!SubInXLA"

Of course this requires that the add-in is open in Excel.

It will take you only a few seconds to switch one or two call and
test
how
it works for you. check out the Run method for moe on it.

--
Jim

Norman,

Interestingly after my last edit (to 'Customer Data Collect Master
v6.37.xla'!Menu_New_Site), it reverted to your suggestion anyway. And I get a
slighty different error message but essentially the same problem.

--
Trefor


"Norman Jones" wrote:

Hi Trefor,

With the XLA file open, try:

Menu_New_Site

---
Regards,
Norman



"Trefor" wrote in message
...
Norman/Jim,

Yes I worked that bit out, found it another thread, and you were spot on.
I
was just coming back to let Jim know and saw your reply.

Ok, my next problem in converting my files to use RUN, is calling a macro
from a command button. Originally the button said:

'Customer Data Collect v6.37 test.xls'!Menu_New_Site

I tried changing it to the .xla as per the RUN:

'Customer Data Collect Master v6.37.xla'!Menu_New_Site

In both cases it says it can't find the macro, any ideas?

--
Trefor


"Norman Jones" wrote:

Hi Tefor,

Please ignore my previous post; I did not see your public constant.

Try instead changing:

Public Const DCMaster = "Customer Data Collect Master v6.37.xla"
to:
Public Const DCMaster = "'Customer Data Collect Master v6.37.xla'"


The additional single quotes are required because of the spaces in the
file
name.

---
Regards,
Norman



"Norman Jones" wrote in message
...
Hi Tefor,

Application.Run DCMaster & "!test" <----- This says it can't
find

Look at the syntax suggested by Jim and look at the Run method in VBA
help.

Try:

Application.Run "DCMaster.xla!test"

---
Regards,
Norman



"Trefor" wrote in message
...
Jim,

Thank you again for your reply. But I am still having problems:

This is the main workbook:

Option Explicit
Option Private Module
Public Const DCMaster = "Customer Data Collect Master v6.37.xla"

Sub Auto_Open()
If AddinPresent Then
Application.Run DCMaster & "!test" <----- This says it can't
find
the macro
Else
MsgBox "Addin Not Present"
End If
End Sub

Function AddinPresent() As Boolean <----- This works great thankyou
Dim WBName As String
On Error GoTo NotPresent
WBName = Workbooks(DCMaster).Name
AddinPresent = True
NotPresent:
End Function

This is the macro in the .xla:

Public Sub test()
MsgBox "test"
End Sub

Trefor
--
Trefor


"Jim Rech" wrote:

I don't know of any disadvantage except that it's a little more
cumbersome
to call a sub via Run than directly. In theory Run is a little
slower
but I
don't see a difference as a practical matter.

I don't know what you mean by "xlm". All I'm saying is replace a
direct
call to the add-in's sub:

SubInXLA

with

Run "MyAddin.xla!SubInXLA"

Of course this requires that the add-in is open in Excel.

It will take you only a few seconds to switch one or two call and
test
how
it works for you. check out the Run method for moe on it.

--
Jim

Hi Trefor,

And I get a slighty different error message but essentially the
same problem.

What error message do you get?

I created an addin and named it:
Customer Data Collect Master v6.37.xla'

I added a macro and named it: Menu_New_Site; I assigned the macro to the
button using Menu_New_Site as the macro reference. Clicking the button the
macro (which comprised a banal message) ran as expected.

---
Regards,
Norman



"Trefor" wrote in message
...
Norman,

Interestingly after my last edit (to 'Customer Data Collect Master
v6.37.xla'!Menu_New_Site), it reverted to your suggestion anyway. And I
get a
slighty different error message but essentially the same problem.

--
Trefor


"Norman Jones" wrote:

Hi Trefor,

With the XLA file open, try:

Menu_New_Site

---
Regards,
Norman



"Trefor" wrote in message
...
Norman/Jim,

Yes I worked that bit out, found it another thread, and you were spot
on.
I
was just coming back to let Jim know and saw your reply.

Ok, my next problem in converting my files to use RUN, is calling a
macro
from a command button. Originally the button said:

'Customer Data Collect v6.37 test.xls'!Menu_New_Site

I tried changing it to the .xla as per the RUN:

'Customer Data Collect Master v6.37.xla'!Menu_New_Site

In both cases it says it can't find the macro, any ideas?

--
Trefor


"Norman Jones" wrote:

Hi Tefor,

Please ignore my previous post; I did not see your public constant.

Try instead changing:

Public Const DCMaster = "Customer Data Collect Master v6.37.xla"
to:
Public Const DCMaster = "'Customer Data Collect Master v6.37.xla'"


The additional single quotes are required because of the spaces in the
file
name.

---
Regards,
Norman



"Norman Jones" wrote in message
...
Hi Tefor,

Application.Run DCMaster & "!test" <----- This says it can't
find

Look at the syntax suggested by Jim and look at the Run method in
VBA
help.

Try:

Application.Run "DCMaster.xla!test"

---
Regards,
Norman



"Trefor" wrote in message
...
Jim,

Thank you again for your reply. But I am still having problems:

This is the main workbook:

Option Explicit
Option Private Module
Public Const DCMaster = "Customer Data Collect Master v6.37.xla"

Sub Auto_Open()
If AddinPresent Then
Application.Run DCMaster & "!test" <----- This says it can't
find
the macro
Else
MsgBox "Addin Not Present"
End If
End Sub

Function AddinPresent() As Boolean <----- This works great
thankyou
Dim WBName As String
On Error GoTo NotPresent
WBName = Workbooks(DCMaster).Name
AddinPresent = True
NotPresent:
End Function

This is the macro in the .xla:

Public Sub test()
MsgBox "test"
End Sub

Trefor
--
Trefor


"Jim Rech" wrote:

I don't know of any disadvantage except that it's a little more
cumbersome
to call a sub via Run than directly. In theory Run is a little
slower
but I
don't see a difference as a practical matter.

I don't know what you mean by "xlm". All I'm saying is replace a
direct
call to the add-in's sub:

SubInXLA

with

Run "MyAddin.xla!SubInXLA"

Of course this requires that the add-in is open in Excel.

It will take you only a few seconds to switch one or two call and
test
how
it works for you. check out the Run method for moe on it.

--
Jim

Error message is:

"The macro 'Menu_New_Site' cannot be found"

--
Trefor


"Norman Jones" wrote:

Hi Trefor,

And I get a slighty different error message but essentially the
same problem.

What error message do you get?

I created an addin and named it:
Customer Data Collect Master v6.37.xla'

I added a macro and named it: Menu_New_Site; I assigned the macro to the
button using Menu_New_Site as the macro reference. Clicking the button the
macro (which comprised a banal message) ran as expected.

---
Regards,
Norman



"Trefor" wrote in message
...
Norman,

Interestingly after my last edit (to 'Customer Data Collect Master
v6.37.xla'!Menu_New_Site), it reverted to your suggestion anyway. And I
get a
slighty different error message but essentially the same problem.

--
Trefor


"Norman Jones" wrote:

Hi Trefor,

With the XLA file open, try:

Menu_New_Site

---
Regards,
Norman



"Trefor" wrote in message
...
Norman/Jim,

Yes I worked that bit out, found it another thread, and you were spot
on.
I
was just coming back to let Jim know and saw your reply.

Ok, my next problem in converting my files to use RUN, is calling a
macro
from a command button. Originally the button said:

'Customer Data Collect v6.37 test.xls'!Menu_New_Site

I tried changing it to the .xla as per the RUN:

'Customer Data Collect Master v6.37.xla'!Menu_New_Site

In both cases it says it can't find the macro, any ideas?

--
Trefor


"Norman Jones" wrote:

Hi Tefor,

Please ignore my previous post; I did not see your public constant.

Try instead changing:

Public Const DCMaster = "Customer Data Collect Master v6.37.xla"
to:
Public Const DCMaster = "'Customer Data Collect Master v6.37.xla'"


The additional single quotes are required because of the spaces in the
file
name.

---
Regards,
Norman



"Norman Jones" wrote in message
...
Hi Tefor,

Application.Run DCMaster & "!test" <----- This says it can't
find

Look at the syntax suggested by Jim and look at the Run method in
VBA
help.

Try:

Application.Run "DCMaster.xla!test"

---
Regards,
Norman



"Trefor" wrote in message
...
Jim,

Thank you again for your reply. But I am still having problems:

This is the main workbook:

Option Explicit
Option Private Module
Public Const DCMaster = "Customer Data Collect Master v6.37.xla"

Sub Auto_Open()
If AddinPresent Then
Application.Run DCMaster & "!test" <----- This says it can't
find
the macro
Else
MsgBox "Addin Not Present"
End If
End Sub

Function AddinPresent() As Boolean <----- This works great
thankyou
Dim WBName As String
On Error GoTo NotPresent
WBName = Workbooks(DCMaster).Name
AddinPresent = True
NotPresent:
End Function

This is the macro in the .xla:

Public Sub test()
MsgBox "test"
End Sub

Trefor
--
Trefor


"Jim Rech" wrote:

I don't know of any disadvantage except that it's a little more
cumbersome
to call a sub via Run than directly. In theory Run is a little
slower
but I
don't see a difference as a practical matter.

I don't know what you mean by "xlm". All I'm saying is replace a
direct
call to the add-in's sub:

SubInXLA

with

Run "MyAddin.xla!SubInXLA"

Of course this requires that the add-in is open in Excel.

It will take you only a few seconds to switch one or two call and
test
how
it works for you. check out the Run method for moe on it.

--
Jim

Hi Trefor.

Is the addin loaded?

Are you sure that the macro name is spelt correctly?

Is it possible that the macro is present more than once, perhaps in
different modules in the addin.


---
Regards,
Norman



"Trefor" wrote in message
...
Error message is:

"The macro 'Menu_New_Site' cannot be found"

--
Trefor


"Norman Jones" wrote:

Hi Trefor,

And I get a slighty different error message but essentially the
same problem.

What error message do you get?

I created an addin and named it:
Customer Data Collect Master v6.37.xla'

I added a macro and named it: Menu_New_Site; I assigned the macro to the
button using Menu_New_Site as the macro reference. Clicking the button
the
macro (which comprised a banal message) ran as expected.

---
Regards,
Norman



"Trefor" wrote in message
...
Norman,

Interestingly after my last edit (to 'Customer Data Collect Master
v6.37.xla'!Menu_New_Site), it reverted to your suggestion anyway. And I
get a
slighty different error message but essentially the same problem.

--
Trefor


"Norman Jones" wrote:

Hi Trefor,

With the XLA file open, try:

Menu_New_Site

---
Regards,
Norman



"Trefor" wrote in message
...
Norman/Jim,

Yes I worked that bit out, found it another thread, and you were
spot
on.
I
was just coming back to let Jim know and saw your reply.

Ok, my next problem in converting my files to use RUN, is calling a
macro
from a command button. Originally the button said:

'Customer Data Collect v6.37 test.xls'!Menu_New_Site

I tried changing it to the .xla as per the RUN:

'Customer Data Collect Master v6.37.xla'!Menu_New_Site

In both cases it says it can't find the macro, any ideas?

--
Trefor


"Norman Jones" wrote:

Hi Tefor,

Please ignore my previous post; I did not see your public constant.

Try instead changing:

Public Const DCMaster = "Customer Data Collect Master v6.37.xla"
to:
Public Const DCMaster = "'Customer Data Collect Master v6.37.xla'"


The additional single quotes are required because of the spaces in
the
file
name.

---
Regards,
Norman



"Norman Jones" wrote in message
...
Hi Tefor,

Application.Run DCMaster & "!test" <----- This says it
can't
find

Look at the syntax suggested by Jim and look at the Run method in
VBA
help.

Try:

Application.Run "DCMaster.xla!test"

---
Regards,
Norman



"Trefor" wrote in message
...
Jim,

Thank you again for your reply. But I am still having problems:

This is the main workbook:

Option Explicit
Option Private Module
Public Const DCMaster = "Customer Data Collect Master v6.37.xla"

Sub Auto_Open()
If AddinPresent Then
Application.Run DCMaster & "!test" <----- This says it
can't
find
the macro
Else
MsgBox "Addin Not Present"
End If
End Sub

Function AddinPresent() As Boolean <----- This works great
thankyou
Dim WBName As String
On Error GoTo NotPresent
WBName = Workbooks(DCMaster).Name
AddinPresent = True
NotPresent:
End Function

This is the macro in the .xla:

Public Sub test()
MsgBox "test"
End Sub

Trefor
--
Trefor


"Jim Rech" wrote:

I don't know of any disadvantage except that it's a little more
cumbersome
to call a sub via Run than directly. In theory Run is a little
slower
but I
don't see a difference as a practical matter.

I don't know what you mean by "xlm". All I'm saying is replace
a
direct
call to the add-in's sub:

SubInXLA

with

Run "MyAddin.xla!SubInXLA"

Of course this requires that the add-in is open in Excel.

It will take you only a few seconds to switch one or two call
and
test
how
it works for you. check out the Run method for moe on it.

--
Jim

Norman,

I have checked everything you suggested and all appears to be in order.

As a final check, I added a reference (VBE - Tools References) and the
button's work fine. I went back in and removed the Reference and it stopped
working.

Perhaps as a variation to my original question, is it possible to code the
adding and removing of a Reference? I now this is a bit defeatist but it all
seemed to work fine with a reference added.

Before I had a permanent Reference which was fine as long as the reference
was actually there. Now with all this playing I have managed to check for the
xla, load it and use with RUN's from the main workbook. If my main program
can add the reference and remove it before closing/saving I will have
achieved my original goal.

--
Trefor


"Norman Jones" wrote:

Hi Trefor.

Is the addin loaded?

Are you sure that the macro name is spelt correctly?

Is it possible that the macro is present more than once, perhaps in
different modules in the addin.


---
Regards,
Norman



"Trefor" wrote in message
...
Error message is:

"The macro 'Menu_New_Site' cannot be found"

--
Trefor


"Norman Jones" wrote:

Hi Trefor,

And I get a slighty different error message but essentially the
same problem.

What error message do you get?

I created an addin and named it:
Customer Data Collect Master v6.37.xla'

I added a macro and named it: Menu_New_Site; I assigned the macro to the
button using Menu_New_Site as the macro reference. Clicking the button
the
macro (which comprised a banal message) ran as expected.

---
Regards,
Norman



"Trefor" wrote in message
...
Norman,

Interestingly after my last edit (to 'Customer Data Collect Master
v6.37.xla'!Menu_New_Site), it reverted to your suggestion anyway. And I
get a
slighty different error message but essentially the same problem.

--
Trefor


"Norman Jones" wrote:

Hi Trefor,

With the XLA file open, try:

Menu_New_Site

---
Regards,
Norman



"Trefor" wrote in message
...
Norman/Jim,

Yes I worked that bit out, found it another thread, and you were
spot
on.
I
was just coming back to let Jim know and saw your reply.

Ok, my next problem in converting my files to use RUN, is calling a
macro
from a command button. Originally the button said:

'Customer Data Collect v6.37 test.xls'!Menu_New_Site

I tried changing it to the .xla as per the RUN:

'Customer Data Collect Master v6.37.xla'!Menu_New_Site

In both cases it says it can't find the macro, any ideas?

--
Trefor


"Norman Jones" wrote:

Hi Tefor,

Please ignore my previous post; I did not see your public constant.

Try instead changing:

Public Const DCMaster = "Customer Data Collect Master v6.37.xla"
to:
Public Const DCMaster = "'Customer Data Collect Master v6.37.xla'"


The additional single quotes are required because of the spaces in
the
file
name.

---
Regards,
Norman



"Norman Jones" wrote in message
...
Hi Tefor,

Application.Run DCMaster & "!test" <----- This says it
can't
find

Look at the syntax suggested by Jim and look at the Run method in
VBA
help.

Try:

Application.Run "DCMaster.xla!test"

---
Regards,
Norman



"Trefor" wrote in message
...
Jim,

Thank you again for your reply. But I am still having problems:

This is the main workbook:

Option Explicit
Option Private Module
Public Const DCMaster = "Customer Data Collect Master v6.37.xla"

Sub Auto_Open()
If AddinPresent Then
Application.Run DCMaster & "!test" <----- This says it
can't
find
the macro
Else
MsgBox "Addin Not Present"
End If
End Sub

Function AddinPresent() As Boolean <----- This works great
thankyou
Dim WBName As String
On Error GoTo NotPresent
WBName = Workbooks(DCMaster).Name
AddinPresent = True
NotPresent:
End Function

This is the macro in the .xla:

Public Sub test()
MsgBox "test"
End Sub

Trefor
--
Trefor


"Jim Rech" wrote:

I don't know of any disadvantage except that it's a little more
cumbersome
to call a sub via Run than directly. In theory Run is a little
slower
but I
don't see a difference as a practical matter.

I don't know what you mean by "xlm". All I'm saying is replace
a
direct
call to the add-in's sub:

SubInXLA

with

Run "MyAddin.xla!SubInXLA"

Of course this requires that the add-in is open in Excel.

It will take you only a few seconds to switch one or two call
and
test
how
it works for you. check out the Run method for moe on it.

--
Jim