help with Or statement

  • Thread starter Thread starter Bob C
  • Start date Start date
B

Bob C

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
 
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
Click to expand...

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
 
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
 
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
 
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
Click to expand...

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
 
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
 
Back
Top