I'm not really looking for a validation list, I'm looking for the intellisense to appear in the formula parameters when a user types in a function.
For example, if you type =SUM(.. a list of arguments becomes visible to the user explaning what this function needs. I have found a way to register user-defined functions with excel so that this list will appear for your own functions, but I would also like the drop down intellisense to appear for an enumeration list, showing the user which options are available for that particular parameter. Just like it would if you are passing enumerated parameters to a function in the VBE.
Public Enum Compounds 'An enumerated list of compunds available for calculation
[_First = 0]
NHexane = 0
NOctane = 1
NDecane = 2
OleicAcid = 3
[_Last = 3]
Public Function GetAlpha(Compound As Compounds, HKref As Compounds, TdegC As Double) As Double
Dim PaSat As Double
Dim PbSat As Double
PaSat = PiSat(Compound, TdegC)
PbSat = PiSat(HKref, TdegC)
GetAlpha = PaSat / PbSat
Here, the parameters Compund and HKref are enumerated lists. If you call this function in the VBE, a drop down list of arguments will appear for those parameters telling you what you can use. I would like for this drop down list to appear in the function bar when calling this function from the spreadsheet.
Does that sound a little more clear in what I am looking for?
Well, You can try banging your head against the wall, but you just end up with lost-time injuries and damaged equipment. [M. Passman]