CODEWITHSUNDEEP
formsvbaexcelerror-handlingtextbox
Stupid_Intern
Stupid_Intern

Reputation: 3450

Error on textbox text change event VBA form

I have a textbox named "txtEndBalance" in a form I want only numbers to be inserted as a part of data validation. Therefore I have On Error event.

Now when I fire up the form via a button. It directly goes to the error and I have a message box pop up "Invalid currency amount". This wasn't supposed to be like this.

And then when I get rid of the pop up trying to enter a number. And right when I enter a single digit number it throws that error again.

I have no idea where I am wrong in the code below because I think I did everything right.

Have look at the file here if you need.

Private Sub txtEndBalance_Change()

    On Error GoTo Error:

    Dim amt As Currency
    amt = txtEndBalance
    txtEndBalance = Format(amt, "$##,###,##0")
    Exit Sub

Error:
        If txtEndBalance <> "$" And txtEndBalance <> "" Then
        MsgBox "Invalid currency amount", , "Error"
        txtEndBalance = Format(0, "$##,###,##0")
        End If

End Sub

Upvotes: 3

Views: 1785

Answers (2)

Harley B
Harley B

Reputation: 569

Your basic issue is that amt is declared as a Currency variable and the value you're trying to assign to it from the textEndBalance box isn't a Currency object, so your sub is throwing a type mismatch error and dropping you into your error handler.

The reason this is happening on each load of the form is that you're setting the value of the field txtEndBalance on load to a formatted version of a named range. This triggers the txtEndBalance_Change and you get a type mismatch error when trying to assign the value of that text box to the currency variable amt.

A simple fix would be to change how you declare the amt variable to something that matches - Dim amt As Variant works, or maybe even do away with the amt variable and use something like txtEndBalance = Format(txtEndBalance.Value, "$##,###,##0")

Also, as mentioned in another answer, you shouldn't use the Error keyword as a label for your Error Handlers.

Upvotes: 0

MatthewD
MatthewD

Reputation: 6761

This will make it so only numbers can be entered. Put in this KeyPress event.

Private Sub txtEndBalance_KeyPress(KeyAscii As Integer)

    'This is a check for backspace keypress.
    If KeyAscii = 8 Then Exit Sub

    'This will allow only numeric values in the text box.
    If Chr(KeyAscii) < "0" Or Chr(KeyAscii) > "9" Then
        KeyAscii = 0
    End If
End Sub

Then you can do your formatting in the change event.

Upvotes: 2

Related Questions