Sleeper: Sub & Function

[greatheavy]

Hello All,

I created a function refering to different spreadsheets (so far nothing exceptional).

On one of the sheets, I created a dropdown menu (control), linked to a cell.

When I do change the dropdown, then the function referring to it adjusts only one time after editing VBA (VBA editor).
After that, any change on the dropdown won't affect the function (though the cell to which it refers is updated)...

What I mean by VBA editing, is just adding a comment anywhere (not specifically related to the function)

Any idea??? I tried a sub on_change () calculate =>Same result...

Thanks all for your help...

[sheeeng]

Can you tell in more details.
Is what you mean in Sheet is Combo Box?


Sorry, I can't get what you say.
Thanks.

[Bob Phillips]

Hello All,

I created a function refering to different spreadsheets (so far nothing exceptional).

On one of the sheets, I created a dropdown menu (control), linked to a cell.

When I do change the dropdown, then the function referring to it adjusts only one time after editing VBA (VBA editor).
After that, any change on the dropdown won't affect the function (though the cell to which it refers is updated)...

What I mean by VBA editing, is just adding a comment anywhere (not specifically related to the function)

Any idea??? I tried a sub on_change () calculate =>Same result...

Thanks all for your help...

Are you using Excel 97?

[greatheavy]

Yes it is a combo Box, on one of the sheets (let call it sheet "X").
The function is on a Modul
The function applies on the same sheet "X":
It tests the value of the cell linked to the combo box (still on the "X" sheet)
It refers to a value on column A of the "X" sheet.

Then it goes to a "Y" sheet, and while the first column of this "Y" Sheet is not empty, it returns the value corresponding to the value on column A (Ok, sorry for the unclear explanation... It is basically a Vlookup referring to different fields).

Does that make it any clearer? THanks again.

[greatheavy]

My concern, is that normally, any function would update when you modify the content of a cell...

ex,
function test(a)
test = a*2
end function

if cell A1 =4 test(A1)=8 => If I modify A1, then Test adjusts, correct?
Well in my case, let say I change the A1 cell, and the function doesn't recalculate...

[greatheavy]

Nope, I'm using XL 2003

[Jacob Hilderbrand]

Put this at the start of your function.

Application.Volatile

Also maybe you can post an attachment that will help us take a closer look.