Recalculation based on changing cell

[malcmail]

Following on from earlier posts I have an odd one.

I have a function that basically looks up a code in a hard coded array. The argument that passes to the function can be changed via another cell. So the custom function is called Bob and takes an argument rate so it could be =Bob("SRB"). Further development means that that now becomes =Bob(country & "SRB"). Country is a named single cell. This cell will link via an if statement to a dropdown box for the user. So the user selects from a dropdown of which country and the cell country changes to match. Weirldy when this happens Bob doesn't update unless I go in and recalc.

So I figured the idea was to write code in Worksheet_Change, but I always want it to look at the cell country - even though the input from the user could be almost anywhere. My problem is, while I could get the recalc to trigger on the user changing the drop down, that will be a PITA to do in 40 books. Hence alwyas having one cell. But I cannot get the event to trigger on a cell that changes, but which the user hasn't actually changed by entering in the cell. Am I missing something here or is this how it is meant to (not) work?

[malcmail]

Just stumlbed across worksheet_calculate which seems to work fine except for the fact I actually need to go into the cell still to force it to recalc (even F9 won't work). Bit confused why it doesn't update as expected.

[MrShorty]

I am not following exactly what you are doing. I use a lot of UDF's and I don't have trouble with those UDF's updating when their arguments change. Usually, when this situation occurs, it is because the UDF is receiving data from the spreadsheet other than through the argument list. Can you share a typical worksheet + UDF code that illustrates what you are seeing?

[malcmail]

As I was working through it, and then looking at what you said I think my "clever" idea to claim a variable directly from the sheet rather than passing it via the formula is the problem. I guess I was just looking to shortcut the problem giving the number of books I need to update. Looks like it is back to the drawing board. Thanks for the help though.