VBA运行时错误424（需要对象）：HLookup语句报错求助

Hey there, let's break down why you're suddenly hitting this error even though your code worked before and the equivalent Excel formula runs fine. Here are the most likely fixes to try step by step:

1. Swap Text Property for Value on Your TextBox #

For user form text boxes, the Text property only works when the control has focus. If your form or text box isn't active when this line runs, it can trigger an "Object Required" error. The Value property works regardless of focus, so it's more reliable here:

Camposcomplementares.Portar1.Value = Application.WorksheetFunction.HLookup(Sheets("Admin_Lists").Range("BH45"), Sheets("Admin_Lists").Range("BB11:BG38"), 14, False)

2. Handle HLookup Errors Gracefully #

The Application.WorksheetFunction.HLookup method throws a hard error if no match is found, which can sometimes cascade into an object-related error even if your form/control is fine. Use Application.HLookup instead (it returns an error value instead of crashing) and add a check to catch missing matches:

Dim lookupResult As Variant
lookupResult = Application.HLookup( _
    Sheets("Admin_Lists").Range("BH45").Value, _
    Sheets("Admin_Lists").Range("BB11:BG38"), _
    14, _
    False _
)

If Not IsError(lookupResult) Then
    Camposcomplementares.Portar1.Value = lookupResult
Else
    MsgBox "No matching value found in the lookup range!", vbExclamation
End If

This way, you'll know if the issue is a missing match instead of an object reference problem.

3. Explicitly Instantiate Your User Form #

Sometimes Excel's memory can glitch, causing the default form instance to become invalid. Instead of relying on it, explicitly create and reference the form:

Dim myForm As Camposcomplementares
Set myForm = New Camposcomplementares

' Assign the lookup result to the text box
myForm.Portar1.Value = Application.WorksheetFunction.HLookup(Sheets("Admin_Lists").Range("BH45"), Sheets("Admin_Lists").Range("BB11:BG38"), 14, False)

' Show the form if needed (use vbModal if you want it to block other actions)
myForm.Show vbModeless

' Clean up the object when you're done
Set myForm = Nothing

4. Use Worksheet Code Names for Reliable References #

While you confirmed the sheet display name is correct, using the worksheet's code name avoids issues if someone renames the sheet accidentally. Here's how:

• In the VBA Editor, find the Admin_Lists sheet in the Project Explorer.
• Look at its (Name) property in the Properties window (default is something like Sheet1—rename it to AdminLists for clarity).
• Update your code to use this code name:

Camposcomplementares.Portar1.Value = Application.WorksheetFunction.HLookup(AdminLists.Range("BH45"), AdminLists.Range("BB11:BG38"), 14, False)

5. Rule Out Excel or Workbook Corruption #

If none of the above fixes work, the problem might be with Excel itself or your workbook:

• Save your workbook, close Excel completely, then reopen and test the code.
• Copy your code and data to a brand new workbook to see if the error persists.
• Run a quick repair of Microsoft Office via your computer's Control Panel (under Programs > Programs and Features > Microsoft Office > Change > Quick Repair).

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