using loop to populate data in textboxes and comboboxes after matching

KalilMe

Board Regular
Joined
Mar 5, 2021
Messages
187
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
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

rlv01

Well-known Member
Joined
May 16, 2017
Messages
1,160
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
One way to reduce the code is to remove the lines that serve no purpose. I appears that most of the TextBox and ComboBox values do not matter because they are being written to cells that are then immediately overwritten by other TextBox and ComboBox values.

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

So in theory you could just delete those lines of code.

VBA Code:
    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.TextBox7.Value
             ws.Cells(Rng.Row, "B") = Me.TextBox8.Value
             ws.Cells(Rng.Row, "C") = Me.TextBox9.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
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
13,994
Office Version
  1. 2007
Platform
  1. Windows
But what you put in cells A, B and C
VBA Code:
   ws.Cells(Rng.Row, "A") = Me.[B]TextBox1[/B].Value
  ws.Cells(Rng.Row, "B") = Me.TextBox2.Value
  ws.Cells(Rng.Row, "C") = Me.TextBox3.Value

It is then eliminated by the following controls:
VBA Code:
   ws.Cells(Rng.Row, "A") = Me.[B]TextBox4[/B]Value
  ws.Cells(Rng.Row, "B") = Me.TextBox5Value
  ws.Cells(Rng.Row, "C") = Me.TextBox6Value


But I show you a way to clear the controls:
VBA Code:
    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
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
18,164
Office Version
  1. 2013
Platform
  1. Windows
Another way to clear all those controls is to Unload the entire Userform if that is what your using.
 

KalilMe

Board Regular
Joined
Mar 5, 2021
Messages
187
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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
@DanteAmor how implement your idea in my code . actually I want use loop to copy the sheet so , but I have no enough knowladge in vba . may you guide me please?
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
13,994
Office Version
  1. 2007
Platform
  1. Windows
actually I want use loop to copy the sheet so , but I have no enough knowladge in vba . may you guide me please?

But your code is not clear, you are overlapping values in the same cells.
Assuming that you are going to put values from column A to O, one column for each control, then it could be like this:

The following is an example.
Try to adapt it to your need. If you have problems, then come back here and explain in your words which control goes in which column.
You can also use the XL2BB tool minisheet to show what goes on your sheet.

VBA Code:
Private Sub CommandButton1_Click()
  Dim ws As Worksheet
  Dim Rng As Range
  Dim Sel
  Dim i As Long
  
  Set ws = Sheets("first")
  Sel = Me.ComboBox1.Value
  If Sel <> "" Then
    Set Rng = ws.Columns(10).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
 

KalilMe

Board Regular
Joined
Mar 5, 2021
Messages
187
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

@DanteAmor actually it doesn't show any thing . it doesn't seem I can deal with the code .so the textboxes linked with col A,B,C and the comboboxes link with COL D,E,F
the combobox 10 is search I put the item based on COL A . the search based on INV.NO
see the picture
1.PNG
 

rlv01

Well-known Member
Joined
May 16, 2017
Messages
1,160
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
but I have no enough knowladge in vba . may you guide me please?
With respect to the code you posted, did you write it yourself, or is it code you obtained from others?
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
13,994
Office Version
  1. 2007
Platform
  1. Windows
Now I have more questions than answers.

1. In your macro you look in column 4.
But now the values are in column 1?
Rich (BB code):
Set Rng = ws.Columns(4).Find(Sel, lookat:=xlWhole)

2. I guess you must have a reason to save 3 records, why 3 records?

3. Is it always 3 records?

4. So it's 9 textbox1 and 9 combobox?

5. If the value of combobox1 is equal to the value of textboxes 1, 4 and 7, then it is not necessary to capture the values in textboxes 1, 4 and 7, is it?
1633642601868.png

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.
Then the cycle would only apply in the way of cleaning the controls.

Comment and I help you with the final code to pass the values to the sheet.
 

Forum statistics

Threads
1,148,423
Messages
5,746,596
Members
424,032
Latest member
pochie2741

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