I am using a 2002 Excel workbook Auto_Open marco to run a macro in an
Addin.xla that I created. The workbook macro contains a procedure -
Range("WorkbookName") = ActiveWorkbookName - because the Addin is intended to
be used with workbooks with variable names. The Addin Marco that is called
from the Auto_Open macro contains the following procedure - WBFilename =
Worksheets("Calculations").Range("WorkbookName"). WBFileName is used in many
macros in the Addin to identify the workbook. At the point of such Addin
macro I get a message "Run-Time error '9': subscript out of range". When I
run the Auto_Open macro from the VBA menu "Run" the error does not happen,
and both macros work fine. Why do I get the error message? If you would
like, I can email a sample of these two macros. I have tried lots of ways to
solve this, but have not been successful.

Martin,

The Auto_Open macro in the add-in will only run when the add-in workbook is opened.
It does not run when other workbooks are opened.

Jim Cone
San Francisco, USA


"Martin in Frisco Texas" <Martin in Frisco
wrote in message

I am using a 2002 Excel workbook Auto_Open marco to run a macro in an
Addin.xla that I created. The workbook macro contains a procedure -
Range("WorkbookName") = ActiveWorkbookName - because the Addin is intended to
be used with workbooks with variable names. The Addin Marco that is called
from the Auto_Open macro contains the following procedure - WBFilename =
Worksheets("Calculations").Range("WorkbookName"). WBFileName is used in many
macros in the Addin to identify the workbook. At the point of such Addin
macro I get a message "Run-Time error '9': subscript out of range". When I
run the Auto_Open macro from the VBA menu "Run" the error does not happen,
and both macros work fine. Why do I get the error message? If you would
like, I can email a sample of these two macros. I have tried lots of ways to
solve this, but have not been successful.

Jim Cone

Thanks for your reply.

The Auto_Open macro is in the workbook, not the Add-in. The Auto_Open Macro
calls a sub in the Addin called "OpenProc". I get the error messaage in the
"OpenProc" sub.

Martin

"Jim Cone" wrote:

Martin,

The Auto_Open macro in the add-in will only run when the add-in workbook is opened.
It does not run when other workbooks are opened.

Jim Cone
San Francisco, USA


"Martin in Frisco Texas" <Martin in Frisco
wrote in message

I am using a 2002 Excel workbook Auto_Open marco to run a macro in an
Addin.xla that I created. The workbook macro contains a procedure -
Range("WorkbookName") = ActiveWorkbookName - because the Addin is intended to
be used with workbooks with variable names. The Addin Marco that is called
from the Auto_Open macro contains the following procedure - WBFilename =
Worksheets("Calculations").Range("WorkbookName"). WBFileName is used in many
macros in the Addin to identify the workbook. At the point of such Addin
macro I get a message "Run-Time error '9': subscript out of range". When I
run the Auto_Open macro from the VBA menu "Run" the error does not happen,
and both macros work fine. Why do I get the error message? If you would
like, I can email a sample of these two macros. I have tried lots of ways to
solve this, but have not been successful.

Martin,

Some comments...
An add-in always uses the "ActiveSheet" when references to
ranges are made, unless a specific workbook/sheet reference is used.
So Range("A1") refers to that range on the active sheet in the active workbook.
(the add-in is never active)

Workbooks("FileName.xls").Worksheets(1).Range("A1" ) refers to that range
on the first worksheet, in the FileName workbook, if that workbook is open,
otherwise you get an error.

However, if you assign a value to a variable...
Dim FileName as String
FileName = "OtherName.xls"
Then Workbooks(FileName).Worksheets(1).Range("A1") refers to the "OtherName" workbook.
Note - that there are no quote marks surrounding the variable, as you have already
told Excel that it is a String.

If you only want your add-in to run its code with certain workbooks then
you can check the active workbook name against a list of valid names...

Dim strName as String
strName = ActiveWorkbook.Name
If strName = "Larry.xls" or strName = "Moe.xls" or strName = "Curly.xls" then
'do stuff
Else
Exit Sub
End If

Regards,
Jim Cone
San Francisco, USA
'------------------------------


"Martin in Frisco Texas"

wrote in message
...
Jim Cone
Thanks for your reply.
The Auto_Open macro is in the workbook, not the Add-in. The Auto_Open Macro
calls a sub in the Addin called "OpenProc". I get the error messaage in the
"OpenProc" sub.
Martin


"Jim Cone" wrote:
Martin,
The Auto_Open macro in the add-in will only run when the add-in workbook is opened.
It does not run when other workbooks are opened.
Jim Cone
San Francisco, USA


"Martin in Frisco Texas" <Martin in Frisco
wrote in message

I am using a 2002 Excel workbook Auto_Open marco to run a macro in an
Addin.xla that I created. The workbook macro contains a procedure -
Range("WorkbookName") = ActiveWorkbookName - because the Addin is intended to
be used with workbooks with variable names. The Addin Marco that is called
from the Auto_Open macro contains the following procedure - WBFilename =
Worksheets("Calculations").Range("WorkbookName"). WBFileName is used in many
macros in the Addin to identify the workbook. At the point of such Addin
macro I get a message "Run-Time error '9': subscript out of range". When I
run the Auto_Open macro from the VBA menu "Run" the error does not happen,
and both macros work fine. Why do I get the error message? If you would
like, I can email a sample of these two macros. I have tried lots of ways to
solve this, but have not been successful.

Jim

Thanks again.

What this all boils down to is that I want to make WBFileName equal to the
active workbook name, because each workbook that is intended to worik with
the addin will have a different filename. I can do that by WBFileName =
ActiveWorkbook.Name in the Addin macro "OpenProc" and it works, but I get
"out of script" error messages in other macros that are called by the
OpenProc macro. It is just not consistent. When these other macros are
executed by menus, they work fine.

