Issue with a custom vba function (only allow number inputs)

[robertrobert905]

Hi I found a function the internet that works with vba which will only allow a numerical input from a control (textbox for my case)

When i tested the code, I get the popup if I enter a number and then try to delete or backspace it. But if I try to enter a letter I would get the popup twice in a row. How can I fix this problem?

Function OnlyNumbers(ControlNames As Control)
    
   If ControlNames.Value < 0 Or ControlNames.Value > 99999 Then
        ControlNames.Value = ""
        MsgBox ("Invalid input, only numerical input is allowed!")
   End If
End Function

 

[robertrobert905]

I think I know what is going on. If I delete the textbox then the change event is that the textbox has been changed to blank and the numberonly function gets called once. But If I put in a letter, the first change event would call the function and change it to blank, forcing a pop up. The new blank input would force another change event and cause another blank popup

I changed my code to this

So this way it will check for blank inputs first and will ignore the texbox if it's got nothing in there and will not do a popup

If ControlNames.Value = "" Then
   
ElseIf ControlNames.Value < 0 Or ControlNames.Value > 99999 Then
        ControlNames.Value = ""
        MsgBox ("Invalid input, only numerical input (0 to 99999) is allowed!")
    
End If

 

[Norie]

You've posted the function but not the code for the textbox.

Since the problem seems to be with the textbox and not the function it would help to see the textbox code.

PS You could just use IsNumeric to check for a number, and it might be an idea to use a different event eg Exit

 

[robertrobert905]

You've posted the function but not the code for the textbox.

Since the problem seems to be with the textbox and not the function it would help to see the textbox code.

PS You could just use IsNumeric to check for a number, and it might be an idea to use a different event eg Exit

Hi, here is my code for the textbox

Private Sub TextBox45_Change()
Call OnlyNumbers(TextBox45)
Call CheckInput(TextBox45, 58, 62)
End Sub

 

[Norie]

Why are you calling a function like that?

What do you actually want to achieve?

You could easily check that the entry is numeric using the in-built function I mentioned.

Private Sub TextBox45_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    If Not IsNumeric(TextBox45.Value) Then
        Cancel = True
        MsgBox "Please enter a number.", , "Error message"
    End If
End Sub

You could easily add other logic to that, for example to check the number is in a particular range, is an integer etc

 

[robertrobert905]

thanks i didn't know about the built in function. I did a google search of how to get numerical inputs only and I found that function.

I'm just trying to make sure that the user will only enter a number in the textbox, because I'm using the value inputted for a calculation in a different textbox

anyways everything is working now