VBA : Create loop to update Multiple Textboxes based on ComboBox Selection

K

KMGIL

New Member
Joined
Dec 16, 2014
Messages
26
Hi
I am trying to update multiple textbox based on selection in a combo box
The Combox section is based on an email address . This email address could appear multiple times in the data base and I need to return data in the Textboxes for each line.

I have created a form and am able to update the first row of textboxes but am not sure how I can loop I to update the second row. Also when I click on the drop down option of the ComboBox I get to see the multiple times the email name I there

Please see below my initial code and expel of what database looks like

Appreciate any help

Many Thanks

Kind Regards

Ken

Private Sub ComboBox1_Change()
Dim i As Long, LastRow As Long, ws As Worksheet
Set ws = Sheets("Sheet1")

LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To LastRow
If (Me.ComboBox1.Value) = ws.Cells(i, "A") Then
Me.TextBox1 = ws.Cells(i, "C").Value
Me.TextBox2 = ws.Cells(i, "D").Value
Me.TextBox3 = ws.Cells(i, "F").Value
Me.TextBox4 = ws.Cells(i, "J").Value

End If
Next i
End Sub

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





[TABLE="width: 949"]
<colgroup><col width="170" style="width: 128pt; mso-width-source: userset; mso-width-alt: 6217;"> <col width="100" style="width: 75pt; mso-width-source: userset; mso-width-alt: 3657;"> <col width="79" style="width: 59pt; mso-width-source: userset; mso-width-alt: 2889;"> <col width="54" style="width: 41pt; mso-width-source: userset; mso-width-alt: 1974;"> <col width="148" style="width: 111pt; mso-width-source: userset; mso-width-alt: 5412;"> <col width="53" style="width: 40pt; mso-width-source: userset; mso-width-alt: 1938;"> <col width="201" style="width: 151pt; mso-width-source: userset; mso-width-alt: 7350;"> <col width="110" style="width: 83pt; mso-width-source: userset; mso-width-alt: 4022;"> <col width="62" style="width: 47pt; mso-width-source: userset; mso-width-alt: 2267;"> <col width="131" style="width: 98pt; mso-width-source: userset; mso-width-alt: 4790;"> <col width="154" style="width: 116pt; mso-width-source: userset; mso-width-alt: 5632;"> <tbody>[TR]
[TD="width: 170, bgcolor: #DCE6F1"]Email Address[/TD]
[TD="width: 100, bgcolor: #DCE6F1"]Operating Unit[/TD]
[TD="width: 79, bgcolor: #DCE6F1"]PO Number[/TD]
[TD="width: 54, bgcolor: #DCE6F1"]PO Line[/TD]
[TD="width: 148, bgcolor: #DCE6F1"]PO Preparer[/TD]
[TD="width: 53, bgcolor: #DCE6F1"]Vendor[/TD]
[TD="width: 201, bgcolor: #DCE6F1"]Deliver to Person[/TD]
[TD="width: 110, bgcolor: #DCE6F1"]CREATION_DATE[/TD]
[TD="width: 62, bgcolor: #DCE6F1"]Currency[/TD]
[TD="width: 131, bgcolor: #DCE6F1"] Accounted Amount[/TD]
[TD="width: 154, bgcolor: #DCE6F1"]Comment[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]km@abc.com[/TD]
[TD="bgcolor: transparent"]ES-OU-01[/TD]
[TD="bgcolor: transparent"]1234[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]ken[/TD]
[TD="bgcolor: transparent"]Abc[/TD]
[TD="bgcolor: transparent"]ken[/TD]
[TD="bgcolor: transparent"]06-Sep-18[/TD]
[TD="bgcolor: transparent"]EUR[/TD]
[TD="bgcolor: transparent"]39.34 [/TD]
[TD="bgcolor: transparent"]Q1.19 RNV - Low values[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]sb@abc.com[/TD]
[TD="bgcolor: transparent"]FR-OU-01[/TD]
[TD="bgcolor: transparent"]1235[/TD]
[TD="bgcolor: transparent"]2[/TD]
[TD="bgcolor: transparent"]Simon[/TD]
[TD="bgcolor: transparent"]Def[/TD]
[TD="bgcolor: transparent"]Simon[/TD]
[TD="bgcolor: transparent"]29-Aug-14[/TD]
[TD="bgcolor: transparent"]EUR[/TD]
[TD="bgcolor: transparent"]42.00 [/TD]
[TD="bgcolor: transparent"]Q1.19 RNV - Low values[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Jmc@abc.com[/TD]
[TD="bgcolor: transparent"]FR-OU-01[/TD]
[TD="bgcolor: transparent"]3265[/TD]
[TD="bgcolor: transparent"]4[/TD]
[TD="bgcolor: transparent"]Jo[/TD]
[TD="bgcolor: transparent"]GHI[/TD]
[TD="bgcolor: transparent"]Jo[/TD]
[TD="bgcolor: transparent"]19-Oct-18[/TD]
[TD="bgcolor: transparent"]EUR[/TD]
[TD="bgcolor: transparent"]0.62 [/TD]
[TD="bgcolor: transparent"]Q1.19 RNV - Low values[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]km@abc.com[/TD]
[TD="bgcolor: transparent"]ES-OU-01[/TD]
[TD="bgcolor: transparent"]4578[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]ken[/TD]
[TD="bgcolor: transparent"]Abc[/TD]
[TD="bgcolor: transparent"]ken[/TD]
[TD="bgcolor: transparent"]06-Sep-18[/TD]
[TD="bgcolor: transparent"]EUR[/TD]
[TD="bgcolor: transparent"]39.34 [/TD]
[TD="bgcolor: transparent"]Q1.19 RNV - Low values[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]sb@abc.com[/TD]
[TD="bgcolor: transparent"]FR-OU-01[/TD]
[TD="bgcolor: transparent"]1245[/TD]
[TD="bgcolor: transparent"]2[/TD]
[TD="bgcolor: transparent"]Simon[/TD]
[TD="bgcolor: transparent"]Def1[/TD]
[TD="bgcolor: transparent"]Simon[/TD]
[TD="bgcolor: transparent"]29-Aug-14[/TD]
[TD="bgcolor: transparent"]EUR[/TD]
[TD="bgcolor: transparent"]25.00 [/TD]
[TD="bgcolor: transparent"]Q1.19 RNV - Low values[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Jmc@abc.com[/TD]
[TD="bgcolor: transparent"]FR-OU-01[/TD]
[TD="bgcolor: transparent"]2547[/TD]
[TD="bgcolor: transparent"]4[/TD]
[TD="bgcolor: transparent"]Jo[/TD]
[TD="bgcolor: transparent"]GHI[/TD]
[TD="bgcolor: transparent"]Jo[/TD]
[TD="bgcolor: transparent"]19-Oct-18[/TD]
[TD="bgcolor: transparent"]EUR[/TD]
[TD="bgcolor: transparent"]15.64 [/TD]
[TD="bgcolor: transparent"]Q1.19 RNV - Low values[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]km@abc.com[/TD]
[TD="bgcolor: transparent"]ES-OU-01[/TD]
[TD="bgcolor: transparent"]87546[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]ken[/TD]
[TD="bgcolor: transparent"]Abc[/TD]
[TD="bgcolor: transparent"]ken[/TD]
[TD="bgcolor: transparent"]06-Sep-18[/TD]
[TD="bgcolor: transparent"]EUR[/TD]
[TD="bgcolor: transparent"]1,478.00 [/TD]
[TD="bgcolor: transparent"]Q1.19 RNV - Low values[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]sb@abc.com[/TD]
[TD="bgcolor: transparent"]FR-OU-01[/TD]
[TD="bgcolor: transparent"]22223[/TD]
[TD="bgcolor: transparent"]2[/TD]
[TD="bgcolor: transparent"]Simon[/TD]
[TD="bgcolor: transparent"]Def[/TD]
[TD="bgcolor: transparent"]Simon[/TD]
[TD="bgcolor: transparent"]29-Aug-14[/TD]
[TD="bgcolor: transparent"]EUR[/TD]
[TD="bgcolor: transparent"]25.00 [/TD]
[TD="bgcolor: transparent"]Q1.19 RNV - Low values[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Jmc@abc.com[/TD]
[TD="bgcolor: transparent"]FR-OU-01[/TD]
[TD="bgcolor: transparent"]2248[/TD]
[TD="bgcolor: transparent"]4[/TD]
[TD="bgcolor: transparent"]Jo[/TD]
[TD="bgcolor: transparent"]GHI2[/TD]
[TD="bgcolor: transparent"]Jo[/TD]
[TD="bgcolor: transparent"]19-Oct-18[/TD]
[TD="bgcolor: transparent"]EUR[/TD]
[TD="bgcolor: transparent"]15.00 [/TD]
[TD="bgcolor: transparent"]Q1.19 RNV - Low values[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]km@abc.com[/TD]
[TD="bgcolor: transparent"]ES-OU-01[/TD]
[TD="bgcolor: transparent"]2258[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]ken[/TD]
[TD="bgcolor: transparent"]Abc[/TD]
[TD="bgcolor: transparent"]ken[/TD]
[TD="bgcolor: transparent"]06-Sep-18[/TD]
[TD="bgcolor: transparent"]EUR[/TD]
[TD="bgcolor: transparent"]37.50 [/TD]
[TD="bgcolor: transparent"]Q1.19 RNV - Low values[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]sb@abc.com[/TD]
[TD="bgcolor: transparent"]FR-OU-01[/TD]
[TD="bgcolor: transparent"]95687[/TD]
[TD="bgcolor: transparent"]2[/TD]
[TD="bgcolor: transparent"]Simon[/TD]
[TD="bgcolor: transparent"]Def[/TD]
[TD="bgcolor: transparent"]Simon[/TD]
[TD="bgcolor: transparent"]29-Aug-14[/TD]
[TD="bgcolor: transparent"]EUR[/TD]
[TD="bgcolor: transparent"]22.00 [/TD]
[TD="bgcolor: transparent"]Q1.19 RNV - Low values[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Jmc@abc.com[/TD]
[TD="bgcolor: transparent"]FR-OU-01[/TD]
[TD="bgcolor: transparent"]1245[/TD]
[TD="bgcolor: transparent"]4[/TD]
[TD="bgcolor: transparent"]Jo[/TD]
[TD="bgcolor: transparent"]GHI[/TD]
[TD="bgcolor: transparent"]Jo[/TD]
[TD="bgcolor: transparent"]19-Oct-18[/TD]
[TD="bgcolor: transparent"]EUR[/TD]
[TD="bgcolor: transparent"]13.48 [/TD]
[TD="bgcolor: transparent"]Q1.19 RNV - Low values[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]km@abc.com[/TD]
[TD="bgcolor: transparent"]ES-OU-01[/TD]
[TD="bgcolor: transparent"]11111[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]ken[/TD]
[TD="bgcolor: transparent"]Abc3[/TD]
[TD="bgcolor: transparent"]ken[/TD]
[TD="bgcolor: transparent"]06-Sep-18[/TD]
[TD="bgcolor: transparent"]EUR[/TD]
[TD="bgcolor: transparent"]7.25 [/TD]
[TD="bgcolor: transparent"]Q1.19 RNV - Low values[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]sb@abc.com[/TD]
[TD="bgcolor: transparent"]FR-OU-01[/TD]
[TD="bgcolor: transparent"]3333[/TD]
[TD="bgcolor: transparent"]2[/TD]
[TD="bgcolor: transparent"]Simon[/TD]
[TD="bgcolor: transparent"]Def[/TD]
[TD="bgcolor: transparent"]Simon[/TD]
[TD="bgcolor: transparent"]29-Aug-14[/TD]
[TD="bgcolor: transparent"]EUR[/TD]
[TD="bgcolor: transparent"]6.00 [/TD]
[TD="bgcolor: transparent"]Q1.19 RNV - Low values[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Jmc@abc.com[/TD]
[TD="bgcolor: transparent"]FR-OU-01[/TD]
[TD="bgcolor: transparent"]66587[/TD]
[TD="bgcolor: transparent"]4[/TD]
[TD="bgcolor: transparent"]Jo[/TD]
[TD="bgcolor: transparent"]GHI[/TD]
[TD="bgcolor: transparent"]Jo[/TD]
[TD="bgcolor: transparent"]19-Oct-18[/TD]
[TD="bgcolor: transparent"]EUR[/TD]
[TD="bgcolor: transparent"]19.68 [/TD]
[TD="bgcolor: transparent"]Q1.19 RNV - Low values[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
You must log in or register to reply here.

Similar threads

C
  • Question Question
SUMIF - Based On Unique Values and Multiple Criteria
2 3
Replies
25
Views
11K
bronxjm
B
H
  • Question Question
Top 10 Values based on couple of conditions
Replies
8
Views
852
harinsh
H
S
  • Question Question
Formula or VBA to Evaluating Data based on Material #, Location then Date
Replies
3
Views
677
DanteAmor
DanteAmor
V
  • Question Question
Deplete cell values based on a common reference 2
Replies
1
Views
1K
victoria2207
V
M
  • Question Question
Tricky one - finding the last entry for a specific cell and returning a corresponding value.
Replies
1
Views
670
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,223,886
Messages
6,175,196
Members
452,616
Latest member
intern444

Share this page

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
Back
Top