If Statement only evaluates "False"

[ERyan]

Hello,

I'm a new member here and this is my first post. I really appreciate any help you all might be able to offer. I am a bit new to VBA, but eager to learn, so your critique is welcome.

I am creating a userform which uses combo boxes to collect Account Name and Account Number, and Option buttons to collect Status (Active, Inactive) then adds the information to sheet (Accounts). I'm working on making this dynamic in that if someone attempts to enter an account name or account number that is already on the lsit the userform will search and recognize it is already on the list and auto-populate the rest of the form. I have it working perfectly when I enter an already existing Account Name in that combo box. The rest of the form auto-populates just as is should using the following code:

Private Sub cboName_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    Dim last As Integer, varcboName As String
    varcboName = ""
    With ActiveSheet
        last = .Cells(.Rows.Count, "B").End(xlUp).Row
    End With
    
    For Each cell In Range("B3:B" & last)
        If cell.Value = Me.cboName.Value Then
            varcboName = cell.Value
            cboNumber.Value = cell.Offset(0, -1).Value
            If cell.Offset(0, 1).Value = "Active" Then
                optActive = True
            Else
                optInactive = True
            End If
            cmdUpdate.Enabled = True
        End If
    Next
    If varcboName <> Me.cboName.Value Then
        cmdAddNew.Enabled = True
    End If
End Sub

So I tried the same approach for the combo box which accepts the Account Number using the following code:

Private Sub cboNumber_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    Dim last As Integer, varcboNumber As Long
    varcboNumber = 0
    With ActiveSheet
        last = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With
    
    For Each cell In Range("A3:A" & last)
        If cell.Value = Me.cboNumber.Value Then
            varcboNumber = cell.Value
            cboName.Value = cell.Offset(0, 1).Value
            If cell.Offset(0, 2).Value = "Active" Then
                optActive = True
            Else
                optInactive = True
            End If
            cmdUpdate.Enabled = True
        End If
    Next
    If varcboNumber <> Me.cboNumber.Value Then
        cmdAddNew.Enabled = True
    End If
End Sub

This one does not auto-populate anything but it does not give an error message. I believe I've narrowed this down to the line in red above. This appears to always evaluate to False, even if I enter a number in the combo box which matches perfectly with a number in column A on my sheet. I think it has to do with some kind of mismatch between strings and integers but I have tried every variation with the variable call I can think of. Any help here would be greatly appreciated.

 

[RickXL]

Hi and welcome to the MrExcel Message Board.

Try this line instead:

If CLng(cell.Value) = CLng(Me.cboNumber.Value) Then

That will convert both values to Longs before making the comparison.

Regards,

 

[ERyan]

This works perfectly. Thank you so much for the help RickXL!

 

[RickXL]

You're welcome and thanks for the "Like".

Regards,