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

B. Go
– B. Go

2020-01-12 20:55:10 +00:00
Commented Jan 12, 2020 at 20:55 — B. Go
– B. Go, Commented Jan 12, 2020 at 20:55

Dominique · Accepted Answer · 2020-01-12 19:51:15Z

2

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"))
...

answered Jan 12, 2020 at 19:51

Dominique

17.7k20 gold badges81 silver badges161 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Alistair Cabral · Accepted Answer · 2020-01-13 09:34:51Z

1

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

answered Jan 13, 2020 at 9:34

Alistair Cabral

114 bronze badges

Comments

Dang D. Khanh · Accepted Answer · 2020-01-13 12:10:41Z

1

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

edited Jan 13, 2020 at 12:10

answered Jan 13, 2020 at 9:48

Dang D. Khanh

1,4618 silver badges13 bronze badges

Comments

AntiDrondert · Accepted Answer · 2020-01-13 13:20:18Z

0

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

answered Jan 13, 2020 at 13:20

AntiDrondert

1,1498 silver badges21 bronze badges

Collectives™ on Stack Overflow

How to do if "=" multiple values?

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related