I've a function which reads a value from a cell... it's

Function ReadCell(msheet, mCell)
ReadCell = ActiveWorkbook.Worksheets(msheet).Range(mCell).Val ue
End Function

.... and normally works fine.

But if there's a second Workbook open, closing Excel invokes
'Run-time error '9': Subscript out of range' on the ReadCell= line.

Private Sub Workbook_BeforeClose in Workbook 1 is calling this.
Variables msheet and mcell are both present, and correct.

The erorr only occurs when Workbook 2 is 'active'. In other words
calling the function from Workbook 2's immediate window causes the
error, but not Workbook 1.

I hope that makes sense... it's the same immediate window, the
difference is only which underlyng workbook is open (has focus?).

I suspect it's something to do with Closing book 2 somehow using 1's
before close event but I don't quite understand it, or how to get
round it. Any help appreciated.

Thanks - Kirk

Hi

If I get it right shall only work on workbook1, try to change from:

ReadCell= ActiveWorkbook.Worksheets.....

To:

ReadCell=Workbooks("Book1.xls").Worksheets...

where Book1.xls is the name of book1

HTH

------
Per

On 7 Jan., 02:18, kirkm wrote:
I've a function which reads a value from a cell... *it's

Function ReadCell(msheet, mCell)
ReadCell = ActiveWorkbook.Worksheets(msheet).Range(mCell).Val ue
End Function

... and normally works fine.

But if there's a second Workbook open, closing Excel invokes
'Run-time error '9': Subscript out of range' on the ReadCell= line.

Private Sub Workbook_BeforeClose in Workbook 1 is calling this.
Variables msheet and mcell are both present, and correct.

The erorr only occurs when Workbook 2 is 'active'. In other words
calling the function from Workbook 2's *immediate window causes the
error, but not Workbook 1.

I hope that makes sense... it's the same immediate window, the
difference is only which underlyng workbook is open (has focus?).

I suspect it's something to do with Closing book 2 somehow using 1's
before close event but I don't quite understand it, or how to get
round it. *Any help appreciated.

Thanks - Kirk

On Tue, 6 Jan 2009 17:32:22 -0800 (PST), Per Jessen
wrote:

Hi

If I get it right shall only work on workbook1, try to change from:

ReadCell= ActiveWorkbook.Worksheets.....

To:

ReadCell=Workbooks("Book1.xls").Worksheets...

where Book1.xls is the name of book1

Thanks for the suggestion. Unfortunately (unless I've done
something wrong) it gives 'Method or data member not found'
error with '.Range' selected.

The full changed line is

ReadCell = Workbooks("Test.xls").Worksheets.Range(mCell).Valu e

Test.xls is supposedt to be the actual filename? And no path details
needed?

Hi

You miss the worksheet reference:

ReadCell = Workbooks("Test.xls").Worksheets(mSheet).Range
(mCell).Value


Regards,
Per

On 7 Jan., 06:12, kirkm wrote:
On Tue, 6 Jan 2009 17:32:22 -0800 (PST), Per Jessen

wrote:
Hi

If I get it right shall only work on workbook1, try to change from:

ReadCell= ActiveWorkbook.Worksheets.....

To:

ReadCell=Workbooks("Book1.xls").Worksheets...

where Book1.xls is the name of book1

Thanks for the suggestion. Unfortunately (unless I've done
something wrong) it gives 'Method or data member not found'
error with '.Range' selected.

The full changed line is

ReadCell = Workbooks("Test.xls").Worksheets.Range(mCell).Valu e

Test.xls is supposedt to be the actual filename? And no path details
needed?

Many thanks Per.

Problem solved :)

Cheers - Kirk