Userform. Data identical except for ID#, generate excel row for each ID#

NHagedorn

New Member
Joined
May 11, 2012
Messages
37
Office Version
  1. 365
Platform
  1. Windows
Hello all..

Wondering if it is possible to build a userform for data entry.... sometimes all of the data may be identical except for the ID#.... What is the best way of processing this thru a userform? Thought maybe set up ten text boxes where if a ID# is entered the userform will generate a new row of data for each ID#..... duplicating all the rest of the data

Example if I have 10 different cars.... the cars are identical except for their ID#s... I would like the userform to generate 10 rows of data on sheet1... all data would be identical except for the ID#s.. end result would look like below....however I would not have had to fill out the userform 10 times.... only once but entered each different ID#s

Is this possible? Would it be easiest to have 10 textboxes so that one could enter up to 10 different ID#s? Is there a better way to enter this data in a userform?

Below VBA is the userform VBA that I am using to push data from userform to "Sheet1"

Thank you in advance.
Nate

ColorWindowsTransmissionTiresInteriorID#
RedPowerAutomaticNewLeather
774847​
RedPowerAutomaticNewLeather
88575​
RedPowerAutomaticNewLeather
73639327​
RedPowerAutomaticNewLeather
8287247​
RedPowerAutomaticNewLeather
88889799​
RedPowerAutomaticNewLeather
9797975​
RedPowerAutomaticNewLeather
2487594​
RedPowerAutomaticNewLeather
664849​
RedPowerAutomaticNewLeather
736​
RedPowerAutomaticNewLeather
6363663​

VBA Code:
Private Sub CommandButton1_Click()
Application.DisplayAlerts = False
erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Cells(erow, 1) = txtDate.Text
Cells(erow, 2) = txtSSRName.Text
Cells(erow, 3) = txtFRSName.Text
Cells(erow, 4) = txtContactNumber.Text
Cells(erow, 5) = ComboBox1
Cells(erow, 6) = txtPreviouslyReported.Text
Cells(erow, 7) = cboTopics
Cells(erow, 8) = cboIssues
Cells(erow, 9) = ComboBox3
Cells(erow, 10) = txtPatientID.Text
Cells(erow, 12) = ComboBox2
Cells(erow, 16) = txtPertinentDetails.Text
Cells(erow, 17) = txtOtherImportant.Text
Cells(erow, 19) = txtClaimDenial.Text
Cells(erow, 20) = txtPayer.Text
Cells(erow, 21) = txtDOS.Text
Cells(erow, 22) = txtAppealInfo.Text
Cells(erow, 23) = txtSiteName.Text
Cells(erow, 25) = txtHCPName.Text
Cells(erow, 24) = txtSiteID.Text
Cells(erow, 26) = txtSiteContact.Text
Cells(erow, 27) = txtSitePhone.Text
Cells(erow, 28) = txtBestTime.Text
Cells(erow, 29) = txtExpectations.Text

txtDate.Text = ""
txtSSRName.Text = ""
txtFRSName.Text = ""
txtContactNumber.Text = ""
ComboBox1 = ""
txtPreviouslyReported.Text = ""
cboTopics = ""
cboIssues = ""
ComboBox3 = ""
txtPatientID.Text = ""
ComboBox2 = ""
txtPertinentDetails.Text = ""
txtOtherImportant.Text = ""
txtClaimDenial.Text = ""
txtPayer.Text = ""
txtDOS.Text = ""
txtAppealInfo.Text = ""
txtSiteName.Text = ""
txtHCPName.Text = ""
txtSiteID.Text = ""
txtSiteContact.Text = ""
txtSitePhone.Text = ""
txtBestTime.Text = ""
txtExpectations.Text = ""

Application.Visible = True
Unload Me


ActiveWorkbook.Save
Application.DisplayAlerts = True






End Sub
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,581
Office Version
  1. 2013
Platform
  1. Windows
So these ten different serial numbers?
Where are these numbers coming from?
To enter these numbers into Textboxes where are these numbers coming from?
Like if I was to enter 14567456 into textbox1.
How did I know this number?
 

NHagedorn

New Member
Joined
May 11, 2012
Messages
37
Office Version
  1. 365
Platform
  1. Windows
Found a solution... Check to see if they are splittable ... separate them by commas rather than individual boxes

I would be entering the serial numbers via the userform

Thank you for the assistance.



'Check to make sure that TxtPart is splittable
If InStr(Me.TxtPart.Value, ",") > 0 Then
PartNbrs = Split(Me.TxtPart.Value, ",")
Else
PartNbrs = Array(Me.TxtTart.Value)
End If
For j = LBound(PartNbrs) To UBound(PartNbrs)
'copy the data to the database
ws.Cells(iRow + j, 1).Value = Me.TxtDate.Value
ws.Cells(iRow + j, 2).Value = Me.TxtCustomer.Value
ws.Cells(iRow + j, 3).Value = Me.TxtPO.Value
ws.Cells(iRow + j, 4).Value = Me.TxtLoc.Value
ws.Cells(iRow + j, 5).Value = PartNbrs(j)
ws.Cells(iRow + j, 6).Value = Me.TxtQty.Value
ws.Cells(iRow + j, 7).Value = Me.TxtESD.Value
ws.Cells(iRow + j, 8).Value = Me.TxtEntBy.Value
Next j

 
Solution

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,581
Office Version
  1. 2013
Platform
  1. Windows
This way you have now found is workable. But you never answered my question.
If the Id#s are already somewhere in your workbook then these numbers could be loaded into a control on userform when opened and then a script could be written to enter them is another location. I would be surprised if these ID #s are not already somewhere in the workbook.
 

NHagedorn

New Member
Joined
May 11, 2012
Messages
37
Office Version
  1. 365
Platform
  1. Windows
This way you have now found is workable. But you never answered my question.
If the Id#s are already somewhere in your workbook then these numbers could be loaded into a control on userform when opened and then a script could be written to enter them is another location. I would be surprised if these ID #s are not already somewhere in the workbook.
The ID#s along with the data are being manually entered into the Userform
 

Watch MrExcel Video

Forum statistics

Threads
1,129,914
Messages
5,638,955
Members
417,062
Latest member
Canucks21

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