Solved: Linking comboboxes to data ranges

[hhnebula]

I have a userform which I am using to capture data and populate onto a data log on a new worksheet. Currently I have manually entered the elements of a combobox on the userform with values e.g.


With cboResponsible1
.AddItem "Project Manager"
.AddItem "Tender Control Manager"
.AddItem "Technical Manager"
.AddItem "Other"
End With
cboResponsible1.Value = ""


When the user selects the relevant job title, it is then inserted into the new sheet e.g.


Private Sub cmdOk_Click()
Sheet3.Activate
Range("B1").Select
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True
ActiveCell.Offset(0, 1) = cboResponsible1.Value
etc


However, as the team build-up is always the same but the individuals are different from project to project I want to look-up the values selected in the combobox and reference to a range that contains the job titles and then individual's names in the adjacent column and populate the cell on the new sheet with an individual's name not the job title.

I have included the job titles on sheet1 cells A2 to B6. The destination of the data will be sheet3 as above.

Can anyone tell me how to build in this range lookup functionality into a combobox? This has been driving me absolutely loopy for days....

: pray2: :banghead: : pray2: :banghead: : pray2: :banghead: : pray2: :banghead:

[OBP]

In Design Mode in the combo box Properties about a third of the way down you have CoulmnCount where you set how many columns you want and about halfway down you have the "ListFillRange" where you can put your range.

[hhnebula]

Thanks for the response OBP. Unfortunately that doesn't seem to be what I am after. I would like to maintain just 1 column within the combobox reflecting the titles of project members, preferably by linking the combobox to the values in column A on sheet1 (as opposed to writing them into the code as above in the first vba box). Once one of the titles is selected, I would like it to pick up the person's name from the adjacent cell (column B, sheet1) when populating sheet3.

[lucas]

This will populate your combobox:
Private Sub UserForm_Initialize()
cboResponsible1.List() = Range([A2], [A2].End(xlDown)).Value
End Sub

[hhnebula]

Thanks Lucas, that worked a treat. Could you tell me how to ignore blanks (sorry I didn't mention that the titles are listed on every other line)?

[lucas]

Try this:

Private Sub UserForm_Initialize()
Dim UniqueList() As String
Dim x As Long
Dim Rng1 As Range
Dim c As Range
Dim Unique As Boolean
Dim y As Long

Set Rng1 = Sheets("Sheet1").Range("A2:A1103")
y = 1

ReDim UniqueList(1 To Rng1.Rows.Count)

For Each c In Rng1
If Not c.Value = vbNullString Then
Unique = True
For x = 1 To y
If UniqueList(x) = c.Text Then
Unique = False
End If
Next
If Unique Then
y = y + 1
Me.cboResponsible1.AddItem (c.Text)
UniqueList(y) = c.Text
End If
End If
Next

End Sub

[hhnebula]

I am getting the following error having inserted the code:

run time error 9
Subscript out of range

I have to run to meetings now so will not be around to check this but thank you very much for your responses and I will play more when I get back. Cheers!

[lucas]

Subscript out of range probably means it can't find something, maybe you don't have a sheet1

Set Rng1 = Sheets("Sheet1").Range("A2:A1103")

[hhnebula]

Thanks again Lucas - it was indeed related to the sheet. I changed the code to the sheet name and it works. Cheers! :bow:

[lucas]

Glad to help. Don't forget to mark your thread solved.