CODEWITHSUNDEEP
excelvbauserform
user12821291
user12821291

Reputation:

VBA Excel Textbox clear after msg box appear

My code looks for an ID in the column "A", if its a match, then throws a msg so the user can´t re enter another person with the same ID. I wanted something simple and effective, so this works. The problem is when i clear the value, it pop ups again the same msg box, even when the textbox is clear. Any way to solve this issue?

Private Sub TextBox1_Change()

    Dim controlrow
    Dim lookrow

    controlrow = Me.TextBox1.Value
    Set lookrow = Hoja4.Range("A:A").Find(What:=controlrow, LookIn:=xlValues)

    If Me.TextBox1.Value = lookrow.Value Then
    MsgBox "El ID ya existe"
    Me.TextBox1.Value = ""
    End If

End Sub

Upvotes: 1

Views: 686

Answers (1)

urdearboy
urdearboy

Reputation: 14580

Amend your code to first check if the textbox is blank.

As mentioned by @JvdV, you also need to code for the possibility that the value you are searching for is never found. When that happens, you will error out for trying to compare Something to Nothing

Private Sub Textbox1_Change()

If Me.TextBox1.Value = "" Then
    Exit Sub
End If

Dim controlrow
Dim lookrow As Range

controlrow = Me.TextBox1.Value
Set lookrow = Hoja4.Range("A:A").Find(controlrow, LookIn:=xlValues)

If Not lookrow Is Nothing Then
    If Me.TextBox1.Value = lookrow.Value Then
        MsgBox "El ID ya existe"
        Me.TextBox1.Value = ""
    End If
End If

End Sub

Upvotes: 2

Related Questions