multiple row data transfer from user form to same worksheet

dss28

Board Regular
Joined
Sep 3, 2020
Messages
61
Office Version
  1. 2007
Platform
  1. Windows
I am developing a program to update issuance of materials for a process. I have made a user form (userform38) with 15 rows to transfer data to a sheet. Each row has 7 textboxes and a command button.
Each line has a separate command button to transfer the data to the same sheet.
For row one - after clicking the command button , it displays a userform39. The code in this userform to transfer the data is same for all 15 userforms (39, 40, 41 ... 53 ) except changes in the textbox numbering as per each row.
While transferring the data the program calculates the remaining qty of a material and updates the stock. it works fine with one row but shows error when the command button on second row is clicked. The error line is

final = actual - UserForm38.TextBox8

which actually calculates the quantity remaining.

I need help in two things:
1. how to resolve this line error so that i can go on processing each row.
2. Is there a simpler way to transfer the data from these 15 lines at once in the same worksheet on consequent 15 rows.

i have included the code on userform 38 and 39 separately.

I have copied the code from some program from internet and using / adopting it for my program building and not developed by me.

please help.


VBA Code:
‘ Code on userform 38


Private Sub ComboBox1_Click()

Dim I As Integer
Dim final As Integer
Dim final1 As Integer
Dim final22 As Integer
Dim final222 As Integer
Dim FINAL2 As Integer
Dim j As Integer

For I = 2 To 10000              
If Sheet5.Cells(I, 1) = "" Then
final = I - 1
Exit For
End If
Next

For I = 2 To 10000                
If Sheet2.Cells(I, 1) = "" Then
final1 = I - 1
Exit For
End If
Next


For I = 2 To 10000                  
If Sheet2.Cells(I, 1) = "" Then
final222 = I - 1
Exit For
End If
Next

For I = 2 To 10000                  
If Sheet2.Cells(I, 1) = "" Then
final22 = I - 1
Exit For
End If
Next

For I = 2 To 10000
If sheet6.Cells(I, 1) = "" Then        
FINAL2 = I - 1
Exit For
End If
Next

For I = 2 To final
If ComboBox1 = Sheet5.Cells(I, 1) Then            
TextBox1 = Sheet5.Cells(I, 2)                      

Exit For
End If
Next

For I = 2 To final1
If ComboBox1 = Sheet2.Cells(I, 1) Then            
TextBox13 = Sheet2.Cells(I, 10)                
Exit For
End If
Next

For I = 2 To final22
If ComboBox1 = Sheet2.Cells(I, 1) Then            
TextBox14 = Sheet2.Cells(I, 4)                
Exit For
End If
Next

For I = 2 To final222
If ComboBox1 = Sheet2.Cells(I, 1) Then            
TextBox69 = Sheet2.Cells(I, 7)                
Exit For
End If
Next



For j = 1 To FINAL2                          
If ComboBox1 = sheet6.Cells(j, 1) Then
TextBox2 = sheet6.Cells(j, 3)                      
Exit For
End If
Next
End Sub

Private Sub ComboBox1_Enter()
Dim I As Integer
Dim final As Integer
Dim tareas As String

ComboBox1.BackColor = &H80000005

For I = 1 To ComboBox1.ListCount

               'Remove an item from the ListBox.
               ComboBox1.RemoveItem 0

           Next I

For I = 2 To 1000
If Sheet5.Cells(I, 1) = "" Then
final = I - 1
Exit For
End If
Next


'If ComboBox1.ListCount < 1 Then

'ComboBox1.AddItem "-"
For I = 2 To final
tareas = Sheet5.Cells(I, 1)
ComboBox1.AddItem (tareas)
Next

'End If

End Sub


Private Sub CommandButton3_Click()
Dim validar As Boolean
Dim validarfecha As Boolean


If TextBox1 = "" Then
UserForm16.Show            
Exit Sub
End If

If TextBox8 = "" Then      
UserForm17.Show
Exit Sub
End If

If TextBox4 = "" Then        
UserForm20.Show
Exit Sub
End If

If TextBox6 = "" Then        
UserForm19.Show
Exit Sub
End If

If TextBox7 = "" Then          
UserForm18.Show
Exit Sub
End If

validar = IsNumeric(TextBox8.Value)    
If validar = False Then
UserForm24.Show
TextBox8.BackColor = &HFF00&
Exit Sub
End If

validarfecha = IsDate(TextBox6.Value)
If validarfecha = False Then                
UserForm25.Show
TextBox6.BackColor = &HFF00&
Exit Sub
End If

If TextBox1 <> "" And TextBox4 <> "" And TextBox6 <> "" And TextBox7 <> "" And TextBox8 <> "" Then
TextBox8.BackColor = -2147483643
TextBox6.BackColor = -2147483643
UserForm39.Show
End If
End Sub




Private Sub TextBox8_Change()
Dim I As Integer
Dim final As Integer
Dim j As Integer
Dim antes As Integer
Dim validar As Boolean
Dim ahora As Double

Dim SALDO As Double


For I = 1 To 1000
If sheet6.Cells(I, 1) = "" Then
final = I
Exit For
End If
Next


For j = 1 To final
If ComboBox1 = sheet6.Cells(j, 1) Then
antes = sheet6.Cells(j, 3)

validar = IsNumeric(TextBox8.Value)
If validar = False Then
UserForm24.Show
TextBox8.BackColor = &HFF00&
Exit Sub
End If

ahora = TextBox8
If TextBox8 = "" Or TextBox8 = 0 Then
ahora = 0
TextBox9 = ""
End If

SALDO = antes - ahora
TextBox9 = SALDO
Exit For

End If
Next
End Sub


Code:
Form 39

Private Sub CommandButton1_Click()
Dim I As Integer
Dim final As Integer
Dim j As Integer
Dim actual As Double

For I = 1 To 1000
If Sheet3.Cells(I, 1) = "" Then
final = I
Exit For
End If
Next

Sheet3.Cells(final, 1) = UserForm38.ComboBox1    
Sheet3.Cells(final, 2) = UserForm38.TextBox1    
Sheet3.Cells(final, 3) = UserForm38.TextBox69  
Sheet3.Cells(final, 4) = UserForm38.TextBox8    
Sheet3.Cells(final, 5) = UserForm38.TextBox4      
Sheet3.Cells(final, 6) = UserForm38.TextBox7    
Sheet3.Cells(final, 7) = UserForm38.TextBox6    


For j = 1 To 1000
If sheet6.Cells(j, 1) = Sheet3.Cells(final, 1) Then
actual = sheet6.Cells(j, 3)
final = actual - UserForm38.TextBox8
sheet6.Cells(j, 3) = final
Exit For
End If
Next

UserForm38.ComboBox1 = ""  
UserForm38.TextBox1 = ""        
UserForm38.TextBox69 = ""      
UserForm38.TextBox2 = ""      
UserForm38.TextBox8 = ""    
UserForm38.TextBox9 = ""  
UserForm38.TextBox13 = ""  
UserForm38.TextBox14 = ""  

UserForm39.Hide


End Sub
userform 15 rows.jpg
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Watch MrExcel Video

Forum statistics

Threads
1,127,107
Messages
5,622,780
Members
415,927
Latest member
vedasinternational

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