Problem Closing Sheet

K

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
 
P

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
 
K

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?
 
P

Per Jessen

Hi

You miss the worksheet reference:

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


Regards,
Per
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top