Look Up Box In a User Form

[JohnnyTheAmmer]

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

[Scott]

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
>

[JohnnyTheAmmer]

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
> >