Force Integer in Inputbox

[Guest]

I have the following code that inputs a value into a cell that is typed in an
input box. How can I force the number that is typed in the input box to be
an integer? Thanks.

Sub Input_Fields()

Dim Question3, Title3, Default3, Box3
Dim ChangeLevel As Integer
Question3 = "What is the Change Level?"
Title3 = "Change Level"
Default3 = ""
ChangeLevel = InputBox(Question3, Title3, Default3)


Range("e5").Select
ActiveCell.FormulaR1C1 = ChangeLevel


End Sub

 

[NickHK]

Alex,
If you really mean an Integer (a whole number between -32,768 to 32,767) use
this, otherwise change accordingly for Long, Double etc :

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"

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 "Out of range"
Range("E5").Value = CVErr(xlErrNum)
End If
On Error GoTo 0

NickHK

 

[Guest]

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

 

[Guest]

Perhaps something like this using InputBox (rather than Application.InputBox)

Sub Input_Fields()

Dim Question3, Title3, Default3, Box3
Dim ChangeLevel As String
Question3 = "What is the Change Level? Enter an integer number."
Title3 = "Change Level"
Default3 = ""

Dim longNum As Long
Dim dif As Double

On Error GoTo reset

retry:
ChangeLevel = InputBox(Question3, Title3, Default3)

If StrPtr(ChangeLevel) = 0 Then ' Cancel
MsgBox "Cancel not allowed. Entry is mandatory."
GoTo retry
'or
'Exit Sub

Else

If ChangeLevel = "" Then ' Blank
MsgBox "Blank not allowed. Entry is mandatory."
GoTo retry
Else
longNum = CLng(ChangeLevel)
dif = ChangeLevel - longNum

If dif <> 0 Then
MsgBox "Integer entry only."
GoTo retry
End If
End If

End If

Range("E5").Value = ChangeLevel

Exit Sub

reset:
MsgBox "You may have entered text or the number is too large."
Resume retry

End Sub

 

[Guest]

try this:

ChangeLevel = InputBox(Question3, Title3, Default3 , , , , 1)
the comma's denote the location of the box left, right, help, help context,
type
type 1 is numeric input only and it will automatically display an error
window.

 

[Chip Pearson]

ChangeLevel = InputBox(Question3, Title3, Default3 , , , , 1)

Nope. You missed it. First of all, you need to prefix InputBox with
Application so you call Excel's InputBox function, not VBA's InputBox. You
also need to test whether the user clicked Cancel and take appropriate
action.

Rather than using a string of commas for positional parameter notation, it
is MUCH better to used named arguments:

Dim ChangeLevel As Variant
ChangeLevel = Application.InputBox(Prompt:=Question3, _
Title:=Title3, Default:=Default3, Type:=1)
If ChangeLevel = False Then
Debug.Print "User clicked cancel"
Else
Debug.Print "User choose: " & CDbl(ChangeLevel)
End If


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)