dss28
Board Regular
- Joined
- Sep 3, 2020
- Messages
- 165
- Office Version
- 2007
- Platform
- Windows
I have a form in which I have 15 rows for data input and each row has a series of boxes and comboboxes.
Each row has a separate command button to save the data to sheet.
The basic code is same except each line command button has reference to the respective textboxes/comboxes in that row.
I had no issues with the code for almost 5-6 months but now suddenly the code is giving "Run time error 1004 - Application defined or object defined error" and stopping intermittently on any row. sometimes it goes through but then it is stuck on the first line iteself for last couple of days and can not go further.
for internet i tried to gather information about this error and I tried to use :
1. "Thisworkbook.sheets("SheetName").activate code as there are two sheets involved in the code transfer from userform to sheet.
2. .value added in the code eg. "Sheet3.Cells(final, 1) = UserForm38.ComboBox1.Value"
but no success.
please help to resolve the issue.
The code stops at line "Sheet3.Cells(final, 1) = UserForm38.ComboBox1.Value"
Each row has a separate command button to save the data to sheet.
The basic code is same except each line command button has reference to the respective textboxes/comboxes in that row.
VBA Code:
I had no issues with the code for almost 5-6 months but now suddenly the code is giving "Run time error 1004 - Application defined or object defined error" and stopping intermittently on any row. sometimes it goes through but then it is stuck on the first line iteself for last couple of days and can not go further.
for internet i tried to gather information about this error and I tried to use :
1. "Thisworkbook.sheets("SheetName").activate code as there are two sheets involved in the code transfer from userform to sheet.
2. .value added in the code eg. "Sheet3.Cells(final, 1) = UserForm38.ComboBox1.Value"
but no success.
please help to resolve the issue.
The code stops at line "Sheet3.Cells(final, 1) = UserForm38.ComboBox1.Value"
VBA Code:
Private Sub cmd39_Click()
Dim i As Integer
Dim final As Double
Dim j As Integer
Dim actual As Double
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.EnableEvents = False
ActiveSheet.DisplayPageBreaks = False
ThisWorkbook.Sheets("Data").Activate ‘ sheet3
For i = 1 To 1000
If Sheet3.Cells(i, 1) = "" Then
final = i
Exit For
End If
Next
Application.ScreenUpdating = False
Sheet3.Cells(final, 1) = UserForm38.ComboBox1.Value
Sheet3.Cells(final, 2) = UserForm38.TextBox1.Value
Sheet3.Cells(final, 3) = UserForm38.TextBox69.Value
Sheet3.Cells(final, 4) = Format(UserForm38.TextBox8, "0.00000000")
Sheet3.Cells(final, 5) = UserForm38.ComboBox16.Value
Sheet3.Cells(final, 6) = UserForm38.TextBox7.Value
Sheet3.Cells(final, 7) = Format(UserForm38.TextBox6, "dd/mm/yyyy")
Sheet3.Cells(final, 8) = UserForm38.Label17
Sheet3.Cells(final, 9) = UserForm38.TextBox114.Value
Sheet3.Cells(final, 10) = UserForm38.TextBox14.Value
Sheet3.Cells(final, 11) = Format(UserForm38.TextBox115, "0.000000")
Sheet3.Cells(final, 13) = Format(UserForm38.TextBox130, "0.000")
Sheet3.Cells(final, 15) = Format(UserForm38.TextBox160, "0.000")
Sheet3.Cells(final, 16) = UserForm38.TextBox177.Value
Application.ScreenUpdating = False
For j = 1 To 1000
If sheet6.Cells(j, 1) = Sheet3.Cells(final, 1) Then
Application.ScreenUpdating = False
actual = sheet6.Cells(j, 3)
final = actual - UserForm38.TextBox8
sheet6.Cells(j, 3) = final
Exit For
End If
Next
Application.ScreenUpdating = False
MsgBox "Data of 1st row saved"
UserForm39.Hide
Application.ScreenUpdating = False
UserForm38.CommandButton3.Caption = "Data Saved"
UserForm38.CommandButton3.BackColor = &HFF00&
UserForm38.CommandButton3.Enabled = False
Application.DisplayAlerts = False
ThisWorkbook.Save
Application.DisplayAlerts = True
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.DisplayStatusBar = True
Application.EnableEvents = True
ActiveSheet.DisplayPageBreaks = True
End Sub