Look Up Box In a User Form
 

Hi,
I have created a user form for a spreadsheet solution, when opened it
provides various functions for handling data and exporting it to other
workbooks etc,
I am however stuck on one aspect of the form, I have a sheet titled 'Lookup'
On it I have in column (A) a list of Councils, in column (B) is the council
area code,
On my user form I have a combo box which is to be used to look up the
council name, next to it I have another text box in which I want to display
the area code for the selected council, at present I can get the combo box to
show the council and its area code when you scroll down the list, however
when you select a particular council it just shows the area code in the combo
box and nothing in the other text box next to it,
Can anyone help€¦€¦€¦€¦€¦€¦€¦€¦.

Regards
JTH

Look Up Box In a User Form
 

Try something along these lines.

Private Sub ComboBox1_Change()

If ComboBox1 < "" Then

TextBox1.Value = Excel.WorksheetFunction.VLookup(ComboBox1.Value,
Sheets("lookup").Range("A1:B65536"), 2, False)

End If


End Sub

anyways if the value is not there, it will probably give you a #num# value.
But I imagine you are populating the combobox with the data in column A.

Cheers,

Scott

"JohnnyTheAmmer" wrote:

Hi,
I have created a user form for a spreadsheet solution, when opened it
provides various functions for handling data and exporting it to other
workbooks etc,
I am however stuck on one aspect of the form, I have a sheet titled 'Lookup'
On it I have in column (A) a list of Councils, in column (B) is the council
area code,
On my user form I have a combo box which is to be used to look up the
council name, next to it I have another text box in which I want to display
the area code for the selected council, at present I can get the combo box to
show the council and its area code when you scroll down the list, however
when you select a particular council it just shows the area code in the combo
box and nothing in the other text box next to it,
Can anyone help€¦€¦€¦€¦€¦€¦€¦€¦.

Regards
JTH

Look Up Box In a User Form
 

Scott

Many Thanks, a little tweaking got that up and running

Cheers

JTH

"Scott" wrote:

Try something along these lines.

Private Sub ComboBox1_Change()

If ComboBox1 < "" Then

TextBox1.Value = Excel.WorksheetFunction.VLookup(ComboBox1.Value,
Sheets("lookup").Range("A1:B65536"), 2, False)

End If


End Sub

anyways if the value is not there, it will probably give you a #num# value.
But I imagine you are populating the combobox with the data in column A.

Cheers,

Scott

"JohnnyTheAmmer" wrote:

Hi,
I have created a user form for a spreadsheet solution, when opened it
provides various functions for handling data and exporting it to other
workbooks etc,
I am however stuck on one aspect of the form, I have a sheet titled 'Lookup'
On it I have in column (A) a list of Councils, in column (B) is the council
area code,
On my user form I have a combo box which is to be used to look up the
council name, next to it I have another text box in which I want to display
the area code for the selected council, at present I can get the combo box to
show the council and its area code when you scroll down the list, however
when you select a particular council it just shows the area code in the combo
box and nothing in the other text box next to it,
Can anyone help€¦€¦€¦€¦€¦€¦€¦€¦.

Regards
JTH