Inputbox Validation
Here's a little goodie I find myself re-writing every once in a while.
It's a simple "Here is a question, give me a valid answer" type routine.
Note the use of the infinite Do .. Loop.
Just make sure your exits are covered (Cancel button)
Sub test()
Dim str As String, bln As Boolean
'Set up a default value
str = "Blue"
Do
str = InputBox("Favourite Colour?", "Colour?", str)
'check for Cancel
If str = "" Then
'Cancelled - exit from do loop with failure
bln = False
Exit Do
Else
'Check the entry against valid answers
Select Case LCase(str)
Case "black", "red", "green", "yellow", "blue", "magenta", "cyan", "black"
'Match found - exit from do loop with success
bln = True
Exit Do
Case Else
'oops - not a valid answer
MsgBox "Not a recognised colour", vbExclamation, "Error"
End Select
End If
Loop 'infinite loop
If bln Then
MsgBox "Your favourite colour is " & str
End If
End Sub
Dim str As String, bln As Boolean
'Set up a default value
str = "Blue"
Do
str = InputBox("Favourite Colour?", "Colour?", str)
'check for Cancel
If str = "" Then
'Cancelled - exit from do loop with failure
bln = False
Exit Do
Else
'Check the entry against valid answers
Select Case LCase(str)
Case "black", "red", "green", "yellow", "blue", "magenta", "cyan", "black"
'Match found - exit from do loop with success
bln = True
Exit Do
Case Else
'oops - not a valid answer
MsgBox "Not a recognised colour", vbExclamation, "Error"
End Select
End If
Loop 'infinite loop
If bln Then
MsgBox "Your favourite colour is " & str
End If
End Sub
simon:
what happens if you want to receive a positive number?
can you use a variant as the input variable and cast it into an integer
4 May 2005, 6:40 amJuan Pablo González:
Simon,
It's probably better to use Application.InputBox() when you want to validate for a number.
Look at this for example:
http://www.dicks-blog.com/archives/2004/05/26/validating-an-inputbox/
4 May 2005, 8:10 amRobert Bruce:
I notice that you're checking for cancel by testing whether the inputbox returns a zero length string. What if a zero length string is legitimate input? It turns out that if the user cancels, the string is null rather than zero length, so we can test for that.
Dim strInput As String
strInput = InputBox("do something")
If Len(strInput) = 0 Then
If StrPtr(strInput) = 0 Then
Debug.Print "The user clicked Cancel"
Else
Debug.Print "The user clicked Enter, but typed nothing"
End If
End If
Rob
9 May 2005, 1:49 amRob van Gelder:
Robert,
That's an excellent suggestion and certainly worth using.
Read about StrPtr.
To use in the posted code:
Change: If str = "" Then
To: If StrPtr(str) = 0 Then
Cheers,
9 May 2005, 3:52 pmRob
Paul:
Hello, ive just read this, and i was wondering how you would validate an answer so that if it contained certain characters, then an error message is produced?
for example, i want dates to be entered, but there are so many ways of entering a date, i only want the one way, which is 06.09.2006, i dont want 06/09/2006 or, as many people have been doing it 06_09_2006.
I just want it to check for /'s and _'s and come up saying, incorrect format.
14 July 2006, 8:51 am