VBA Populate text box from Combobox

Sanderch

New Member
Joined
Mar 5, 2010
Messages
27
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)

Code:
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
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,755
Office Version
  1. 365
Platform
  1. Windows
Try changing your Change event to this
Code:
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,755
Office Version
  1. 365
Platform
  1. Windows
Glad to help & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,122,214
Messages
5,594,876
Members
413,946
Latest member
richelg

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top