using loop to populate data in textboxes and comboboxes after matching

KalilMe

Board Regular
Joined
Mar 5, 2021
Messages
165
Office Version
  1. 2016
Platform
  1. Windows
hello
I have this code . it populates values in textboxes and comboboxes after matching with sheets in COLS A,B,C,D,E,F) so what I want using loop to make the code is short
this is the code
VBA Code:
Private Sub CommandButton1_Click()
Dim ws As Worksheet
Dim Rng As Range
Dim Sel
Set ws = Sheets("first")
Sel = Me.ComboBox1.Value
If Sel <> "" Then
 Set Rng = ws.Columns(4).Find(Sel, lookat:=xlWhole)
 If Not Rng Is Nothing Then
   ws.Cells(Rng.Row, "A") = Me.TextBox1.Value
  ws.Cells(Rng.Row, "B") = Me.TextBox2.Value
  ws.Cells(Rng.Row, "C") = Me.TextBox3.Value
   ws.Cells(Rng.Row, "A") = Me.TextBox4Value
  ws.Cells(Rng.Row, "B") = Me.TextBox5Value
  ws.Cells(Rng.Row, "C") = Me.TextBox6Value
   ws.Cells(Rng.Row, "A") = Me.TextBox7Value
  ws.Cells(Rng.Row, "B") = Me.TextBox8Value
  ws.Cells(Rng.Row, "C") = Me.TextBox9.Value
  ws.Cells(Rng.Row, "D") = Me.ComboBox1.Value
   ws.Cells(Rng.Row, "E") = Me.ComboBox2.Value
    ws.Cells(Rng.Row, "F") = Me.CoMBoBoX3.Value
    ws.Cells(Rng.Row, "D") = Me.ComboBox4.Value
   ws.Cells(Rng.Row, "E") = Me.ComboBox5.Value
    ws.Cells(Rng.Row, "F") = Me.CoMBoBoX6.Value
 
 
 Else
 Me.TextBox1.Value = ""
 Me.TextBox2.Value = ""
 Me.TextBox3.Value = ""
 Me.TextBox4.Value ""
 Me.TextBox5.Value = ""
 Me.TextBox6.Value = ""
 Me.TextBox7.Value = ""
 Me.TextBox8.Value = ""
 Me.TextBox9.Value = ""
 Me.ComboBox1.Value = ""
 Me.ComboBox2.Value = ""
  Me.CoMBoboX3.Value = ""
  Me.ComboBox4.Value = ""
  Me.ComboBox5.Value = ""
   Me.CoMBoBoX6.Value = ""
 
 
 End If
End If
End Sub
 

KalilMe

Board Regular
Joined
Mar 5, 2021
Messages
165
Office Version
  1. 2016
Platform
  1. Windows
brilliant ! your super professional.:)
thanks for inform me how should mod if i need it .
you're legend (y)
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
3,439
Office Version
  1. 365
Platform
  1. Windows
@KalilMe
Sorry, I forgot something, "Sub toPopulate" relies on sheet data being sorted by col A. I've put the sort part in "Sub UserForm_Initialize", but if you edit, insert or delete some data (via Userform) then it might no longer be sorted as needed . So it's best to sort the data in "Sub toPopulate" too, just add the blue line in this part:

Rich (BB code):
Sub toPopulate()

  Dim c As Range
  Dim tx As String
  Dim i As Long, j As Long, k As Long, m As Long
 
    tx = Me.ComboBoxSearch.Value
        ws.Cells(1).CurrentRegion.Sort Key1:=ws.Cells(1), Order1:=xlAscending, Header:=xlYes
       
        Set c = ws.Columns(1).Find(What:=tx, LookIn:=xlValues, lookAt:=xlWhole, _
        SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
        If Not c Is Nothing Then
        k = WorksheetFunction.CountIf(ws.Columns(1), tx)
        If k > nSet Then MsgBox "Too many invoices. You need more textbox & combobox": Exit Sub
          For j = 1 To k
                For i = 1 To 6
                    Me.Controls(ary(i + m)).Value = c.Offset(j - 1, i - 1).Value
                Next
                m = m + 6
          Next
        End If
End Sub
 
Solution

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
3,439
Office Version
  1. 365
Platform
  1. Windows
You're welcome, glad to help & thanks for the feedback.:)
 

Forum statistics

Threads
1,143,690
Messages
5,720,311
Members
422,275
Latest member
Maria95

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