CODEWITHSUNDEEP
excelworksheet
user1787425
user1787425

Reputation: 33

Active Worksheet vs Calling Worksheet

I have an Excel VBA function (below) which is called from several worksheets and references a local named cell whose value is different in each worksheet. When I update my workbook by hitting F9, each call to the function produces an identical result, because all calls to the function pick up the local cell value from the worksheet which is the focus when I hit the F9 key. I now understand that this is how the "active" worksheet is defined. I want to pick up the value of the local named cell in each worksheet from which the function is called. Can someone tell me how to do this? 

Function AcquisDate()
    Application.Volatile True
    [WOULD LIKE TO SET ACTIVE WORKSHEET TO CALLING WORKSHEET HERE
    AcquisDate = Range("AcquisDateCell") 'This cell name is local to each worksheet.
End Function

Upvotes: 0

Views: 457

Answers (1)

V.B.
V.B.

Reputation: 6382

Use Application.Caller.Parent.Range(...) like in example below

Function AcquisDate()
    Application.Volatile True
    AcquisDate = Application.Caller.Parent.Range("AcquisDateCell")
End Function

Upvotes: 1

Related Questions