Inputbox values control

  • Thread starter Thread starter Peter
  • Start date Start date
P

Peter

Hi evrybody,
I need your help on the following issue:
I would like to enter a value in one of my forms by prompting the user to
enter this value in an inputbox. This value must be a number, so i need a way
to control the datatype of the values that are entered in the inputbox, i.e.
to restrict the user from entering a letter instead of a number.
 
Peter said:
Hi evrybody,
I need your help on the following issue:
I would like to enter a value in one of my forms by prompting the user to
enter this value in an inputbox. This value must be a number, so i need a way
to control the datatype of the values that are entered in the inputbox, i.e.
to restrict the user from entering a letter instead of a number.
Click to expand...

Try this:

Sub InputValidation()

Dim bOK As Boolean, ans As String
Dim i As Long, sChar As String
Dim msg As String

bOK = False
msg = "Enter a number"

Do While Not bOK
ans = InputBox(msg)
bOK = True
msg = msg & vbNewLine & vbNewLine & _
"Numbers only Please."
For i = 1 To Len(ans)
sChar = Mid(ans, i, 1)
If (Asc(sChar) < 48 Or Asc(sChar) > 57) Then
bOK = False
Exit For
End If
Next
Loop
MsgBox ans & " is OK"

End Sub
 
Revised to catch cancel and empty strings

Public Function InputWithValidation() As String

Dim bOK As Boolean, sAns As String
Dim i As Long, sChar As String
Dim sMsg As String

bOK = False
sMsg = "Enter a number"

Do While Not bOK
sAns = InputBox(sMsg)

If StrPtr(sAns) = 0 Then
Debug.Print "user pressed cancel"
Exit Function
ElseIf Len(sAns) = 0 Then
Debug.Print "user pressed Okay, zero length string"
Exit Function
Else
bOK = True
sMsg = "Numbers only Please."
For i = 1 To Len(sAns)
sChar = Mid$(sAns, i, 1)
If (Asc(sChar) < 48 Or Asc(sChar) > 57) Then
bOK = False
Exit For
End If
Next
End If
Loop

InputWithValidation = sAns
Debug.Print sAns

End Function
 
Peter said:
Hi evrybody,
I need your help on the following issue:
I would like to enter a value in one of my forms by prompting the user to
enter this value in an inputbox. This value must be a number, so i need a
way
to control the datatype of the values that are entered in the inputbox,
i.e.
to restrict the user from entering a letter instead of a number.
Click to expand...

Inputbox can't be controlled in that fashion, but here's how I do it using a
form's control, by disallowing the numeric keypresses:

Public Function KeyNonNumeric(KeyAscii As Integer)
' Called from keypress event of an unbound textbox
' Restricts the keypresses to non-numeric chars only
'
' Syntax: KeyNonNumeric KeyAscii

Select Case KeyAscii
Case 8, 9, 13, 27 'Backspace, Tab, Enter, Esc
Exit Function
End Select
Select Case KeyAscii
Case 48 To 57
Beep
KeyAscii = 0
End Select
End Function

Paste that function into a standard module (don't name the module
KeyNonNumeric), then call it from a textbox's KeyPress event, like this:

KeyNonNumeric KeyAscii
 
Back
Top