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
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

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,454
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,454
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.
 

Forum statistics

Threads
1,141,160
Messages
5,704,649
Members
421,360
Latest member
Rhodia

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