Dependant Combobox

[MBlake]

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

 

[Toppers]

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]

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

 

[Toppers]

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]

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