I used the following code in my module. I'm actually not going to set the
range value until later in the code so I remarked it out. My problem is that
if user leaves the changelevel blank an error box appears saying that,"the
formula you typed contains an error". In this code I just need the user to
be forced to type an integer, cannot leave it blank and cannot type text. If
user types text or leaves the box blank I need msgbox("you must type a
number"). What am I missing? Thanks.

Dim Question3 As String, Title3 As String
Dim ChangeLevel As Variant

Const DftInt As Integer = 0
Const NumbersOnly As Long = 1

Question3 = "What is the Change Level?"
Title3 = "Change Level"
NeedChangeLevel:
ChangeLevel = Application.InputBox(Question3, Title3, DftInt, , , , ,
NumbersOnly)
If ChangeLevel = False Then
Exit Sub
End If
'See if the result can coerced into an Int
On Error Resume Next
'Range("E5").Value = CInt(ChangeLevel)
If Err.Number < 0 Then
MsgBox "Must be a number"
GoTo NeedChangeLevel
' Range("E5").Value = CVErr(xlErrNum)
End If
On Error GoTo 0

Const DftInt As Integer = 0
Const NumbersOnly As Long = 1
Dim cnt as Long, vChangeLevel as Variant

Question3 = "What is the Change Level? [Mandatory]"
Title3 = "Change Level"
do
vChangeLevel = Application.InputBox(Question3, Title3, DftInt,type:=1)
cnt = cnt + 1
if cnt 4 then exit sub
Loop while typename(vChangeLevel) = "Boolean"
ChangeLevel = cint(vChangeLevel)

--
Regards,
tom Ogilvy


"Alex" wrote:

I used the following code in my module. I'm actually not going to set the
range value until later in the code so I remarked it out. My problem is that
if user leaves the changelevel blank an error box appears saying that,"the
formula you typed contains an error". In this code I just need the user to
be forced to type an integer, cannot leave it blank and cannot type text. If
user types text or leaves the box blank I need msgbox("you must type a
number"). What am I missing? Thanks.

Dim Question3 As String, Title3 As String
Dim ChangeLevel As Variant

Const DftInt As Integer = 0
Const NumbersOnly As Long = 1

Question3 = "What is the Change Level?"
Title3 = "Change Level"
NeedChangeLevel:
ChangeLevel = Application.InputBox(Question3, Title3, DftInt, , , , ,
NumbersOnly)
If ChangeLevel = False Then
Exit Sub
End If
'See if the result can coerced into an Int
On Error Resume Next
'Range("E5").Value = CInt(ChangeLevel)
If Err.Number < 0 Then
MsgBox "Must be a number"
GoTo NeedChangeLevel
' Range("E5").Value = CVErr(xlErrNum)
End If
On Error GoTo 0

I've tried different variations of this and if I leave the box blank, I still
get an error message that says,"the formula you typed contains an error".
I'd be OK if it said,"number not valid", but not sure why I'm getting the
error message. Thanks for your help.

"Tom Ogilvy" wrote:

Const DftInt As Integer = 0
Const NumbersOnly As Long = 1
Dim cnt as Long, vChangeLevel as Variant

Question3 = "What is the Change Level? [Mandatory]"
Title3 = "Change Level"
do
vChangeLevel = Application.InputBox(Question3, Title3, DftInt,type:=1)
cnt = cnt + 1
if cnt 4 then exit sub
Loop while typename(vChangeLevel) = "Boolean"
ChangeLevel = cint(vChangeLevel)

--
Regards,
tom Ogilvy


"Alex" wrote:

I used the following code in my module. I'm actually not going to set the
range value until later in the code so I remarked it out. My problem is that
if user leaves the changelevel blank an error box appears saying that,"the
formula you typed contains an error". In this code I just need the user to
be forced to type an integer, cannot leave it blank and cannot type text. If
user types text or leaves the box blank I need msgbox("you must type a
number"). What am I missing? Thanks.

Dim Question3 As String, Title3 As String
Dim ChangeLevel As Variant

Const DftInt As Integer = 0
Const NumbersOnly As Long = 1

Question3 = "What is the Change Level?"
Title3 = "Change Level"
NeedChangeLevel:
ChangeLevel = Application.InputBox(Question3, Title3, DftInt, , , , ,
NumbersOnly)
If ChangeLevel = False Then
Exit Sub
End If
'See if the result can coerced into an Int
On Error Resume Next
'Range("E5").Value = CInt(ChangeLevel)
If Err.Number < 0 Then
MsgBox "Must be a number"
GoTo NeedChangeLevel
' Range("E5").Value = CVErr(xlErrNum)
End If
On Error GoTo 0

When you use application.inputbox and type:=1, the user has to enter something
that looks like a number (or hit cancel).

That application.dialog won't let your code (or the user) continue until one of
those things is met (a number or a click on cancel).

Maybe just changing the message to something like:
Question3 = "What is the Change Level? [Mandatory]" _
& vbLf & "Please enter a number"

would make it easier for the user to know what is expected.

Alex wrote:

I've tried different variations of this and if I leave the box blank, I still
get an error message that says,"the formula you typed contains an error".
I'd be OK if it said,"number not valid", but not sure why I'm getting the
error message. Thanks for your help.

"Tom Ogilvy" wrote:

Const DftInt As Integer = 0
Const NumbersOnly As Long = 1
Dim cnt as Long, vChangeLevel as Variant

Question3 = "What is the Change Level? [Mandatory]"
Title3 = "Change Level"
do
vChangeLevel = Application.InputBox(Question3, Title3, DftInt,type:=1)
cnt = cnt + 1
if cnt 4 then exit sub
Loop while typename(vChangeLevel) = "Boolean"
ChangeLevel = cint(vChangeLevel)

--
Regards,
tom Ogilvy


"Alex" wrote:

I used the following code in my module. I'm actually not going to set the
range value until later in the code so I remarked it out. My problem is that
if user leaves the changelevel blank an error box appears saying that,"the
formula you typed contains an error". In this code I just need the user to
be forced to type an integer, cannot leave it blank and cannot type text. If
user types text or leaves the box blank I need msgbox("you must type a
number"). What am I missing? Thanks.

Dim Question3 As String, Title3 As String
Dim ChangeLevel As Variant

Const DftInt As Integer = 0
Const NumbersOnly As Long = 1

Question3 = "What is the Change Level?"
Title3 = "Change Level"
NeedChangeLevel:
ChangeLevel = Application.InputBox(Question3, Title3, DftInt, , , , ,
NumbersOnly)
If ChangeLevel = False Then
Exit Sub
End If
'See if the result can coerced into an Int
On Error Resume Next
'Range("E5").Value = CInt(ChangeLevel)
If Err.Number < 0 Then
MsgBox "Must be a number"
GoTo NeedChangeLevel
' Range("E5").Value = CVErr(xlErrNum)
End If
On Error GoTo 0



--

Dave Peterson