VBA Populate text box from Combobox

[Sanderch]

Hi all, I haven't posted on the site for a long time so how everyone is well.

I have a basic userform that uses a combobox to return and populate some text boxes. the combobox returns the name of a client as and when they have contacted me. the problem has accrued when adding multiple contacts from the same client.

As the I am usung the xlUP function it is looking for last cell from bottom and when I come across a duplicated name it only displays the last entry data even if I select the first entry within the combobox.

im ok with VBA but not advanced so am stuck as to how I can get around this.

The code I am using is (Column C has the list of Client Names)

Private Sub UserForm_Initialize()
Dim i As Long, LastRow As Long, ws As Worksheet
Set ws = Sheets("Sheet3")
LastRow = ws.Range("C" & Rows.Count).End(xlUp).Row
For i = 2 To LastRow
Me.ComboBox1.AddItem ws.Cells(i, "C").Value
Next i
End Sub

Private Sub ComboBox1_Change()
Dim i As Long, LastRow As Long, ws As Worksheet
Set ws = Sheets("Sheet3")
LastRow = ws.Range("C" & Rows.Count).End(xlUp).Row
For i = 2 To LastRow
'If Val(Me.ComboBox1.Value) = ws.Cells(i, "C") Then = Value
If (Me.ComboBox1.Value) = ws.Cells(i, "C") Then
Me.TextBox1 = ws.Cells(i, "A").Value
Me.TextBox2 = ws.Cells(i, "B").Value
Me.TextBox3 = ws.Cells(i, "D").Value
Me.TextBox4 = ws.Cells(i, "E").Value
Me.TextBox5 = ws.Cells(i, "F").Value
Me.TextBox6 = ws.Cells(i, "G").Value
End If
Next i
End Sub

any help would be greatly received.

Regards

Chris

 

[Fluff]

Try changing your Change event to this

Private Sub ComboBox1_Change()
   Dim i As Long, ws As Worksheet
   Set ws = Sheets("Sheet3")
   i = ComboBox1.ListIndex + 2
   Me.TextBox1 = ws.Cells(i, "A").Value
   Me.textbox2 = ws.Cells(i, "B").Value
   Me.textbox3 = ws.Cells(i, "D").Value
   Me.TextBox4 = ws.Cells(i, "E").Value
   Me.TextBox5 = ws.Cells(i, "F").Value
   Me.TextBox6 = ws.Cells(i, "G").Value
End Sub

 

[Sanderch]

Easy when you know how - Thanks Fluff...

 

[Fluff]

Glad to help & thanks for the feedback