Problem Closing Sheet

[kirkm]

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

Function ReadCell(msheet, mCell)
ReadCell = ActiveWorkbook.Worksheets(msheet).Range(mCell).Value
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

 

[Per Jessen]

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

 

[kirkm]

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).Value

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

 

[Per Jessen]

Hi

You miss the worksheet reference:

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


Regards,
Per

 

[kirkm]

Many thanks Per.

Problem solved

Cheers - Kirk