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
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
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/
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
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,
Rob
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.
Thaks .
This Code Is Really Helpful For Me.
I am trying to create a performance model. I am using vb were we input values. I want it so that when i close the vb screen the excel sheets also should close
How can I validate that a whole, positive number is entered into an InputBox? No decimals, no letters, no blanks…else it displays an msgbox error and loops back to the beginning.
I keep getting an error.
Thanks
This is an answer to Paul’s question, but he probably will not see it because of the time that has passed since he asked it; however, I think others reading this thread in the future will find this answer interesting. Paul wanted to know how to insure the date inputted by the user was in 06.09.2006 format and not in any other date format. Personally, I can’t think of a good reason for wanting this as Paul could always take a valid date and format it anyway he wants afterwards; however, to answer his question…
If DateIn Like “##.##.####” Then
MsgBox “Thank you… that is the correct format”
Else
MsgBox “Incorrect format”
End If
The Like operator can provide for some very powerful testing and is worth adding to your programming arsenol. I would note, though, while the above does guarantee the format is correct, it does not test to see if the inputted numbers make up a valid date… that would have to be a separate test.
@Ryan
Here is a function that should help you out as it determines whether the value passed into it is made up of only digits or not…
Function IsDigitsOnly(Value As String) As Boolean
IsDigitsOnly = Len(Value) > 0 And Not Value Like “*[!0-9]*”
End Function