Application.InputBox usage

[ArthurJ]

I want the user to input a number. I want to handle the three possible
situations:
1. User enters number
2. User clicks Cancel
3. User enters nothing and clicks OK

I am having trouble with the third possibility, where the user enters nothing.

Below is some of the code I have now:

Sub GetNumber
Dim myNum As Variant
On Error Resume Next 'This line seems to have no effect.
myNum = Application.InputBox(Prompt:="Enter a number", Type:=1)
'If user enters nothing and clicks OK, Excel generates an info box about
'entering an incorrect formula. But it is NOT a VBA error so cannot be
trapped
'with normal methods.

If myNum = False Then
MsgBox ("Cancel was chosen. Macro will end.")
Exit Sub
Else
MsgBox (myNum)
End If
End Sub

Thank you,
Art

 

[Susan]

Sub GetNumber
Dim myNum As Variant
On Error Resume Next 'This line seems to have no effect.
myNum = Application.InputBox(Prompt:="Enter a number", Type:=1)

'.............................new
coding................................
if myNum = "" then
msgbox "Please enter a number in the input box."
exit sub
end if
'.............................................................................

'If user enters nothing and clicks OK, Excel generates an info box
about
'entering an incorrect formula. But it is NOT a VBA error so
cannot be
trapped
'with normal methods.


If myNum = False Then
MsgBox ("Cancel was chosen. Macro will end.")
Exit Sub
Else
MsgBox (myNum)
End If
End Sub


try that.

susan

 

[Susan]

nope.......... should have tested it before i posted it. still
triggers that excel error message. tried putting
Application.DisplayAlerts=false in there.... but that didn't help
because it got rid of the excel error message but didn't let the macro
continue.
sorry!
susan

 

[ArthurJ]

nope.......... should have tested it before i posted it. still
triggers that excel error message. tried putting
Application.DisplayAlerts=false in there.... but that didn't help
because it got rid of the excel error message but didn't let the macro
continue.
sorry!

Susan, that's Ok! Same problem that tripped me up. Maybe I need to use one
of the other input boxes, not Application.InputBox.

Art

 

[john]

this may do what you are looking for:

Sub GetNumber()
Dim myNum As Variant

Application.DisplayAlerts = False

myNum = Application.InputBox(Prompt:="Enter A Number", Title:="Enter A
Number", Type:=1)

If VarType(myNum) = vbBoolean Then
If myNum = False Then
Debug.Print "cancelled"

MsgBox ("Cancel was chosen. Macro will end.")


Else

MsgBox myNum

End If

End If

Application.DisplayAlerts = True

End Sub

 

[Dave Peterson]

By using application.inputbox and type:=1, you're telling excel that they have
to enter a number. This means that your code doesn't have to do anything to
check to see if the entry is a number.

If you don't want that warning message, then you can use:

myNum = inputbox(Prompt:="enter a number")

But now all the validation will be your responsibility.

 

[john]

typed it too fast! Put the MsgBox myNum in wrong place!!

Sub GetNumber()
Dim myNum As Variant

Application.DisplayAlerts = False

myNum = Application.InputBox(Prompt:="Enter A Number", Title:="Enter A
Number", Type:=1)

If VarType(myNum) = vbBoolean Then

If myNum = False Then
Debug.Print "cancelled"

MsgBox ("Cancel was chosen. Macro will end.")


End If

Else

MsgBox myNum

End If



Application.DisplayAlerts = True

End Sub