Automatic updating
 

Hello,
I have a workbook that contains one worksheet that is used as a template for
OLE Automation. Through automation I insert a new worksheet at position 1 in
the worksheets collection, copy the worksheet that is used as a template,
and then update the inserted worksheet with data, also through automation.
My users want another worksheet in this workbook, placed at position 3 in
the worksheets collection that grabs data from the information that I place
in the worksheet that I update (as stated previously). These users would
like to have their worksheet updated automatically so I wrote them a little
function as follows:

Public Function MyCellValue(sCell As String)
On Error GoTo eh
Dim sht As Worksheet

Set sht = Worksheets.Item(1)
MyCellValue = sht.Range(sCell).Value

Exit Function
eh:
Debug.Print Err.Number; Err.Description
End Function

When we insert the following into a cell:
=MyCellValue("B8")

their worksheet updates as expected. However, it doesn't work once they've
placed all the function calls into the cell and then I open up the file and
insert the expected worksheet at position 1 and update the data. Can anybody
tell me how to make their worksheet update automatically when I open up the
file and update it? Excel is set to Automatically calculate on the
Calculation tab of the options dialog.

Any help would be much appreciated and I hope that I have been clear and
concise enough in my explaination.

Steve Long