Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dependant Combobox
Hi,
I have read quite a few article son comboboxes but am struggling with the following - I am currently using a worksheet that has dependant lists based on INDIRECT method. My boss wants the input area to be a userform that holds a series of comboboxes for data entry. . I would be grateful for advice on the following - 1. User enters a pin number into the first combobox, I am unable to figure out how to then have the combobox entry do a VLOOKUP and display the associated data into 4 other boxes on the userform. I can do this with the worksheet and imagine it can be done on the combobox, the idea is that the user sees their pin number entry automatically generate their associated name, workplace etc. 2. I then need to have the next combobox offer a range based on one of the entries returned by the VLOOKUP. For example, USER enters their PIN and 4 boxes are populated with USER NAME, UNIT, WORKPLACE and CONTACT. The USER is then taken to the next combobox whose choices have been populated as a result of the UNIT field. I have tried the MS advice to populate the comboboxes via the RowSource property (e.g. Lists!B2:B8) but am unable to figure out how to reference the returned value UNIT. I would be extremely grateful for any advice. Mickey |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dependant Combobox
Hi,
For answer to 1. try: Private Sub ComboBox1_Change() Dim pin As String, i As Integer, LookupRng As Range ' Named range (User_Table) is 5 colums (pin,UserName, etc) Set LookupRng = Range("User_Table") pin = ComboBox1.Value ' Selected pin number ' ' Assumes data is placed in textbox1 to textbox4 ' Select items 2 to 5 from User_Table (user Name, unit, Workplace and Contact ', For i = 1 To 4 Me.Controls("Textbox" & i).Value = Application.VLookup(pin, LookupRng, i + 1, False) Next i End Sub Private Sub UserForm_Initialize() ComboBox1.RowSource = "$a2:$a10" ' Sets up Pin numbers in combobox1 (first column of User_Table End Sub for 2. do you want to select a range of data depending on value of unit to populate the second combobox? If so, you could name the ranges as "Unit 1", "Unit 2" etc and populate combobox using additem. HTH HTH "MBlake" wrote: Hi, I have read quite a few article son comboboxes but am struggling with the following - I am currently using a worksheet that has dependant lists based on INDIRECT method. My boss wants the input area to be a userform that holds a series of comboboxes for data entry. . I would be grateful for advice on the following - 1. User enters a pin number into the first combobox, I am unable to figure out how to then have the combobox entry do a VLOOKUP and display the associated data into 4 other boxes on the userform. I can do this with the worksheet and imagine it can be done on the combobox, the idea is that the user sees their pin number entry automatically generate their associated name, workplace etc. 2. I then need to have the next combobox offer a range based on one of the entries returned by the VLOOKUP. For example, USER enters their PIN and 4 boxes are populated with USER NAME, UNIT, WORKPLACE and CONTACT. The USER is then taken to the next combobox whose choices have been populated as a result of the UNIT field. I have tried the MS advice to populate the comboboxes via the RowSource property (e.g. Lists!B2:B8) but am unable to figure out how to reference the returned value UNIT. I would be extremely grateful for any advice. Mickey |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dependant Combobox
Wow!, Cheers Toppers,
I am printing your reply off as I will need to work my way through it all. If I get a problem can I repost to this thread for further help? Mickey |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dependant Combobox
Yes, ... that's the idea. There are plenty of experts (not me!) who are very
willing and veyr,very able to help. Good luck. "MBlake" wrote: Wow!, Cheers Toppers, I am printing your reply off as I will need to work my way through it all. If I get a problem can I repost to this thread for further help? Mickey |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dependant Combobox
Hi Toppers,
Your code worked perfectly, I just had to changed the reference $a2:$a10 to "rollcall" and the code referenced my named range perfectly. I am going to take apart your code and see what i can learn from it, thanks again! Mickey |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
fill combobox depending on selection from another combobox | Excel Discussion (Misc queries) | |||
dependant combobox list | New Users to Excel | |||
Dependant Lists | Excel Discussion (Misc queries) | |||
How Do I Load A ComboBox RowSource From The Results Of Another ComboBox | Excel Programming | |||
second combo box will be dependant | Excel Programming |