Solved: Listbox starting value - vlookup disappears?

[Simon Lloyd]

Here's a strange one gents, attached is a workbook where i was trying to help another op's at another forum, the request seemed simple enough....."..if i have a list of numbers for a list box and i type a number in to a cell how can i get the listbox to start at the nearest number to the one in the cell?"....so below i created a userform, a list of numbers called "picks" and in B1 was my control number (which i added to the Control Source of the ListBox properties) all works well unless the number does not appear in the list....not to be beaten i thought ok how about a Vlookup with lookup_range set to TRUE i entered this in C1 changed the control source and it worked.....but only the once as the formula was then deleted, i have tried numerous things but must say its beyond me, Why is it deleting the formula from C1?

Regards,
Simon

[Charlize]

Use this instead of your coding for the form. Nearest value will be selected in listboxlist. [VBA]Private Sub UserForm_Activate()
Me.ListBox1.Value = Application.WorksheetFunction.VLookup(Range("B1"), Range("A1:A41"), 1, True)
End Sub[/VBA]Charlize

[Simon Lloyd]

Thanks Charlize, i dont think i would have thought to do that in the Userform Activate. I posted the solution here http://www.excelforum.com/showthread...61#post1765661

Regards,
Simon

[Simon Lloyd]

Any ideas why the formula was getting deleted after the userform was initialised?

Regards,
SImon

[moa]

I've had the same problem before and it's related to controlSource, specifically the fact that it's next to useless if you are trying to do something with a formula. It's a two way thing. Control populates cell, cell populates control. This means that the formula calculates and the calculated value in the cell then populates the control on the form, which in turn populates the cell. Bingo, no formula, just the value.

[Simon Lloyd]

specifically the fact that it's next to useless if you are trying to do something with a formula.

nice touch by MS don't you think? it had me baffled, i would have thought Control Source would be a constant else how can it be a reference point if it chnaged by the very item that looks to it?

Anyway thanks for you explanation Glen....and thanks again Charlize!

Regards,
Simon