IF...Then

allenD

New Member
Joined
Sep 9, 2006
Messages
5
how do I construct a IF... Then statement that displays a msgbox error if anything other than a number is entered into the inputbox? Example:

Sub I_Want_A_Number()

Dim num

num = InputBox("enter number")
IF num is....Then ' This is where I'm stuck at. I don't know _
' how to complete this statement.
msgbox"must enter number"
Else
test_case(num)
end IF

End Sub
--------------------------------------------------------------------------------------

End Sub

Function test_case(num)
Select Case grade
Case 1
MsgBox "number 1"
Case 2, 3
MsgBox "numbers 2 or 3"
Case 4 To 6
MsgBox " numbers 4, 5 or 6"
Case Is > 8
MsgBox "number is above 8"
Case Else
MsgBox "number< is not conditional statement"
End Select

End Function
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
Hi
try
Code:
Sub I_Want_A_Number()

    Dim num
    On Error GoTo Last
    num = Application.InputBox("enter number",type:=1)
    On Error GoTo 0
    
    test_case(num)
Last:
End Sub
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,453
Hello AllenD, welcome to the board.
First off, here's how to write what you're asking.
Code:
Sub I_Want_A_Number()

Dim num  As Long
num = InputBox("enter number")
If Not IsNumeric(num) Then
  MsgBox "must enter number"
Else
  test_case (num)
End If

End Sub

Secondly, you can use Application.Inputbox (type1) to automatically limit the user to
numeric inputs without having to test for them. For example:
Code:
Sub I_Want_A_Number()
Dim num As Long
num = Application.InputBox(Prompt:="Please enter a number only", _
Title:="Numeric Characters Only", Type:=1)
test_case (num)
End Sub
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
HalAce,

It's fully depends on how OP wants to set up though, I don't think
OP wants MsgBox to appear when user hit close button.
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,453
Hi jindon,
I agree. (at least I wouldn't want it to appear then.)
Seems I get the msgbox no matter which of our codes I use.

And I see where I dim'ed num as Long in the first example.
This will generate an error if the user inputs a letter so it is best defined as a variant after all.
 

Watch MrExcel Video

Forum statistics

Threads
1,111,597
Messages
5,541,160
Members
410,543
Latest member
ExcelGlenn
Top