test for "A" or "D"

  • Thread starter Thread starter David Turner
  • Start date Start date
D

David Turner

Using XL2000, I'm trying to validate an InputBox entry:

Col = UCase(InputBox("Column for New Member? A or D", "COLUMN", "A"))
If Col = "" Then Exit Sub

This gives a strong enough hint to the novice user, but I want to make it
foolproof and test for "A" OR "D"

How?
 
David,

Try something like

Dim S As String
S = InputBox("Enter A or D")
Select Case UCase(S)
Case "A", "D"
MsgBox "entry OK"
Case Else
MsgBox "entry invalid"
End Select


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com (e-mail address removed)
 
David,

Try, then, something like

If S = "A" Or S = "D" Then
' valid entry
Else
' invalid entry
End If


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com (e-mail address removed)
 
Chip Pearson wrote
Try, then, something like

If S = "A" Or S = "D" Then
' valid entry
Else
' invalid entry
End If
Click to expand...

This works:
If Not Col = "A" And Not Col = "D" Then
MsgBox "Invalid entry", vbOKOnly: Exit Sub
End If
 
I don't understand what you mean by "split my code". How does the Select Case block differ from
an If/End if block in this situation?

BTW, you could also write that as

If Not (S = "A" or S = "D") Then

or

If Len(S) <> 1 Or Instr("AD",S) = 0 Then
 
Myrna Larson wrote
I don't understand what you mean by "split my code". How does the
Select Case block differ from an If/End if block in this situation?
Click to expand...

All code following a valid Column entry would take the place of Chip's
MsgBox "entry OK" and the Case Else would follow that. Not True?
If Not (S = "A" or S = "D") Then
Click to expand...

I like it.

Thanks for jumping in.
 
I don't understand the explanation, but I think by splitting the
code David meant that he can see more of the other code
on the screen with less scrolling when fewer lines are used
for the test. Why else would one want to test for a negative
(NOT) conditions when positives are generally easier to read
and less confusing..
 
I am not sure, but if one enters an invalid character by accident, would you
want them to try again, or exit the Sub by default? As an general idea,
perhaps you could loop until they enter a valid character. Again, just an
thought.

Sub Demo()
Dim S As String
Dim Ok As Boolean
Ok = False

Do
S = UCase(InputBox("Enter A or D (Blank to exit)"))
Select Case S
Case "A", "D"
Ok = True
MsgBox "entry OK"
Case vbNullString
Ok = True
MsgBox "Blank...about to exit sub"
Case Else
Ok = False
MsgBox "Innvalid: Enter A, D, or Blank to exit"
End Select
Loop While Not Ok

If S = vbNullString Then Exit Sub

' Continue with code. S = A or D
'....
End Sub
 
Dana DeLouis wrote
I am not sure, but if one enters an invalid character by accident,
would you want them to try again, or exit the Sub by default? As an
general idea, perhaps you could loop until they enter a valid
character. Again, just an thought.
Click to expand...

Yes, I've opted to exit by default. The most common user error that occurs
is that they start typing a name to Add or Remove from one of the columns
since that is the prompt that follows the column choice.

Interesting idea to loop until valid entry is made, though. I'm just too
fanatical about keeping number of lines of code in a module to a minimum.
 
If you object to the Not, you have a couple of other options:

If S = "A" or S = "D" Then
'leave empty or put there the code that would
'ordinarily follow the End If
Else
'error message
Exit Sub
End If

If S <> "A" and S <> "D" Then
 
Back
Top