Excel VBA: Using OR with <>

[zero269]

Hello,

I'm wondering if it's possible to use the OR statement with Not Equal To (<>) in VBA.

I'm trying to simplify the user selection from a MsgBox to validate the entries and then Exit Sub if no valid matches were found, Else proceed with running the Macro.

Here's what I'm working with:
Note: I'm using a list of Student ID's, but I just wanted to test with some names to simplify the testing for now…

Sub GetStudentName()[/FONT]
 
[FONT=Segoe UI]'Get Name
Dim Student As String
   
Student = Application.InputBox( _
"Enter Students Name to assign new books." & vbCrLf & _
"Click Cancel or leave blank to cancel.", "Copy New Books for...", "Enter student name")
   
Student = Application.WorksheetFunction.Proper(Student) 'Convert input to Propercase
   
'Validate Name
If Student <> "John" Or Student <> "Jane" Or cName = vbNullString Then
MsgBox "You selected Cancel, or entered an invalid name."
Exit Sub
Else
MsgBox "You entered " & Student
End If[/FONT]
 
[FONT=Segoe UI]End Sub[/FONT]

If I enter a valid name, or an invalid name, or hit Cancel... all result with an invalid or canceled entry response.

Any help would be greatly appreciated…

 

[Cubist]

This condition will always be evaluated as true because any value for Student will either not be "John" or not be "Jane". Change it to AND.

If (Student <> "John" And Student <> "Jane") Or cName = vbNullString Then

 

[zero269]

This condition will always be evaluated as true because any value for Student will either not be "John" or not be "Jane". Change it to AND.

If (Student <> "John" And Student <> "Jane") Or cName = vbNullString Then

Thanks Cubist,

This shows how fried my brain is right now working with VBA. This makes perfect sense now.

My tests show valid entries are recongnized as valid entries.
Hitting Cancel or entering an invalid name returns the correct response.

Thank you very much!

 

[rlv01]

Sometimes when you have a list like your list of names to exclude, the Select Case statement is a better bet that trying to do it with If statements. It is more easiliy expandable if the list changes.

Sub GetStudentName()
'Get Name
    Dim Student As String

    With Application
        Student = .Proper(.Trim(.InputBox("Enter Students Name to assign new books." & vbCrLf & _
                                          "Click Cancel or leave blank to cancel.", "Copy New Books for...", "Enter student name")))
    End With

    'Validate Name
    Select Case Student
    Case "John", "Jane", "Paul", "George", "Ringo"    'invalid student
        MsgBox "You entered an invalid name."
        Exit Sub
    Case "False", vbNullString
        MsgBox "You selected Cancel."
        Exit Sub
    End Select

    MsgBox "You entered " & Student
End Sub