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

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


"David Turner" wrote in message
...
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

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

"David Turner" wrote in message
...
Chip Pearson wrote

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


I was hoping for an IF..OR solution as this would really split
my code.

But if noone else chimes in..., Thanks.

--
David

Chip Pearson wrote

Try, then, something like

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


This works:
If Not Col = "A" And Not Col = "D" Then
MsgBox "Invalid entry", vbOKOnly: Exit Sub
End If

--
David

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


On Sat, 09 Aug 2003 17:41:52 -0700, David Turner wrote:

Chip Pearson wrote

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


I was hoping for an IF..OR solution as this would really split my code.

But if noone else chimes in..., Thanks.

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?

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

I like it.

Thanks for jumping in.

--
David

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..
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"David Turner" wrote in message ...
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?

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

I like it.

Thanks for jumping in.

--
David

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
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =


"David Turner" wrote in message
...
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?

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

I like it.

Thanks for jumping in.

--
David

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.


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.

--
David

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



On Sun, 10 Aug 2003 09:45:24 -0400, "David McRitchie" wrote:

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..
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"David Turner" wrote in message
...
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?

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

I like it.

Thanks for jumping in.

--
David