PDA

View Full Version : Solved: Getting TextBox to display multiple Vlookup's on Combobox change?



Simon Lloyd
04-26-2007, 10:50 AM
Hi all, below is some code that works if i only look up the value of the first combobox....what i am trying to do is....on my userform i have 5 comboboxes and when a value is changed (these values are short codes for a longer description) lookup the value and display the long description in the Textbox, then when the next ComboBox is changed display that value ASWELL as the value from the first combobox and so on, i wasnt sure how to get TextBox10 to change value everytime a selection is made which is why i used mousemove!
The code as it is will not work for multiple lookups only a single....Any ideas?
Private Sub UserForm_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
Me.TextBox10.Value = Application.WorksheetFunction.VLookup(ComboBox1.Value, Range("caredesc"), 2, False) & ", " & _
Application.WorksheetFunction.VLookup(ComboBox2.Value, Range("caredesc"), 2, False) & ", " & _
Application.WorksheetFunction.VLookup(ComboBox3.Value, Range("caredesc"), 2, False) & ", " & _
Application.WorksheetFunction.VLookup(ComboBox4.Value, Range("caredesc"), 2, False) & ", " & _
Application.WorksheetFunction.VLookup(ComboBox5.Value, Range("caredesc"), 2, False)
End Sub
Regards,
Simon

Bob Phillips
04-26-2007, 11:33 AM
Private Sub ComboBox1_Change()
UpdateOutput
End Sub
Private Sub ComboBox2_Change()
UpdateOutput
End Sub
Private Sub ComboBox3_Change()
UpdateOutput
End Sub
Private Sub ComboBox4_Change()
UpdateOutput
End Sub
Private Sub ComboBox5_Change()
UpdateOutput
End Sub

Private Sub UpdateOutput()
Me.TextBox10.Value = _
IIf(IsError(Application.VLookup(ComboBox1.Value, Range("caredesc"), 2, False)), "", _
Application.VLookup(ComboBox1.Value, Range("caredesc"), 2, False)) & ", " & _
IIf(IsError(Application.VLookup(ComboBox2.Value, Range("caredesc"), 2, False)), "", _
Application.VLookup(ComboBox2.Value, Range("caredesc"), 2, False)) & ", " & _
IIf(IsError(Application.VLookup(ComboBox3.Value, Range("caredesc"), 2, False)), "", _
Application.VLookup(ComboBox3.Value, Range("caredesc"), 2, False)) & ", " & _
IIf(IsError(Application.VLookup(ComboBox4.Value, Range("caredesc"), 2, False)), "", _
Application.VLookup(ComboBox4.Value, Range("caredesc"), 2, False)) & ", " & _
IIf(IsError(Application.VLookup(ComboBox5.Value, Range("caredesc"), 2, False)), "", _
Application.VLookup(ComboBox5.Value, Range("caredesc"), 2, False))
End Sub

Simon Lloyd
04-26-2007, 11:46 AM
Haha, nice to see i wasnt a million miles away!

Thanks you oh great one!