Automatically Sorting a Validation List

[smowen]

In Excel I have a validation list which takes values from cells in other worksheets, so the validation list is made of up formulas with cell references in.

I want to have a macro which automatically sorts the list if a value within the list changes.

I tried using a worksheet_change macro, but this only works if the content of a cell changes and doesn't work with formulas.

Is there any way to do this?

Cheers

[Tequilaman]

I just solved something similar by using a port listener. But in my case there is a database link implemented as well for other reasons. I just use that one with an adjusted port#.

Could it be an option for you?

[smowen]

I'm sorry but I don't know what a port listener is. But there is no database linked to it. Any help?

[Tequilaman]

I have a port listener like for MsSql connection attached to my functions. It's normally used for the database for the application server <--> database server connection

[Killer42]

I don't recall what events are available in Excel VBA, but could you perhaps fire the sort routine when the user enters a relevant cell, or begins to update it?

Otherwise, perhaps you could set it up to re-run the sort periodically.

Hm... here's another thought. Could you create a function that is used in getting the values from the other worksheets to the list? Then recalculation would have to call your function.