Accessing to target values in vba simultaneously-CodePudding

I have a code what it basically do is: i want to access two triggers or target values and based on their respective values, it perform on action. Ex:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column = 19 And Target.row = 17 Then
        If Target.Value = "Yes" Then
'do something
End if 
End Sub
If Target.Column = 20 And Target.row = 18 Then
        If Target.Value = "Yes" Then
'do something
End if 
End Sub

What this does is, it only checks one trigger value at a time. When trigger1.value changes, it doesn't check trigger2.value However what i really wanted to do is:

if triger1.value = "Yes" and triger2.value = "yes" then 
'' do something
elseif triger1.value = "No" and triger2.value = "yes"
'' do another thing
end if
and so on

my question now is: how can i access the two triggers simultaneously and if the value one trigger is updated, it will check the second trigger value and decide what to do. Any help or suggestion is highly appreciated!

CodePudding user response：

Please remove the first end sub in your macro: it makes your macro stop at that moment and the other lines are not even read anymore.

CodePudding user response：

For complicated checks, you can use a Boolean to store the True/False value, and check that later.

Dim bContinue AS Boolean
bContinue = False

If Target.Column = 19 And Target.row = 17 Then bContinue = True
If Target.Column = 20 And Target.row = 18 Then bContinue = True

If bContinue = True Then
    'If Trigger1...
End If

However, your current code is simple enough that an OR should work:

If (Target.Column = 19 And Target.row = 17) OR (Target.Column = 20 And Target.row = 18) Then

After that, Row 18, Column 20 of the sheet will be Me.Cells(18, 20). Similarly, Row 17, Column 19 will be Me.Cells(17, 19)

This allows you to do this:

If Me.Cells(17, 19).Value = "Yes" And Me.Cells(18,20).Value = "Yes" Then
    'Do Something
'' do something
elseif Me.Cells(17, 19).value = "No" and Me.Cells(18,20).value = "yes"
'' do another thing
end if

However, if the only possible values are "Yes" and "No" (i.e. no "Maybe" or blank) then you can combine things into a Switch Statement with a bit of Binary:

Select Case (IIF(Me.Cells(17, 19).Value = "Yes", 1, 0)   IIF(Me.Cells(18, 20).Value = "Yes", 2, 0))
    Case 0: '0b00
        'Both are "No"
    Case 1: '0b01
        '"Yes" and "No"
    Case 2: '0b10
        '"No" and "Yes"
    Case 3: '0b11
        'Both are "Yes"
    Case Else:
        'Something has gone wrong
End Select

(The Worksheet_Change function is attached to the Worksheet, so you can use the special keyword Me to refer to the Worksheet. If this was code in a Module, then that would not work)

CodePudding user response：

I think you want something like this:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    
    Dim trigger1 As Range, trigger2 As Range
    Set trigger1 = Me.Range("S17")
    Set trigger2 = Me.Range("T18")

    If Not Intersect(Target, Union(trigger1, trigger2)) Is Nothing Then
        If trigger1.Value = "Yes" And trigger2.Value = "Yes" Then
            'do something
        End If
    End If

End Sub

Any time either of the triggers is changed, this will check to see if both triggers are set to "Yes". If they are then the code will run (if you change the "do something" to the necessary functions)