data validation NOT equal to a word

[michaelsmithgall]

i want to allow any word to be entered except one. i am also using a diffent cell for the data validation.

example

a1= "any customer name"
a2= data validation that essentially says A1 may contain any word as long its its not equal to "Superman"

i dont really have anything agains Superman, thats just an example. if they were to enter superman in this example they would get one of the standard validation msg boxes.

i tried entering in the A2 validation box a custom validation =A1<>"Superman" but that didnt work. it popped the msg on everything

 

[Jonmo1]

If you want to prevent "Superman" from being entered into A1, then the validation rule goes in A1.

 

[stnkynts]

Something like this in Worksheet Change event

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
    If Range("A1").Value = "Superman" Or Range("A1").Value = "superman" Then
        MsgBox ("Cell A1 cannot be superman please enter something else")
        Range("A1").ClearContents
    End If
End If
 
End Sub

 

[michaelsmithgall]

Stnkynts - i cant seem to get it to work. it looks logical but im missing something. im using the superman name and nothing happens. this is exactly what i am using which i think only has the cell reference changed.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$c$4" Then
If Range("c4").Value = "Superman" Or Range("c4").Value = "superman" Then
MsgBox ("Cell A1 cannot be superman please enter something else")
Range("c4").ClearContents
End If
End If

End Sub

 

[Jonmo1]

Is C4 a Formula?

 

[shg]

String comparisons in VBA are case-sensitive by default.

If Target.Address = "$[COLOR=red][B]C[/B][/COLOR]$4" Then

 

[michaelsmithgall]

Shg- that was it. I am very lazy about caps. And didnt realize it made a difference. thanks so much for the help.