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

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


On Nov 6, 10:48*am, ArthurJ <Arth...@discussions.microsoft.com> wrote:
> 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

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


On Nov 6, 11:09*am, Susan <bogenex...@aol.com> wrote:
> 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)
>
> '.............................newcoding................................
> * * 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
>
> On Nov 6, 10:48*am, ArthurJ <Arth...@discussions.microsoft.com> wrote:
>
>
>
> > 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- Hide quoted text -
>
> - Show quoted text -

"Susan" wrote:

> 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
> susan
>
>
> On Nov 6, 11:09 am, Susan <bogenex...@aol.com> wrote:
> > 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)
> >
> > '.............................newcoding................................
> > 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
> >
> > On Nov 6, 10:48 am, ArthurJ <Arth...@discussions.microsoft.com> wrote:
> >
> >
> >
> > > 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- Hide quoted text -
> >
> > - Show quoted text -
>
>

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

--
jb


"ArthurJ" wrote:

> 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

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.



ArthurJ wrote:
>
> 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

--

Dave Peterson

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
--
jb


"john" wrote:

> 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
>
> --
> jb
>
>
> "ArthurJ" wrote:
>
> > 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