using loop to populate data in textboxes and comboboxes after matching

[KalilMe]

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

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

 

[rlv01]

Yes, because if it is, then you may not be able to explain to us what it is trying to accomplish. As others have noted, there are things about it that do not make any sense.

 

[KalilMe]

1. In your macro you look in column 4.
But now the values are in column 1?

yes in column 1 and sorry about mod this part

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

actually no resason just to understand how should work somtimes two records or four .if it's four then I have to add more textboxes , comboboxes
just I want how should show the data . this is structure invoice . the aim of that to I can adjust the invoice after populate
so if the invoice just show one record then loop(texbox) 1 to 3 and the same thing comboboxes , and if invoice shows two records then loop(textboxes) , comboboxes 1 to 6

3. Is it always 3 records?

as I said in Q2

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

yes

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?

I agree wit you . if you see this is not problem should show like the others .

 

[DanteAmor]

That's where you should start, is this for the records of an invoice?
So if the invoice has 100 records, you would need 100 textbox.
Let's start over, what is the end goal?
Forget your userform for a moment. How are you going to store the data on the sheet.
Do you only have one sheet? On that sheet do you have all your invoices?

 

[KalilMe]

Do you only have one sheet? On that sheet do you have all your invoices?

yes in one sheet .

 

[Akuini]

@KalilMe
I agree with @DanteAmor, you should rethink your approach. Perhaps just using 3 textboxes + 3 combobox + 1 listbox would be a better choice. I remember answering a similar problem in this thread:

Userform textboxs autofill

I have a UserForm2 with 3 textbox columns. 'Claim Check, City, Employee Int'. Is there a way to make the City and Employee textboxes auto populate with the City and Employee that was entered right above it? Claim Check textbox (Name) starts at c1 the one below it is c2 and so on. City textbox...

www.mrexcel.com

the OP was using more than 60 textbox for data entry, and I suggested to use just some of it (12 textbox) + a listbox.
See if similar approach can be applied on your case.

 

[DanteAmor]

yes in one sheet .

But you need to show us how you are going to store the data on the sheet.
You could put an image with more information, 3 or 4 invoices with different records each. You can also use the XL2BB tool minisheet.

 

[KalilMe]

@DanteAmor this is three cases
if there are three records

2 if there are two records

3 if there is one record

about textbox INV . NO I need it . because I have another macro to update the repeated numbers in textbox INV.NO
I hope this help

 

[KalilMe]

@Akuini

I suggested to use just some of it (12 textbox) + a listbox.
See if similar approach can be applied on your case.

actually if I use listbox and the invoice contains about three rows in list box I have to select each row individually to updating .
but my way I can change in all of tools (textboxes,comboboxes) at one time .

 

[DanteAmor]

You set a great example.

You have 4 records of an invoice.
How are you going to modify the 4?
You would need an extra textboxes and comboboxes to modify record 4.
What @Akuini and I want to express to you, what you intend is not good practice.
It is recommended to:
- Display your records in a listbox
- Select the register that you are going to modify
- Pass the data to a single line of textboxes and comboboxes
- Change the data
- Update the sheet.
It's a long way, yes. It takes more effort, yes. But it is a best practice, this way, if you have an invoice of 20 records, you only need 3 textboxes and 3 comboboxes.
I hope I have explained myself.

 

[KalilMe]

You have 4 records of an invoice.
How are you going to modify the 4?
You would need an extra textboxes and comboboxes to modify record 4.

I'm really sorry. forget it just 3

It's a long way, yes. It takes more effort, yes. But it is a best practice, this way, if you have an invoice of 20 records, you only need 3 textboxes and 3 comboboxes.
I hope I have explained myself.

so you say my way is impossible .
I know needing more tools(textboxes,comboboxes) if the invoice contains 5 or 6 records. but in my mind I will design that from the beggining until create new textboxes or comboboxes . just i need doing that by three records and I will adapt as far requirements .