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
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
13,771
Office Version
  1. 2007
Platform
  1. Windows
I did not say that it is impossible, I said that it is bad practice. It seems to me that I have not been able to explain myself.


Just as you put your code at the beginning of the thread it works fine.
Just follow my recommendation:
6. If this is how you need it, that is, 3 records, maybe there is not much to simplify, only after passing the first 6 values, you should increase the row by 1.
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

KalilMe

Board Regular
Joined
Mar 5, 2021
Messages
165
Office Version
  1. 2016
Platform
  1. Windows
@DanteAmor actually I know the code in PO works , but too long . that's why I ask using the loop . your code exactly what I look for but it gives error could not fined the specified object in this line
VBA Code:
   ws.Cells(Rng.Row, i) = Me.Controls("TextBox" & i).Value
this is the whole code based on 3 records
VBA Code:
Private Sub CommandButton1_Click()
  Dim ws As Worksheet
  Dim Rng As Range
  Dim Sel
  Dim i As Long
 
  Set ws = Sheets("sheet1")
  Sel = Me.ComboBox10.Value
  If Sel <> "" Then
    Set Rng = ws.Columns(1).Find(Sel, lookat:=xlWhole)
    If Not Rng Is Nothing Then
      For i = 1 To 9
        ws.Cells(Rng.Row, i) = Me.Controls("TextBox" & i).Value
      Next
     
      For i = 1 To 6
        ws.Cells(Rng.Row, i + 9) = Me.Controls("ComboBox" & i).Value
      Next
    Else
      For i = 1 To 9
        Me.Controls("TextBox" & i).Value = ""
      Next
      '
      For i = 1 To 6
        Me.Controls("ComboBox" & i).Value = ""
      Next
    End If
  End If
End Sub
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
13,771
Office Version
  1. 2007
Platform
  1. Windows
My code is not going to work for what you have.
You must use your code.
Just check that it is correct.

Rich (BB code):
'first block
   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, "D") = Me.ComboBox1.Value
   ws.Cells(Rng.Row, "E") = Me.ComboBox2.Value
    ws.Cells(Rng.Row, "F") = Me.CoMBoBoX3.Value

'second block
   ws.Cells(Rng.Row + 1, "A") = Me.TextBox4Value  'Missing a point .Value
  ws.Cells(Rng.Row + 1, "B") = Me.TextBox5Value
  ws.Cells(Rng.Row + 1, "C") = Me.TextBox6Value
    ws.Cells(Rng.Row + 1, "D") = Me.ComboBox4.Value
   ws.Cells(Rng.Row + 1, "E") = Me.ComboBox5.Value
    ws.Cells(Rng.Row + 1, "F") = Me.CoMBoBoX6.Value

'third block
   ws.Cells(Rng.Row + 2, "A") = Me.TextBox7Value
  ws.Cells(Rng.Row + 2, "B") = Me.TextBox8Value
  ws.Cells(Rng.Row + 2, "C") = Me.TextBox9.Value
    ws.Cells(Rng.Row + 2, "D") = Me.ComboBox7.Value
   ws.Cells(Rng.Row + 2, "E") = Me.ComboBox8.Value
    ws.Cells(Rng.Row + 2, "F") = Me.CoMBoBoX9.Value
 

KalilMe

Board Regular
Joined
Mar 5, 2021
Messages
165
Office Version
  1. 2016
Platform
  1. Windows
@DanteAmor
strange it deletes data from the sheet what I search for it . it clears cells :eek:

this is the code what I use it
VBA Code:
Private Sub CommandButton1_Click()
Dim ws As Worksheet
Dim Rng As Range
Dim Sel
Set ws = Sheets("sheet1")
Sel = Me.ComboBox10.Value
If Sel <> "" Then
 Set Rng = ws.Columns(1).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, "D") = Me.ComboBox1.Value
   ws.Cells(Rng.Row, "E") = Me.ComboBox2.Value
    ws.Cells(Rng.Row, "F") = Me.ComboBox3.Value

