Excel VBA运行时错误1004：Worksheets方法调用失败求助

Hey there! As a first-time VBA programmer, it’s totally normal to hit this kind of snag—let’s break down why that error is popping up and fix it step by step.

What’s Causing the Error? #

The Run-time error '1004' on this line:

Me.PortLocation.List = Worksheets("Data lookup_ports").range("e3:e200").Value

Almost always stems from VBA being unable to locate the worksheet you’re referencing. Here’s the key issue: when you open GUI.xlsm in your UserForm_Initialize sub, the active workbook switches to that new file. When you call Worksheets("Data lookup_ports") without specifying which workbook it belongs to, VBA looks in the currently active workbook (now GUI.xlsm) instead of your original workbook that contains the "Data lookup_ports" sheet.

Step-by-Step Fixes #

1. Explicitly reference your code’s workbook
   Use ThisWorkbook to target the workbook that holds your VBA code—this is far more reliable than relying on ActiveWorkbook, which can change unexpectedly. Update the problematic line to:

   Me.PortLocation.List = ThisWorkbook.Worksheets("Data lookup_ports").Range("E3:E200").Value
   

2. Verify your worksheet name
   Double-check that the sheet’s exact name matches "Data lookup_ports"—no extra spaces, typos, or capitalization mismatches. Even a tiny error (like "Data lookup_port" missing an 's') will trigger this error.

3. Clean up your temporary workbook
   You’re opening GUI.xlsm but never closing it, which can leave it running in the background and cause unexpected behavior. Add this line at the end of your UserForm_Initialize sub to close it properly:

   MyTempWkBk.Close SaveChanges:=False
   

Revised UserForm_Initialize Code #

Here’s your corrected sub with all fixes applied:

Private Sub UserForm_Initialize()
    ' Reference the worksheet in your current code workbook
    Me.PortLocation.List = ThisWorkbook.Worksheets("Data lookup_ports").Range("E3:E200").Value
    
    Dim MyTempWkBk As Workbook
    Dim MyCurrentWin As Window
    
    Set MyCurrentWin = ActiveWindow
    Set MyTempWkBk = Workbooks.Open("D:\Users\611281\Downloads\Zebra\GUI.xlsm")
    
    MyCurrentWin.Activate
    MyTempWkBk.Windows.Visible = False
    
    ' Close the temporary workbook without saving changes
    MyTempWkBk.Close SaveChanges:=False
End Sub

Extra Tip for Future Projects #

Get in the habit of explicitly referencing workbooks and worksheets instead of using ActiveSheet or unqualified Worksheets calls. This will save you from tons of similar errors down the line—especially when working with multiple workbooks at once.

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