Error on textbox text change event VBA form

Question

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

I don't get an error, when the userform shows, I just get an error if I enter text in the textbox instead of numbers, I also get an error when I click the command button, as it is trying to find a sub function. — Davesexcel
– Davesexcel, Commented Oct 3, 2015 at 11:51

MatthewD · Accepted Answer · 2015-10-03 12:47:07Z

2

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.

edited Oct 3, 2015 at 12:47

answered Oct 3, 2015 at 12:08

MatthewD

6,8215 gold badges24 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

paul bica Over a year ago

This will also allow the removal of On Error GoTo Error and the improper use of the Error keyword as a label

Harley B · Accepted Answer · 2015-10-03 15:07:34Z

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.

Collectives™ on Stack Overflow

Error on textbox text change event VBA form

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related