VBA用户窗体文本框校验问题：库存超限报错异常求助

Hey there! Let's break down why your current code is showing that error message for every input, even when you enter a valid quantity. There are two main culprits here: data type mismatch and unhandled errors from VLookup. Here's how to fix it:

Key Problems in Your Original Code #

• The MaterialQuantityTextBox.Value returns text, but you're comparing it directly to a numeric value from VLookup. Text vs numeric comparisons can behave unexpectedly in VBA (e.g., a text "5" might be treated as larger than a numeric 10 in some cases).
• If WorksheetFunction.VLookup can't find the selected material in your inventory sheet, it throws a runtime error. Since you don't handle this error, your code might be triggering the message box even when the lookup fails—not just when the quantity is too high.

Revised Code with Fixes #

Private Sub MaterialQuantityTextBox_AfterUpdate()
    Dim enteredQty As Double
    Dim availableQty As Variant
    Dim inventorySheet As Worksheet
    
    ' Set reference to your "In-house inventory" sheet (adjust name if needed)
    Set inventorySheet = ThisWorkbook.Worksheets("In-house inventory")
    
    ' First validate the input is a number
    If Not IsNumeric(MaterialQuantityTextBox.Value) Then
        MsgBox "Please enter a valid numeric quantity."
        MaterialQuantityTextBox.SetFocus
        MaterialQuantityTextBox.Value = ""
        Exit Sub
    End If
    
    ' Convert text input to numeric value for proper comparison
    enteredQty = CDbl(MaterialQuantityTextBox.Value)
    
    ' Use Application.VLookup to handle missing items gracefully
    ' (it returns an error instead of crashing if no match is found)
    availableQty = Application.VLookup( _
        Me.InhouseMaterialComboBox.Value, _
        inventorySheet.Range("B:D"), _
        3, _
        False _
    )
    
    ' Check if the material was found in inventory
    If IsError(availableQty) Then
        MsgBox "Selected material not found in inventory. Please verify your selection."
        Me.InhouseMaterialComboBox.SetFocus
        Exit Sub
    End If
    
    ' Now compare the numeric values correctly
    If enteredQty > CDbl(availableQty) Then
        MsgBox "Entered material quantity is greater than the available quantity in the inventory."
        MaterialQuantityTextBox.SetFocus
        MaterialQuantityTextBox.Value = ""
    End If
End Sub

What This Code Does Differently #

• Validates Numeric Input: First checks if the user entered a number, preventing non-numeric values from breaking the comparison.
• Converts Text to Numeric: Ensures both the entered quantity and inventory quantity are numeric types, so comparisons work as expected.
• Handles Missing Materials: Uses Application.VLookup instead of WorksheetFunction.VLookup to avoid runtime errors when a material isn't found, and shows a clear message to the user.
• Improves User Experience: Sets focus back to the problematic control and clears invalid input, making it easier for the user to correct mistakes quickly.

Quick Checks to Ensure It Works #

• Double-check that your "In-house inventory" sheet name matches exactly in the code ("In-house inventory").
• Confirm column D in your inventory sheet contains numeric values (not text-formatted numbers)—this ensures the lookup returns a valid number for comparison.

内容的提问来源于stack exchange，提问作者T.Sarathchandra