CODEWITHSUNDEEP
excelvba
DiPi
DiPi

Reputation: 21

VBA code to display message box if value in another cell contains specific text

I am new to VBA...looking for code that will only allow me to enter a value in a column if the value in one or more of the three cells immediately to the left "contains" the word "Other". I've successfully written the code so that if the value in one or more of the cells is "Other" I am restricted from entering a value, but have not been successful in using ISERROR and FIND so that the code looks for text that includes "other". Here is what I have right now...

If Target.Column = 15 And Target <> "" Then
    If Cells(Target.Row, Target.Column - 1).Value <> "Other" _
        Or Cells(Target.Row, Target.Column - 2).Value <> "Other" _
        Or Cells(Target.Row, Target.Column - 3).Value <> "Other" _
        Then

        Target.Value = ""
        MsgBox "First Select 'Other' value in one or more of the 'Excluded Employee' Columns to the left"
        Exit Sub
   End If
End If
exitHandler:
  Application.EnableEvents = True

End Sub

Any suggestions would be most appreciated!

Upvotes: 0

Views: 1262

Answers (2)

brettdj
brettdj

Reputation: 55702

You can use COUNTIF with a wildcard to look for at least once cell containing other, i.e.:

If target.Column = 15 And target.Value <> "" Then
 If Application.WorksheetFunction.CountIf(target.Offset(0, -3).Resize(1, 3), "*other*") = 0 Then
        target.Value = ""
        MsgBox "First Select 'Other' value in one or more of the 'Excluded Employee' Columns to the left"
        Exit Sub
 End If
End If

Upvotes: 0

Abe Gold
Abe Gold

Reputation: 2357

If Target.Column = 15 And Target <> "" Then
    If InStr(1, Cells(Target.Row, Target.Column - 1).Value, "Other") = 0 _
        And InStr(1, Cells(Target.Row, Target.Column - 2).Value, "Other") = 0 _
        And InStr(1, Cells(Target.Row, Target.Column - 3).Value, "Other") = 0 _
        Then

        Target.Value = ""
        MsgBox "First Select 'Other' value in one or more of the 'Excluded Employee' Columns to the left"
        Exit Sub
   End If
End If
exitHandler:
  Application.EnableEvents = True
End Sub

Upvotes: 1

Related Questions