View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.programming
ALEX ALEX is offline
external usenet poster
 
Posts: 493
Default Forcing Integer in InputBox

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