using loop to populate data in textboxes and comboboxes after matching

KalilMe

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

rlv01

Well-known Member
Joined
May 16, 2017
Messages
1,082
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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.
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

KalilMe

Board Regular
Joined
Mar 5, 2021
Messages
166
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Dec 3, 2018
Messages
13,771
Office Version
  1. 2007
Platform
  1. Windows
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?
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
3,471
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Dec 3, 2018
Messages
13,771
Office Version
  1. 2007
Platform
  1. Windows
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

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

ADVERTISEMENT

@DanteAmor this is three cases
if there are three records
1.PNG



2 if there are two records
2.PNG


3 if there is one record
3.PNG

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

Board Regular
Joined
Mar 5, 2021
Messages
166
Office Version
  1. 2016
Platform
  1. Windows
@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

Well-known Member
Joined
Dec 3, 2018
Messages
13,771
Office Version
  1. 2007
Platform
  1. Windows
You set a great example.
1633696292052.png

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

Board Regular
Joined
Mar 5, 2021
Messages
166
Office Version
  1. 2016
Platform
  1. Windows
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 .
 

Forum statistics

Threads
1,144,422
Messages
5,724,237
Members
422,543
Latest member
jedidia

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