Update a text box on a user form based on changes in another control

[Phillip Topping]

On my user form, I have a combo box which is linked to a named ranged. In
this range is a list of possible fare types....for now lets just call them A
to B, B to C, etc etc.

When I select one of these fares in the combo box, I want to tab to another
text box, and have the relevant fare cost appear in that box so the user can
be certain of the cost involved.

The corresponding fares are in the next column on the sheet beside the named
range, but at present, when I tab to the text box I get either no result or
an error message.

I have been trying to use WorksheetFunctionVlookup....etc but obviously I am
using it in the wrong part of the form code.

Any suggestions would be appreciated.

TIA
Phillip

 

[Don Pistulka]

Phillip,

If the fare costs (range name = farecost) is in the same row as the fare
type then you can use the listindex number of the combobox to return the
fare cost.

Put this in the userform module. (Change the names of the combobox and
textbox to fit your needs).


Private Sub ComboBox1_Change()
Set mycost = Range("farecost")
TextBox1.Value = mycost(ComboBox1.ListIndex + 1)
End Sub

Don Pistulka