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

robertrobert905

Board Regular
Joined
Jun 27, 2008
Messages
139
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?


Code:
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
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

robertrobert905

Board Regular
Joined
Jun 27, 2008
Messages
139
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

Code:
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
 
Last edited:

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,219
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Jun 27, 2008
Messages
139
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

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

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,219
Office Version
  1. 365
Platform
  1. Windows
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.
Code:
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

Board Regular
Joined
Jun 27, 2008
Messages
139
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,079
Messages
5,599,636
Members
414,326
Latest member
kfg1287

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top