using loop to populate data in textboxes and comboboxes after matching

K

KalilMe

Active Member
Joined
Mar 5, 2021
Messages
343
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
 
Sort by date Sort by votes
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:
DanteAmor said:
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.
Click to expand...
 
Upvote 0

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
@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
 
Upvote 0
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
 
Upvote 0
@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
 
Upvote 0
KalilMe said:
strange it deletes data from the sheet what I search for it . it clears cells
Click to expand...
Obviously you must have values in all the controls, if the control has empty, then in the cell it will put empty.
 
Upvote 0
@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
 
Upvote 0
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
 
Upvote 0
@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.
 
Upvote 0
@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
 
Upvote 0
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

www.dropbox.com

KalilMe - pop 1.xlsm

Shared with Dropbox
www.dropbox.com www.dropbox.com
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
User Form Textbox looping
2
Replies
12
Views
582
sjtjr73
S
I
  • Solved
Cell above is selected when i duplicate row
Replies
2
Views
84
ipbr21054
I
Y
  • Question
Sum the total working hours when the Qty Completed become value except 0 automatically
Replies
0
Views
321
yoon
Y
Y
  • Question
Help me find error in this coding
Replies
6
Views
369
Peter_SSs
Peter_SSs
J
  • Question
Listbox not showing in sent email
Replies
1
Views
123
Micron
M

Forum statistics

Threads
1,214,830
Messages
6,121,835
Members
449,051
Latest member
excelquestion515

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