Look for item in two columns vba

[kelly mort]

Hello,

I have yet another issue here with me.

Below is a code I am using to track the availability of an item under the "textbox enter evet".

It's working okay for now.

However, I am trying to also track the item when the textbox changes its contents. That is when I click my listbox to load the textboxes.

Since my case statement is pointing to the third textbox, anytime I click the listbox, only the case else statement is applied.

Because during the "freg2" change event, the "freg3" has not yet switched.


Is there a way I can look for the item from those two columns without using the case statement based on the challenges I have reviewed above?

Private fEnterValue,  fNameID,  fSName 
Private Sub freg2_Enter()
If freg3 <> "" Then fEnterValue = Trim(freg2.Text)
Select Case freg3.Text
    Case "X", "Y", "Z"
    For Each fSName In Sheet1.[AZ7:AZ407]
        If fSName = fEnterValue Then
            fNameID = fSName.Offset(0, -1)
            Exit For
        End If
    Next fSName
    
    Case Else
    For Each fSName In Sheet1.[C7:C407]
        If fSName = fEnterValue Then
            fNameID = fSName.Offset(0, -1)
            Exit For
        End If
    Next fSName
End Select
End Sub

 

[Anthony47]

Without a demo workbook suitable for replicating your environment I can only suggest that you use the freg3 change event.

Bye

 

[kelly mort]

Okay thanks .

It helped.

I actually used both the freg2 update event and listbox click event to track it and it's working cool for now.