Hello,
I have two sheets within the same workbook that have essentially the same UserForm in each, but referencing two different tables. I did this by exporting the UserForm, importing it, and then renaming it, as well as changing the range. The first UserForm inserts new data into the first available row which has formulas in different cells within that row. The second UserForm, which is literally the same thing as the first UserForm besides a different range, is adding a row to the bottom of the table.
UserForm1
UserForm2
I am incredibly curious as to why this effect is occurring. I have deleted and re-entered the code manually to see if there was an issue and I still get the result of a new row being added to the bottom of the table. I have 5 more sheets in the same workbook that I will be doing the exact same thing for, but with different ranges, so I am really hoping that I can understand what I am doing wrong.
Thank you so much!
I have two sheets within the same workbook that have essentially the same UserForm in each, but referencing two different tables. I did this by exporting the UserForm, importing it, and then renaming it, as well as changing the range. The first UserForm inserts new data into the first available row which has formulas in different cells within that row. The second UserForm, which is literally the same thing as the first UserForm besides a different range, is adding a row to the bottom of the table.
UserForm1
VBA Code:
Private Sub OKButton_Click()
Dim rng As Range
Dim LastRow As Long
Set rng = ActiveSheet.ListObjects("SunSch").Range
LastRow = rng.Find("*", rng.Cells(1), xlValues, xlPart, xlByRows, xlPrevious, False).Row + 1
rng.Parent.Cells(LastRow, 1).Value = StoreTextBox.Value
rng.Parent.Cells(LastRow, 7).Value = TrailerTextBox.Value
rng.Parent.Cells(LastRow, 8).Value = TractorTextBox.Value
rng.Parent.Cells(LastRow, 9).Value = ProTextBox.Value
rng.Parent.Cells(LastRow, 10).Value = LoadTextBox.Value
rng.Parent.Cells(LastRow, 12).Value = DriverTextBox.Value
rng.Parent.Cells(LastRow, 15).Value = StopTextBox.Value
rng.Parent.Cells(LastRow, 19).Value = ConfirmedTextBox.Value
If DispatchCheck1.Value = True Then
rng.Parent.Cells(LastRow, 11).Value = "Yes"
ElseIf DispatchCheck2.Value = True Then
rng.Parent.Cells(LastRow, 11).Value = "No"
Else
rng.Parent.Cells(LastRow, 11).Value = ""
End If
If InfoCheck1.Value = True Then
rng.Parent.Cells(LastRow, 20).Value = "Yes"
ElseIf InfoCheck2.Value = True Then
rng.Parent.Cells(LastRow, 20).Value = "No"
Else
rng.Parent.Cells(LastRow, 20).Value = ""
End If
If LiveCheck1.Value = True Then
rng.Parent.Cells(LastRow, 30).Value = "Yes"
ElseIf LiveCheck2.Value = True Then
rng.Parent.Cells(LastRow, 30).Value = "No"
Else
rng.Parent.Cells(LastRow, 30).Value = ""
End If
If CancelCheck1.Value = True Then
rng.Parent.Cells(LastRow, 31).Value = "Yes"
ElseIf CancelCheck2.Value = True Then
rng.Parent.Cells(LastRow, 31).Value = "No"
Else
rng.Parent.Cells(LastRow, 31).Value = ""
End If
If SweepCheck1.Value = True Then
rng.Parent.Cells(LastRow, 32).Value = "Yes"
ElseIf SweepCheck2.Value = True Then
rng.Parent.Cells(LastRow, 32).Value = "No"
Else
rng.Parent.Cells(LastRow, 32).Value = ""
End If
If RevCheck1.Value = True Then
rng.Parent.Cells(LastRow, 33).Value = "Yes"
ElseIf RevCheck2.Value = True Then
rng.Parent.Cells(LastRow, 33).Value = "No"
Else
rng.Parent.Cells(LastRow, 33).Value = ""
End If
If BackhaulCheck1.Value = True Then
rng.Parent.Cells(LastRow, 34).Value = "Yes"
ElseIf BackhaulCheck2.Value = True Then
rng.Parent.Cells(LastRow, 34).Value = "No"
Else
rng.Parent.Cells(LastRow, 34).Value = ""
End If
End Sub
UserForm2
VBA Code:
Private Sub OKButton_Click()
Dim rng As Range
Dim LastRow As Long
Set rng = ActiveSheet.ListObjects("MonSch").Range
LastRow = rng.Find("*", rng.Cells(1), xlValues, xlPart, xlByRows, xlPrevious, False).Row + 1
rng.Parent.Cells(LastRow, 1).Value = StoreTextBox.Value
rng.Parent.Cells(LastRow, 7).Value = TrailerTextBox.Value
rng.Parent.Cells(LastRow, 8).Value = TractorTextBox.Value
rng.Parent.Cells(LastRow, 9).Value = ProTextBox.Value
rng.Parent.Cells(LastRow, 10).Value = LoadTextBox.Value
rng.Parent.Cells(LastRow, 12).Value = DriverTextBox.Value
rng.Parent.Cells(LastRow, 15).Value = StopTextBox.Value
rng.Parent.Cells(LastRow, 19).Value = ConfirmedTextBox.Value
If DispatchCheck1.Value = True Then
rng.Parent.Cells(LastRow, 11).Value = "Yes"
ElseIf DispatchCheck2.Value = True Then
rng.Parent.Cells(LastRow, 11).Value = "No"
Else
rng.Parent.Cells(LastRow, 11).Value = ""
End If
If InfoCheck1.Value = True Then
rng.Parent.Cells(LastRow, 20).Value = "Yes"
ElseIf InfoCheck2.Value = True Then
rng.Parent.Cells(LastRow, 20).Value = "No"
Else
rng.Parent.Cells(LastRow, 20).Value = ""
End If
If LiveCheck1.Value = True Then
rng.Parent.Cells(LastRow, 30).Value = "Yes"
ElseIf LiveCheck2.Value = True Then
rng.Parent.Cells(LastRow, 30).Value = "No"
Else
rng.Parent.Cells(LastRow, 30).Value = ""
End If
If CancelCheck1.Value = True Then
rng.Parent.Cells(LastRow, 31).Value = "Yes"
ElseIf CancelCheck2.Value = True Then
rng.Parent.Cells(LastRow, 31).Value = "No"
Else
rng.Parent.Cells(LastRow, 31).Value = ""
End If
If SweepCheck1.Value = True Then
rng.Parent.Cells(LastRow, 32).Value = "Yes"
ElseIf SweepCheck2.Value = True Then
rng.Parent.Cells(LastRow, 32).Value = "No"
Else
rng.Parent.Cells(LastRow, 32).Value = ""
End If
If RevCheck1.Value = True Then
rng.Parent.Cells(LastRow, 33).Value = "Yes"
ElseIf RevCheck2.Value = True Then
rng.Parent.Cells(LastRow, 33).Value = "No"
Else
rng.Parent.Cells(LastRow, 33).Value = ""
End If
If BackhaulCheck1.Value = True Then
rng.Parent.Cells(LastRow, 34).Value = "Yes"
ElseIf BackhaulCheck2.Value = True Then
rng.Parent.Cells(LastRow, 34).Value = "No"
Else
rng.Parent.Cells(LastRow, 34).Value = ""
End If
End Sub
I am incredibly curious as to why this effect is occurring. I have deleted and re-entered the code manually to see if there was an issue and I still get the result of a new row being added to the bottom of the table. I have 5 more sheets in the same workbook that I will be doing the exact same thing for, but with different ranges, so I am really hoping that I can understand what I am doing wrong.
Thank you so much!