Combo Box selection to populate Form

[HDfatboy03]

Hello

I am working on a form where when a selection is made from a combo box the next box is populated automatically from information entered earlier in a worksheet (GolferDB). Specifically when the last name is selected from a combo box the first name appears in the next combo box automatically.

Below is the code that I have currently. It is working with 2 combo boxes but the First name is not tied into the Last name selection. I feel it would be more user friendly if it would populate automatically.

I'm very new at this any help would be greatly appreciated.


Private Sub UserForm_Initialize()
Dim cPart As Range
Dim cLoc As Range
Dim ws As Worksheet
Set ws = Worksheets("GolferDB")
For Each cLoc In ws.Range("LastName")
With Me.cboLocation
.AddItem cLoc.Value
End With
Next cLoc
For Each cPart In ws.Range("FirstName")
With Me.cbopart
.AddItem cPart.Value
.List(.ListCount - 1, 1) = cPart.Offset(0, 1).Value
End With
Next cPart
Me.cbopart.SetFocus
End Sub

Thanks you in advance.

Bob

 

[Stephen_Tsui]

Try Follow Code

Private Sub cboLocation_Change()

Dim MatchFirstName As Integer
Dim ws As Worksheet

Set ws = Worksheets("GolferDB")
On Error Resume Next
MatchFirstName = WorksheetFunction.Match(Me.cboLocation, ws.Range("LastName"), 0)
If Err = 0 Then
    Me.cbopart.Value = WorksheetFunction.Index(ws.Range("FirstName"), MatchFirstName)
Else:
    Me.cbopart.Value = ""
End If
On Error GoTo 0

End Sub

Private Sub UserForm_Initialize()

Dim cPart As Range
Dim cLoc As Range
Dim ws As Worksheet

Set ws = Worksheets("GolferDB")

For Each cLoc In ws.Range("LastName")
    With Me.cboLocation
        .AddItem cLoc.Value
    End With
Next cLoc

For Each cPart In ws.Range("FirstName")
    With Me.cbopart
        .AddItem cPart.Value
        .List(.ListCount - 1, 1) = cPart.Offset(0, 1).Value
    End With
Next cPart

Me.cbopart.SetFocus

End Sub

Sample Data

Excel Workbook
	A	B
1	LastName	FirstName
2	AB	CC
3	CD	DD
4	EF	EE
5	GH	FF
6	IJ	GG
7	KL	HH
8	MN	II
9	OP	JJ
GolferDB