Can someone tell me how I can shorten the below "Or" statement without
repeating the entire string?

If (frmMaintest.CBox1.Text = "bob") Or (frmMaintest.CBox1.Text = "george")
Or (frmMaintest.CBox1.Text = "kevin") Then
MsgBox "You must select another name!"
Exit Sub
ElseIf (frmMaintest.CBox1.Text = "john") Or (frmMaintest.CBox1.Text = "tom")
Then
MsgBox "This name is acceptable!"
End If

On Thu, 4 Sep 2003 10:15:32 -0400, "Bob C" wrote in
microsoft.public.excel.programming:

Can someone tell me how I can shorten the below "Or" statement without
repeating the entire string?

If (frmMaintest.CBox1.Text = "bob") Or (frmMaintest.CBox1.Text = "george")
Or (frmMaintest.CBox1.Text = "kevin") Then
MsgBox "You must select another name!"
Exit Sub
ElseIf (frmMaintest.CBox1.Text = "john") Or (frmMaintest.CBox1.Text = "tom")
Then
MsgBox "This name is acceptable!"
End If

Maybe I don't quite understand the problem, but to me the two obvious
ways of shortening the code (and make it faster, too), seem to be:

With frmMaintest.CBox1
If (.Text = "bob") Or (.Text = "george") _
Or (.Text = "kevin") Then
MsgBox "You must select another name!"
Exit Sub
ElseIf (.Text = "john") Or (.Text = "tom") Then
MsgBox "This name is acceptable!"
End If
End With

or

sTmp=frmMaintest.CBox1.Text
If (sTmp = "bob") Or (sTmp = "george") _
Or (sTmp = "kevin") Then
MsgBox "You must select another name!"
Exit Sub
ElseIf (sTmp = "john") Or (sTmp = "tom") Then
MsgBox "This name is acceptable!"
End If

--
Michael Bednarek, IT Manager, TGM Ltd, Brisbane, Australia
http://mcmbednarek.tripod.com/ "POST NO BILLS"

If instr(1,frmMaintest.CBox1.Text,"bobgeorgekevin" then
MsgBox "You must select another name!"
Exit Sub
ElseIf instr(1,frmMaintest.CBox1.Text,"johntom" Then
MsgBox "This name is acceptable!"
End If

this will work but the amount of code maintnance i see you doing will be
large rather add another column to your combobox and keep "good" or "bad" in
there and test for that

"Bob C" wrote in message
...
Can someone tell me how I can shorten the below "Or" statement without
repeating the entire string?

If (frmMaintest.CBox1.Text = "bob") Or (frmMaintest.CBox1.Text = "george")
Or (frmMaintest.CBox1.Text = "kevin") Then
MsgBox "You must select another name!"
Exit Sub
ElseIf (frmMaintest.CBox1.Text = "john") Or (frmMaintest.CBox1.Text =
"tom")
Then
MsgBox "This name is acceptable!"
End If

Select Case frmMaintest.CBox1.Text
Case "bob", "george", "kevin"
MsgBox "You must select another name!"
Exit Sub
Case "john", "tom"
MsgBox "This name is acceptable!"
End Select


"Bob C" wrote in message
...
Can someone tell me how I can shorten the below "Or" statement without
repeating the entire string?

If (frmMaintest.CBox1.Text = "bob") Or (frmMaintest.CBox1.Text = "george")
Or (frmMaintest.CBox1.Text = "kevin") Then
MsgBox "You must select another name!"
Exit Sub
ElseIf (frmMaintest.CBox1.Text = "john") Or (frmMaintest.CBox1.Text =
"tom")
Then
MsgBox "This name is acceptable!"
End If

On Thu, 4 Sep 2003 16:45:58 +0200, "Reg Besseling"
wrote in
microsoft.public.excel.programming:

If instr(1,frmMaintest.CBox1.Text,"bobgeorgekevin" then
MsgBox "You must select another name!"
Exit Sub
ElseIf instr(1,frmMaintest.CBox1.Text,"johntom" Then
MsgBox "This name is acceptable!"
End If

this will work but the amount of code maintnance i see you doing will be
large rather add another column to your combobox and keep "good" or "bad" in
there and test for that

1. There are some closing parentheses missing.
2. I think you got the order of parameters for "Instr()" wrong: your
example will almost always return 0.
3. "Instr()" returns a Long, not a Boolean; If-statements are supposed
to test for a True/False condition, yours doesn't (although it works).
4. Even with the parameters reversed, your code will also produce
false positives: e.g. for "jo", "ohn", "om", etc.
5. There's a flaw in the logic of the code of the OP; it really
requires only this:

With frmMaintest.CBox1
If (.Text = "john") Or (.Text = "tom") Then
MsgBox "This name is acceptable!"
ElseIf
MsgBox "You must select another name!"
Exit Sub
End If
End With

--
Michael Bednarek, IT Manager, Tactical Global Management
Waterfront Pl, Brisbane 4000, Australia. "POST NO BILLS"
http://mcmbednarek.tripod.com/

1. correct sloppy of me i did not test just typed it up without testing
2. sloppy again appologies to all
3. (although it works). the test can be added
4. yes but i was working off the spec
5. that will work, the request as i read it was to reduce the ammount of
or's in which case Tim Zych's solution od the select case is a easier /
better (?) solution



"Michael Bednarek" wrote in message
...
On Thu, 4 Sep 2003 16:45:58 +0200, "Reg Besseling"
wrote in
microsoft.public.excel.programming:

If instr(1,frmMaintest.CBox1.Text,"bobgeorgekevin" then
MsgBox "You must select another name!"
Exit Sub
ElseIf instr(1,frmMaintest.CBox1.Text,"johntom" Then
MsgBox "This name is acceptable!"
End If

this will work but the amount of code maintnance i see you doing will be
large rather add another column to your combobox and keep "good" or "bad"
in
there and test for that

1. There are some closing parentheses missing.
2. I think you got the order of parameters for "Instr()" wrong: your
example will almost always return 0.
3. "Instr()" returns a Long, not a Boolean; If-statements are supposed
to test for a True/False condition, yours doesn't (although it works).
4. Even with the parameters reversed, your code will also produce
false positives: e.g. for "jo", "ohn", "om", etc.
5. There's a flaw in the logic of the code of the OP; it really
requires only this:

With frmMaintest.CBox1
If (.Text = "john") Or (.Text = "tom") Then
MsgBox "This name is acceptable!"
ElseIf
MsgBox "You must select another name!"
Exit Sub
End If
End With

--
Michael Bednarek, IT Manager, Tactical Global Management
Waterfront Pl, Brisbane 4000, Australia. "POST NO BILLS"
http://mcmbednarek.tripod.com/