text box not properly triggering multiple criteria lookup in userform?

Question

I have a function in a userform I'm using to lookup a row within a worksheet to extract 3 cell values once the proper row has been matched. I figure I needed to place the sub within the last value that's triggered.

Private Sub txtCompCode_Change()
    Dim recRow As Range
    Set recRow = MatchSalaryRange(Worksheets("Salary Ranges").Range("A1").CurrentRegion, _
                          Me.cmbPosition.Value, _
                          txtCompCode.Text)
    If recRow Is Nothing Then Exit Sub
    With recRow.EntireRow
        .Cells(4).Value = Me.txtMIN.Text
        .Cells(5).Value = Me.txtMID.Text
        .Cells(6).Value = Me.txtMAX.Text
    End With
End Sub
Function MatchSalaryRange(tablerange As Range, lPosition, lCompCode) As Range
    Dim rw As Range
    For Each rw In tablerange.Rows
        If CStr(rw.Cells(2).Value) = CStr(lPosition) Then
            If rw.Cells(3).Value = lCompCode Then
                Set MatchSalaryRange = rw
                Exit Function
            End If
        End If
    Next rw
End Function

it first references a job title in a text box and then a compensation code in another text box. then it locates the row, supposed to pull columns D:E in order to populate the Min/Mid/Max text boxes, but it's not doing so.

Any ideas?

EDIT: based on this after the right text box in yellow is selected, it should trigger the function for the red text boxes below.

If you place a breakpoint does it ever hit the inner If in your function? — Tim Williams
It's hard to tell what exactly is happening. What do the cells contain (cell 1 / 2), what does cmbPosition contain, what does txtCompCode contain. As @TimWilliams suggested, doing some debugging should tell you more. — Nicolas
@NidenK It seems that this is caused by the event type of txtCompCode. The Change event triggers as soon as the value of the textbox changes, perhaps one of the other events is better used, AfterUpdate for example (though I need to know more about the use case). Is store number another textbox which automatically fills the txtCompCode textbox? — Nicolas
@TimWilliams ok so I changed the event type to the job position selection in order to trigger the sub. It never reaches the inner IF, what could be causing that? — NidenK
@TimWilliams sorry, it's now hitting the set matchsalaryrange = rw part, but it's saying it's set to nothing — NidenK

NidenK NidenK · Accepted Answer · 2020-07-30T17:10:01

Just needed to switch around this block:

    With recRow.EntireRow
        Me.txtMIN.Text = .Cells(4).Value
        Me.txtMID.Text = .Cells(5).Value
        Me.txtMAX.Text = .Cells(6).Value
    End With

text box not properly triggering multiple criteria lookup in userform?

1 Answers