Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I fill a cell in a user form from a selection on same form? | Excel Discussion (Misc queries) | |||
How to: User Form to assign a user defined range to a macro variab | Excel Programming | |||
user form | Excel Programming | |||
I am looking to see if anybody has an equivalant user form to Outlooks CONTACT form | Excel Programming |