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 12:48

0

You can use two Application methods called .Match and .Count. We can tell the Match to check a 1D-array against a value. It will return a numeric value if found, and an error if not. Then .Count will ignore these error and will return the total count of found values. So the technique is for example:

Dim ThisColumn As Long: ThisColumn = Target.Column
Dim arr As Variant: arr = Array("# workers", "# of people")

With Application
    If .Count(.Match(arr, Cells(1, ThisColumn), 0)) > 0 Then
        .EnableEvents = False
        .Undo
        .EnableEvents = True
        MsgBox "Protected Columns"
        Exit Sub
    Else
        'Do something else if need be...
    End If
End With

Now you can add to arr whichever value you would want to check against.

answered Jan 13 '20 at 12:48

JvdV

53,146
6
36
60

I think this is closest but when I try the below nothing happens. With Application If .Count(.Match(arr, Cells(1, ThisColumn), 0)) > 0 Then Cells(10, 10).Select Else .EnableEvents = False .Undo .EnableEvents = True MsgBox "Protected Columns" Exit Sub End If End With – Alistair Cabral Jan 13 '20 at 14:44
Where did the Cells(10,10) come from? Are you sure EnableEvents is set to true at your point of execution? @AlistairCabral – JvdV Jan 13 '20 at 16:58
Hi, I was just trying to get one action working and then perform it with an else – Alistair Cabral Jan 14 '20 at 08:48
You understand you have now made it such that when it **is** a positive count (e.g. the selected column should be protected) you select `Cells(10,10)`? And if it's not protected you do all these lines you had earlier. @AlistairCabral – JvdV Jan 14 '20 at 08:55

score 0 · Answer 5 · 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?

5 Answers5