Only allow numbers as user input values to Application.InputBox for CommandButton1_Click()

Question

I'm struggling with an annoyning possibility, that users can insert decimal/fraction values although only whole numbers should be allowed. I've tried to use KeyAscii (48-57) to check if the users enter numbers. However, I cannot get it to work and neither with some other functions. The user should not be able to insert anything else than numeric values (from 0 to 9 and up to 99). This applies to "rowNum" in my attached code. I have everyhting else in control but I can't get my VBA to work for decimal/fraction values inserted by users after pressing the CommandButton_Click(). I've tried now for many hours to find a solution without success. All help appreciated!

Private Sub CommandButton1_Click()
Dim rowNum As Variant
Dim x As Variant
On Error Resume Next

rowNum = Application.InputBox(Prompt:="Please enter the row from where you want to add new empty rows downwards:", _
                                    Title:="Need to add new empty rows?", Type:=1)

                If (VarType(rowNum) = vbBoolean) And (rowNum = False) Then
                MsgBox "You canceled the event!", vbExclamation, "No Input!"

                ElseIf rowNum = 0 Then
                MsgBox "Smallest allowed row number is 7!", vbExclamation, "Invalid Input!"

                ElseIf rowNum < 0 Then
                MsgBox "Negative values are not allowed!", vbExclamation, "Invalid Input!"

                ElseIf rowNum < 7 Then 
        MsgBox "Smallest allowed row number is 7!!", vbExclamation, "Invalid Input!"

        ElseIf rowNum > 99 Then 
        MsgBox "Highest allowed row number is 99!", vbExclamation, "Invalid Input!"

                              End If
                              If rowNum < 7 Or rowNum > 99 Then End



x = Application.InputBox(Prompt:="Please insert the amount of empty rows that you need:", _
                            Title:="Amount of new rows?", Type:=1)


                If (VarType(x) = vbBoolean) And (x = False) Then
                MsgBox "You canceled the event!", vbExclamation, "No Input!"

                ElseIf x = 0 Then
                MsgBox "Smallest amount of new rows is 2!", vbExclamation, "Invalid Input!"

                ElseIf x < 2 Then
                MsgBox "Smallest amount of new rows is 2!", vbExclamation, "Invalid Input!"

                ElseIf x > 10 Then
                MsgBox "Highest amount of new rows is 10!", vbExclamation, "Invalid Input!"

                              End If
                              If x < 2 Or x > 10 Then End

Rows(rowNum & ":" & rowNum + x - 1).EntireRow.Insert Shift:=xlDown

End Sub

Hello Nathan, unfortunately I can't get isnumeric to work because it seems to allow commas and points (for instance 8,7 or 12.2) as numbers as well. The user should only be able to input values between 0 and 9 (without any commas or points). — Nik_Learning
– Nik_Learning, Commented Sep 25, 2018 at 10:53

Nathan_Sav · Accepted Answer · 2018-09-25 11:28:12Z

2

adding another elseif clause

ElseIf x <> Int(x) Then
       MsgBox "Fraction number is not  allowed", vbExclamation, "Invalid Input!"


End if

may solve the problem

edited Sep 25, 2018 at 11:28

Nathan_Sav

8,5762 gold badges18 silver badges22 bronze badges

answered Sep 25, 2018 at 11:05

Ahmed AU

2,7772 gold badges9 silver badges15 bronze badges

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

Comments

Kippa2005 · Accepted Answer · 2018-09-25 10:57:46Z

2

declaring as a variant is the issue as that will accept any input type. If you instead declare x and RowNum as integers you will avoid this issue.

Doing this will round any decimals to whole numbers, and text will hit an error (Type 13: Mismatch) This can be handled in an error handler or skipped

edited Sep 25, 2018 at 10:57

answered Sep 25, 2018 at 10:50

Kippa2005

416 bronze badges

2 Comments

Nik_Learning Over a year ago

Thanks for your suggestion but I have declared x and rowNum as variants because of other issues (user selects cancel instead of input value). However, the "ElseIf x <> Int(x) Then" fixed my problem.

Kippa2005 Over a year ago

No worries, I did try doing what Ahmed suggested but mine was saying I was inputting a string, which is why i settled on this one

Collectives™ on Stack Overflow

Only allow numbers as user input values to Application.InputBox for CommandButton1_Click()

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related