Hi NickHK

Adding the debug.print call shows me the expected cell address. But, as I
mentioned to JLGWhiz, I'm not too concerned about getting this function to
work. It seems to work ok, except that it breaks another function which
should be totally unrelated...

It almost looks like this could be an Excel/VBA bug of some sort. I don't
know.

If you can think of some other method to use, to accomplish the same thing
(ie filling in the neighboring column with some default values, when when the
user selects an item from the drop-down list), then I'll be willing to try
that. I can't seem to get around this one! :-(

Thanks all of you for your time.
Emma


"NickHK" wrote:

Not sure if this applies, but Application.Caller (as was before
Application.ThisCell) could produce unexpected results, at least for me.
Could be because you are not passing the 2 ranges as arguments.
If you added a "debug.print .Thiscell.address", you may find it is being
called from an unexpected cell.

NickHK

"Embirath" wrote in message
...
Hi there

I've had this problem with an Excel program of mine for quite some time
now.
Have posted this problem to many news groups but haven't been able to find
a
solution yet. I finally was able to narrow down the problem, so I think it
is
now fairly well defined. I'm hoping this will help. Any help would be
greatly
appreciated!

I have one column in my spreadsheet which has "data validation" set, so
that
there is a drop-down list of allowed pre-set values. The user can go in
and
change the contents of these cells by picking an item from the drop-down
lists. When the user does this, the cell immediately to the right of it is
filled out with some default parameters. I use the "Worksheet_Change"
function to make this happen. Any time a cell changes in this particular
column, a function is called which fills out the appropriate information
in
the neighboring column. Fine, everything works great so far.

The problem comes in if I happen to call another function that I wrote in
this same spreadsheet. I call this my "haunted" function... which makes
the
above function break... The functions seem completely unrelated, but
perhaps
I'm missing something. When I call this function from some other cell in
the
spreadsheet, nothing happens when I select an item from the drop-down
lists
mentioned above (the information that is supposed to be entered in
neighboring column doesn't appear). Here is my "haunted" function:

Public Function diffr() As Date
Application.Volatile (True)
With Application
diffr = .ThisCell.Offset(0, -1).Value2 - .ThisCell.Offset(-1,
-1).Value2
End With
End Function

This function simply calulates the difference between two dates/times, in
the two cells immediately to the left and "up and to the left" of
whichever
cell calls the function. (There is a long story of why I wrote this
function
rather than just simply typing in the formula in the cell - but please
ignore
this for now.)

It would be easier to just send my spreadsheet, which clearly shows the
problem, but I don't see a way to do that here. If you need more
information,
please let me know.

If any of you have any ideas what might be going on, Please let me know!

Thank you!!
Emma