'second block
   ws.Cells(Rng.Row + 1, "A") = Me.TextBox4.Value  'Missing a point .Value
  ws.Cells(Rng.Row + 1, "B") = Me.TextBox5.Value
  ws.Cells(Rng.Row + 1, "C") = Me.TextBox6.Value
    ws.Cells(Rng.Row + 1, "D") = Me.ComboBox4.Value
   ws.Cells(Rng.Row + 1, "E") = Me.ComboBox5.Value
    ws.Cells(Rng.Row + 1, "F") = Me.ComboBox6.Value

'third block
   ws.Cells(Rng.Row + 2, "A") = Me.TextBox7.Value
  ws.Cells(Rng.Row + 2, "B") = Me.TextBox8.Value
  ws.Cells(Rng.Row + 2, "C") = Me.TextBox9.Value
    ws.Cells(Rng.Row + 2, "D") = Me.ComboBox7.Value
   ws.Cells(Rng.Row + 2, "E") = Me.ComboBox8.Value
    ws.Cells(Rng.Row + 2, "F") = Me.ComboBox9.Value
    End If
    End If
End Sub
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
13,771
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

strange it deletes data from the sheet what I search for it . it clears cells
Obviously you must have values in all the controls, if the control has empty, then in the cell it will put empty.
 

KalilMe

Board Regular
Joined
Mar 5, 2021
Messages
165
Office Version
  1. 2016
Platform
  1. Windows
@Dante sorry and my apologies even my code is in OP is wrong . I put copy instead of the search


this code should populate data in all tools when search for item in col A
VBA Code:
Private Sub CommandButton1_Click()

Dim ws As Worksheet
Dim Rng As Range
Dim Sel
Set ws = Sheets("sheet1")
Sel = Me.ComboBox10.Value
If Sel <> "" Then
 Set Rng = ws.Columns(4).Find(Sel, lookat:=xlWhole)
 If Not Rng Is Nothing Then
  Me.TextBox1.Value = ws.Cells(Rng.Row, "A")
  Me.TextBox2.Value = ws.Cells(Rng.Row, "B")
  Me.TextBox3.Value = ws.Cells(Rng.Row, "C")
  Me.ComboBox1.Value = ws.Cells(Rng.Row, "D")
  Me.ComboBox2.Value = ws.Cells(Rng.Row, "E")
  Me.ComboBox3.Value = ws.Cells(Rng.Row, "F")

'second block
   Me.TextBox4.Value = ws.Cells(Rng.Row, "A")   'Missing a point .Value
  Me.TextBox5.Value = ws.Cells(Rng.Row, "B")
   Me.TextBox6.Value = ws.Cells(Rng.Row, "C")
   Me.ComboBox4.Value = ws.Cells(Rng.Row, "D")
   Me.ComboBox5.Value = ws.Cells(Rng.Row, "E")
   Me.ComboBox6.Value = ws.Cells(Rng.Row, "F")

'third block
 Me.TextBox7.Value = ws.Cells(Rng.Row, "A")
 Me.TextBox8.Value = ws.Cells(Rng.Row, "B")
 Me.TextBox9.Value = ws.Cells(Rng.Row, "C")
 Me.ComboBox7.Value = ws.Cells(Rng.Row, "D")
 Me.ComboBox8.Value = ws.Cells(Rng.Row, "E")
 Me.ComboBox9.Value = ws.Cells(Rng.Row, "F")
    End If
    End If
    End Sub

. now it populate but not like what I want . it should show in first record on userform but it repeates for all tools how should solve this problem

I put random data in sheet . see the picture it repeates the data in all records on userform
1.PNG
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
13,771
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

You forgot +1 and +2

