hhnebula
07-20-2006, 05:11 AM
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:
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: