新增ComboBox3联动ComboBox2行源及基于ComboBox的双范围查询需求

Got it, let's work through your Excel VBA requirements step by step, making sure each part is clear and functional:

Assuming this is tied to a primary ComboBox (let's call it ComboBox1 since you mentioned ComboBox2 and 3 later) with two options corresponding to your two target ranges, you can dynamically switch the lookup range in your code. Here's how to structure it:

Private Sub ComboBox1_Change()
    ' Store the selected range based on ComboBox1's value
    Dim lookupRange As Range
    
    Select Case ComboBox1.Value
        Case "Range 1" ' Replace with your actual option text
            Set lookupRange = Worksheets("cash").Range("BF:BH")
        Case "Range 2" ' Replace with your second option text
            Set lookupRange = Worksheets("cash").Range("BI:BK") ' Replace with your second range
        Case Else
            Set lookupRange = Nothing ' Clear range if no valid selection
    End Select
    
    ' Declare lookupRange as a module-level variable (outside all subs)
    ' if you want to reuse it in ComboBox2_Change without redefining it
End Sub

Pro tip: Putting Dim lookupRange As Range at the top of your module (outside all subs) lets you access this range across multiple events, which keeps your code cleaner.

First, add ComboBox3 to your userform/worksheet and populate its two options (e.g., "Category A" and "Category B"). Then use its Change event to update ComboBox2's row source:

Private Sub ComboBox3_Change()
    ' Clear ComboBox2 first to avoid leftover old values
    ComboBox2.Clear
    
    Select Case ComboBox3.Value
        Case "Category A" ' Replace with your first ComboBox3 option
            ' Set row source to your desired range for this category
            ComboBox2.RowSource = "cash!A2:A100" ' Replace with your actual range
        Case "Category B" ' Replace with your second ComboBox3 option
            ComboBox2.RowSource = "cash!D2:D100" ' Replace with your second range
        Case Else
            ComboBox2.RowSource = "" ' Clear row source if no valid selection
    End Select
End Sub

Note: Always include the sheet name in your row source reference if the range lives on a different sheet than your control—this prevents unexpected errors.

Your existing code is almost there—let's finish the unitplace lookup and add better error handling (instead of relying solely on On Error Resume Next):

Private Sub ComboBox2_Change()
    Dim myRange As Range
    ' Use the dynamic lookupRange from step 1 if you set up the module-level variable
    ' If not, define it directly here
    Set myRange = Worksheets("cash").Range("BF:BH")
    
    ' Clear previous values first
    Price.Value = ""
    unitplace.Value = ""
    
    On Error Resume Next ' Temporarily suppress errors for VLookup
    Price.Value = Application.WorksheetFunction.VLookup(ComboBox2.Value, myRange, 2, 0)
    unitplace.Value = Application.WorksheetFunction.VLookup(ComboBox2.Value, myRange, 3, 0) ' Completed with column 3
    On Error GoTo 0 ' Reset error handling to catch other issues
    
    ' Optional: Add a friendly message if no match is found
    If Price.Value = "" And unitplace.Value = "" Then
        MsgBox "No matching data found for " & ComboBox2.Value, vbInformation
    End If
End Sub

Key fix: I completed the VLookup for unitplace by specifying column 3 (since your range is BF:BH, column 2 is BG, column 3 is BH—adjust this number if your target column is different).

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