Link a combo box and a textbox

slimpickens

New Member
Joined
Feb 17, 2002
Messages
35
Hi there,

I have a combo box and a text box on my form. The combo box is linked to 2 columns. What the user see would be the types of vehicles, but when the item is selected a code - which is in column 1 is placed in the box. Is there anyway I can have the description the description of the vehicle displayed in the textbox after the combobox is selected.

Here is an example

TA10 - Toyota Corolla 1.6 .

Ta10 would be placed in comobox box but how do I get Toyota corolla to be displayed in textbox.

Thanks

Slim
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Ekim

Well-known Member
Joined
Jun 30, 2002
Messages
1,416
Slim,

Here's all the code you need to:

1. Show the form.
2. Populate the combo box with the type of vehicle.
3. Populate the text box with a description when a vehicle is selected via the combo box
4. Unload the form when you are finished.

Macro 1 goes into a standard module (VBA editor Insert/Module).
Macros 2, 3 and 4 go into the Forms Module (display your form in the VBA Editor - double click it, and copy/paste macros 2, 3 and 4 in the white space behind the form.

Assumptions:
1. That your form is named UserForm1.
2. That your form also includes a Command Button (CommandButton1) to unload the form (see the third macro for the form).
3. That your combo box is named ComboBox1 (see the second macro).
4. That your text box is named TextBox1 (see the second macro).
5. That your data is in worksheet2, with the list of vehicle types in column A, starting in row 1 (row 1 could be a label such as "Vehicles"; and the vehicle description is also on worksheet2, starting in column B1 (make B1 a label such as "Description").

If the above assumptions are not valid, change the macro(s) accordingly e.g. if your list of vehicles is in say worksheet5, change the reference in macro 2 from Sheets2 to Sheets5.


NORMAL MODULE

Sub ShowForm()
'Show the form
UserForm1.Show
End Sub

Assign this macro to a button - while in Excel, (not the VBA Editor) go to Views/Toolbars/Forms.

FORMS MODULE

Private Sub UserForm_Initialize()
'Get type of vehicle from worksheet2 column A1:
ComboBox1.RowSource = Sheets(2).Name & "!A1:A100"
End Sub


Private Sub ComboBox1_Change()
'assuming worksheet2 column B is vehicle description
If ComboBox1.ListIndex >= 0 Then
TextBox1.Text = _
Sheets(2).Cells(ComboBox1.ListIndex + 1, 2).Value
Else
TextBox1.Text = "No match"
End If
End Sub


Private Sub CommandButton1_Click()
Unload Me
End Sub


Regards,

Mike
This message was edited by Ekim on 2002-09-04 10:35
 

Forum statistics

Threads
1,144,441
Messages
5,724,378
Members
422,547
Latest member
Vision1291

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top