How to do if "=" multiple values?

Question

I want a message to show if a user attempts to change a cell based on its column title. I could just protect the cells but it would be useful for future knowledge anyway.

Rather than multiple if statements I want something similar to the where in() that is in SQL. Is there a way of doing not in or would you just use an else?

Code that works for one value

Dim ThisColumn as long
ThisColumn=Target.Column
If Cells(1, ThisColumn).Value = "# workers" Then
    Application.EnableEvents = False
    Application.Undo
    Application.EnableEvents = True
    MsgBox "Protected Columns"
    Exit Sub
End If

I want something like

If Cells(1, ThisColumn).Value in("# workers","# of people") Then
    Application.EnableEvents = False
    Application.Undo
    Application.EnableEvents = True
    MsgBox "Protected Columns"
    Exit Sub
End If

Does this answer your question? [Imitating the "IN" Operator](https://stackoverflow.com/questions/1505206/imitating-the-in-operator) — B. Go, Jan 12 '20 at 20:55

score 2 · Answer 1 · answered Jan 12 '20 at 19:51

It looks like you need to use binary logical operators, like AND or OR, as explained here:

AND-operator

OR-operator

In your case, the OR-operator gives you following solution:

if ((Cells(1, ThisColumn).Value="# workers"  )  OR
    (Cells(1, ThisColumn).Value="# of people"))
...

score 1 · Answer 2 · answered Jan 13 '20 at 09:34

many thanks. I did try using the or function but it did not work (unless I needed the brackets around the ifs?. Please note that I have found a solution however and was more what I was looking for as I could define a list:

Dim ColumnTitle
ColumnTitle = Cells(1, ThisColumn).Value  
Select Case ColumnTitle    
Case "# Workers", "Assumption numbers used for costings"   ' List of Column Titles.

    Cells(ThisRow, ThisColumn).Select

Case Else    ' Other values.
    Application.EnableEvents = False
    Application.Undo
    Application.EnableEvents = True
    MsgBox "Protected Columns."

End Select

Dang D. Khanh · Answer 3 · 2020-01-13T12:10:41.860

You can use instr, please read: This

Ex:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Yourvalue As Variant
Dim ThisColumn as long
ThisColumn=Target.Column

Yourvalue = Array("# workers", "# of people", "# ...")
If IsInArray(Cells(1, ThisColumn).Value, Yourvalue) Then
        Application.EnableEvents = False
        Application.Undo
        Application.EnableEvents = True
        MsgBox "Protected Columns"
    Exit Sub
    End If
'Continue Code

End Sub

Function IsInArray(stringToBeFound As String, arr As Variant) As Integer
IsInArray = InStr(Join(arr, ""), stringToBeFound)
End Function

score 0 · Answer 4 · answered Jan 13 '20 at 13:20

You can use a Dictionary and it's built-in method Exists

Dim RestrictedColumns As Object
Set RestrictedColumns = CreateObject("Scripting.Dictionary")
With RestrictedColumns
    .Add "# workers", "# workers"
    .Add "# of people", "# of people"
End With
Dim ThisColumn As Long
ThisColumn=Target.Column
If RestrictedColumns.Exists(Cells(1, ThisColumn).Value) Then
    With Application
        .EnableEvents = False
        .Undo
        .EnableEvents = True
    End With
    MsgBox "Protected Columns"
    Exit Sub
End If

How to do if "=" multiple values?

4 Answers4