how findout the calling cell address in a function

[krish]

hi,
how do I know which cell called my userdefined function?. for example, I have a macro in a module and user used the function in several cells. when the user changes some value, the function is being called. Is there anyway I can find out which cell called the user defined function?. I need to do this programatically. please help. thanks

[raypayette]

Maybe you want Activecell.Select?

[krish]

i tried and Active cell won't work.

[Loz]

Without knowing what the user defined function is exactly, a couple of suggestions are -

(a) use the Worksheet_Change event to trap when a user makes a change anywhere in the worksheet. This event provides the cell address (Target) that was changed, which could be assigned to a global range variable and picked up by your function.

Alternatively, use the Workbook_SheetSelectionChange event if the function is deployed on several Worksheets;

(b) if your user-defined function is being activated by a change on the worksheet, then surely one of the parameters being passed to the function identifies the cell address? Alternatively, you could define your function to require the user to pass the range or cell details as a parameter wherever the function is used on the worksheet.

[Bryan Hessey]

i tried and Active cell won't work.

try

=myfunc()


Function myfunc()
myfunc = ActiveCell.Address
End Function


hth
---