Hey there, let's break down why you're hitting that Runtime Error 1004 and fix it step by step!

1. The Most Likely Culprit: Typo in xlUp #

Looking at your code, the first issue jumps out immediately: you wrote x1Up (with a number 1) instead of xlUp (with a letter L). VBA doesn't recognize x1Up as a valid constant, which triggers the 1004 error right away.

2. Integer Overflow Risk #

You declared last as an Integer, but Excel worksheets can have up to 1,048,576 rows. Since Integer only supports values up to 32,767, this will cause an overflow error once your data passes that row count. Switch to Long instead—it's designed for larger numeric values.

3. Optional: Sheet Reference Clarity #

Using sheet2 relies on the worksheet's CodeName (the name in the VBA Project Explorer, not the tab name). If your worksheet's CodeName isn't actually Sheet2, this can also cause errors. To make the reference more reliable, use ThisWorkbook.Sheets("Sheet2") (replace "Sheet2" with your actual tab name if needed).

4. Quick Check: Verify TextBox Names #

Double-check that your user form has TextBox controls named exactly TextBox1 through TextBox8—typos here (like missing a number, or inconsistent capitalization) can also lead to object definition errors.

Modified Working Code #

Here's your code with all the fixes applied, plus some small cleanups:

Private Sub commandButton1_click()
    Dim last As Long ' Changed from Integer to Long to avoid overflow
    ' Fixed x1Up to xlUp, added explicit workbook reference for reliability
    last = ThisWorkbook.Sheets("Sheet2").Range("B1000").End(xlUp).Row + 1
    
    ' Use With block to simplify repeated sheet references
    With ThisWorkbook.Sheets("Sheet2")
        .Cells(last, "B").Value = Me.TextBox1.Value
        .Cells(last, "C").Value = Me.TextBox2.Value
        .Cells(last, "D").Value = Me.TextBox3.Value
        .Cells(last, "E").Value = Me.TextBox4.Value
        .Cells(last, "F").Value = Me.TextBox5.Value
        .Cells(last, "G").Value = Me.TextBox6.Value
        .Cells(last, "H").Value = Me.TextBox7.Value
        .Cells(last, "I").Value = Me.TextBox8.Value
    End With
    
    ' Clear all text boxes with a loop (cleaner than repetitive lines)
    Dim ctrl As Control
    For Each ctrl In Me.Controls
        If TypeName(ctrl) = "TextBox" Then
            ctrl.Value = ""
        End If
    Next ctrl
    
    MsgBox "Saved Successfully", vbInformation ' Fixed typo in "Successfuly"
    listsource ' Ensure this subroutine exists and is accessible (declare as Public if in another module)
End Sub

Final Notes #

• If listsource is a subroutine in a separate module, make sure it's declared as Public so your user form can access it.
• Test the code after making these changes—most likely the typo in xlUp was the main issue, but the other fixes prevent future errors as your data grows.

内容的提问来源于stack exchange，提问作者Mahmoud