VBA Code:
Private Sub CommandButton1_Click()
  Dim ws As Worksheet
  Dim Rng As Range
  Dim Sel
  Set ws = Sheets("sheet1")
  Sel = Me.ComboBox10.Value
  If Sel <> "" Then
    Set Rng = ws.Columns(4).Find(Sel, lookat:=xlWhole)
    If Not Rng Is Nothing Then
      Me.TextBox1.Value = ws.Cells(Rng.Row, "A")
      Me.TextBox2.Value = ws.Cells(Rng.Row, "B")
      Me.TextBox3.Value = ws.Cells(Rng.Row, "C")
      Me.ComboBox1.Value = ws.Cells(Rng.Row, "D")
      Me.ComboBox2.Value = ws.Cells(Rng.Row, "E")
      Me.ComboBox3.Value = ws.Cells(Rng.Row, "F")
      
      'second block
      Me.TextBox4.Value = ws.Cells(Rng.Row + 1, "A")
      Me.TextBox5.Value = ws.Cells(Rng.Row + 1, "B")
      Me.TextBox6.Value = ws.Cells(Rng.Row + 1, "C")
      Me.ComboBox4.Value = ws.Cells(Rng.Row + 1, "D")
      Me.ComboBox5.Value = ws.Cells(Rng.Row + 1, "E")
      Me.ComboBox6.Value = ws.Cells(Rng.Row + 1, "F")
      
      'third block
      Me.TextBox7.Value = ws.Cells(Rng.Row + 2, "A")
      Me.TextBox8.Value = ws.Cells(Rng.Row + 2, "B")
      Me.TextBox9.Value = ws.Cells(Rng.Row + 2, "C")
      Me.ComboBox7.Value = ws.Cells(Rng.Row + 2, "D")
      Me.ComboBox8.Value = ws.Cells(Rng.Row + 2, "E")
      Me.ComboBox9.Value = ws.Cells(Rng.Row + 2, "F")
    End If
  End If
End Sub
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
3,465
Office Version
  1. 365
Platform
  1. Windows
@KalilMe
Could you upload a sample workbook along with some data & the useform to a sharing site like dropbox.com or google drive?
My idea is to put all controls (textbox+combobox) name to an array, then use that array to refer to the control when needed. So it will be easier to deal with various number of invoice.
If you can provide a sample workbook I'll try to set it up.
 

KalilMe

Board Regular
Joined
Mar 5, 2021
Messages
165
Office Version
  1. 2016
Platform
  1. Windows
@Akuini thanks for your following my request . so I put many cases in sheet CASE how should search based in column A and combobox10 and about repeating textbox(customer,inv) I remembered I can't change as you suggest because I have another macro depends on shape of this structure . I hope understand me .

so when select INV NO from combobox10 should populate for the tools based on how many records conain in sheet .
pop.xlsm
thanks for your cooperation
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
3,465
Office Version
  1. 365
Platform
  1. Windows
Ok, try this:
1. You need to set this part:
Private Const nSet As Long = 3
nSet is how many set or "rows" of textbox & combobox you have in the the userform, in this example is 3.
2. I changed combobox10 to ComboboxSearch, so if you need, you can add more combobox without breaking the number sequence.

VBA Code:
Option Explicit
Private Const nSet As Long = 3
Dim ary
Dim ws As Worksheet

Private Sub UserForm_Initialize()
Dim i As Long, j As Long, k As Long
Dim va
Dim d As Object

ReDim ary(1 To nSet * 6)

'populate textbox & combobox names to array
For i = 1 To UBound(ary) Step 6
    For j = 0 To 2
        k = k + 1
        ary(i + j) = "TextBox" & k
        ary(i + j + 3) = "ComboBox" & k
    Next
Next

Set ws = Sheets("Sheet1")
With ws.Cells(1).CurrentRegion
    .Sort Key1:=.Cells(1), Order1:=xlAscending, Header:=xlYes
    va = .Columns(1).Value
End With

Set d = CreateObject("scripting.dictionary"): d.CompareMode = vbTextCompare
    For i = 2 To UBound(va, 1)
        d(va(i, 1)) = Empty
    Next

'populate ComboBoxSearch with unique values from column 1
Me.ComboBoxSearch.RowSource = Empty
Me.ComboBoxSearch.List = d.keys

End Sub


Private Sub ComboBoxSearch_Change()
Dim i As Long, j As Long, m As Long
    'clear all controls
    For j = 1 To nSet
          For i = 1 To 6
              Me.Controls(ary(i + m)).Value = Empty
          Next
          m = m + 6
    Next

    Call toPopulate
End Sub

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

 

Forum statistics

Threads
1,144,281
Messages
5,723,473
Members
422,499
Latest member
think say

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