Martin

"Jim Cone" wrote:

Martin,

Some comments...
An add-in always uses the "ActiveSheet" when references to
ranges are made, unless a specific workbook/sheet reference is used.
So Range("A1") refers to that range on the active sheet in the active workbook.
(the add-in is never active)

Workbooks("FileName.xls").Worksheets(1).Range("A1" ) refers to that range
on the first worksheet, in the FileName workbook, if that workbook is open,
otherwise you get an error.

However, if you assign a value to a variable...
Dim FileName as String
FileName = "OtherName.xls"
Then Workbooks(FileName).Worksheets(1).Range("A1") refers to the "OtherName" workbook.
Note - that there are no quote marks surrounding the variable, as you have already
told Excel that it is a String.

If you only want your add-in to run its code with certain workbooks then
you can check the active workbook name against a list of valid names...

Dim strName as String
strName = ActiveWorkbook.Name
If strName = "Larry.xls" or strName = "Moe.xls" or strName = "Curly.xls" then
'do stuff
Else
Exit Sub
End If

Regards,
Jim Cone
San Francisco, USA
'------------------------------


"Martin in Frisco Texas"

wrote in message
...
Jim Cone
Thanks for your reply.
The Auto_Open macro is in the workbook, not the Add-in. The Auto_Open Macro
calls a sub in the Addin called "OpenProc". I get the error messaage in the
"OpenProc" sub.
Martin


"Jim Cone" wrote:
Martin,
The Auto_Open macro in the add-in will only run when the add-in workbook is opened.
It does not run when other workbooks are opened.
Jim Cone
San Francisco, USA


"Martin in Frisco Texas" <Martin in Frisco
wrote in message

I am using a 2002 Excel workbook Auto_Open marco to run a macro in an
Addin.xla that I created. The workbook macro contains a procedure -
Range("WorkbookName") = ActiveWorkbookName - because the Addin is intended to
be used with workbooks with variable names. The Addin Marco that is called
from the Auto_Open macro contains the following procedure - WBFilename =
Worksheets("Calculations").Range("WorkbookName"). WBFileName is used in many
macros in the Addin to identify the workbook. At the point of such Addin
macro I get a message "Run-Time error '9': subscript out of range". When I
run the Auto_Open macro from the VBA menu "Run" the error does not happen,
and both macros work fine. Why do I get the error message? If you would
like, I can email a sample of these two macros. I have tried lots of ways to
solve this, but have not been successful.

Martin,

To call a macro in an add-in you can use the following syntax...
Application.Run "YourAdd-in.xla!YourSubName"

If there are arguments required for the sub then...
Application.Run "YourAdd-in.xla!YourSubName", Arg1, Arg2

Note the exclamation point !

Regards,
Jim Cone


"Martin in Frisco Texas"

wrote in message
...
Jim

Thanks again.

What this all boils down to is that I want to make WBFileName equal to the
active workbook name, because each workbook that is intended to worik with
the addin will have a different filename. I can do that by WBFileName =
ActiveWorkbook.Name in the Addin macro "OpenProc" and it works, but I get
"out of script" error messages in other macros that are called by the
OpenProc macro. It is just not consistent. When these other macros are
executed by menus, they work fine.

Martin

"Jim Cone" wrote:

Martin,

Some comments...
An add-in always uses the "ActiveSheet" when references to
ranges are made, unless a specific workbook/sheet reference is used.
So Range("A1") refers to that range on the active sheet in the active workbook.
(the add-in is never active)

Workbooks("FileName.xls").Worksheets(1).Range("A1" ) refers to that range
on the first worksheet, in the FileName workbook, if that workbook is open,
otherwise you get an error.

However, if you assign a value to a variable...
Dim FileName as String
FileName = "OtherName.xls"
Then Workbooks(FileName).Worksheets(1).Range("A1") refers to the "OtherName" workbook.
Note - that there are no quote marks surrounding the variable, as you have already
told Excel that it is a String.

If you only want your add-in to run its code with certain workbooks then
you can check the active workbook name against a list of valid names...

Dim strName as String
strName = ActiveWorkbook.Name
If strName = "Larry.xls" or strName = "Moe.xls" or strName = "Curly.xls" then
'do stuff
Else
Exit Sub
End If

Regards,
Jim Cone
San Francisco, USA
'------------------------------


"Martin in Frisco Texas"

wrote in message
...
Jim Cone
Thanks for your reply.
The Auto_Open macro is in the workbook, not the Add-in. The Auto_Open Macro
calls a sub in the Addin called "OpenProc". I get the error messaage in the
"OpenProc" sub.
Martin


"Jim Cone" wrote:
Martin,
The Auto_Open macro in the add-in will only run when the add-in workbook is opened.
It does not run when other workbooks are opened.
Jim Cone
San Francisco, USA


"Martin in Frisco Texas" <Martin in Frisco
wrote in message

I am using a 2002 Excel workbook Auto_Open marco to run a macro in an
Addin.xla that I created. The workbook macro contains a procedure -
Range("WorkbookName") = ActiveWorkbookName - because the Addin is intended to
be used with workbooks with variable names. The Addin Marco that is called
from the Auto_Open macro contains the following procedure - WBFilename =
Worksheets("Calculations").Range("WorkbookName"). WBFileName is used in many
macros in the Addin to identify the workbook. At the point of such Addin
macro I get a message "Run-Time error '9': subscript out of range". When I
run the Auto_Open macro from the VBA menu "Run" the error does not happen,
and both macros work fine. Why do I get the error message? If you would
like, I can email a sample of these two macros. I have tried lots of ways to
solve this, but have not been successful.