How to start two VBA Macros at once when different trigger cells are changed via formula?

Question

I'm working on an Excel-Sheet that mostly uses standard formula in cells but has two main VBA macros. These macros need to get triggered by three different cells that change their value automatically by formula.

Imagine it like this:

3 pairs of cells spreaded over the sheet (1 pair = 1 trigger cell + 1 output cell)
1 calculating macro and 1 cleaning macro
If one trigger cell changes to "x", the calculating macro starts and gives an output to the nearby output cell.
While this happens, the other trigger cells go to "" what starts the cleaning (ClearContents) of their output cells.
I thought about cleaning the output cells not needed within the calculation of the needed one, but there is a possibility that all 3 trigger cells go to "". Because of this, I tried to realize the cleaning in a separate macro to avoid having the last output remaining all the time.

I'm working with Excel 2010 on Windows 7 and this is my code so far:

Private Sub Worksheet_Change(ByVal target As Range)
'controls if trigger cell values change
'calls macros with trigger parameters

    Select Case target.Address

        'Trigger 1 activated
        Case "$A$1"
            With target

                If .Value = "x" Then
                    Call Calculation(1)     'calculate output cell 1
                ElseIf .Value = "" Then
                    Call Clean(1)           'clean output cell 1
                Else
                    Call Error              'error notification
                End If

            End With

        '-------------------------------------------

        'Trigger 2 activated
        Case "$A$2"
            With target

                If .Value = "x" Then
                    Call Calculation(2)     'calculate output cell 2
                ElseIf .Value = "" Then
                    Call Clean(2)           'clean output cell 2
                Else
                    Call Error              'error notification
                End If

            End With

        '-------------------------------------------

        'Trigger 3 activated
        Case "$A$3"
            With target

                If .Value = "x" Then
                    Call Calculation(3)     'calculate output cell 3
                ElseIf .Value = "" Then
                    Call Clean(3)           'clean output cell 3
                Else
                    Call Error              'error notification
                End If

            End With


    End Select

End Sub
__________________________________________________________________________

Sub Calculation(Trigger As Integer)
    'calculation process with detection which output is to fill
End Sub

__________________________________________________________________________

Sub Clean(Trigger_Clean As Integer)
    'cleaning process with detection which output is to clean    
End Sub

__________________________________________________________________________

Sub Error()
    'individual error notification
End Sub

While using my code, I noticed, that it works fine for manually changed trigger cell values (wrote x and pressed Enter) but not for a value change by formula. I also noticed that I'm not able to trigger the calculation of output 1 and the cleaning of output 2 and 3 or the cleaning of all output cells at once.

What I need to know: How to get the macros started by automatic trigger cell value changes and how to start the calculation and the cleaning processes at once or in a row?

It really hope my problem finds your interest. Thank you for any help in advance.

RaspiManu

Thank you very much for that hint. Searching through Stackoverflow with this in mind brought me to a solution. I will post an answer with the helpful thread and my finished code. — RaspiManu, May 07 '19 at 12:05

score 1 · Accepted Answer · answered May 07 '19 at 12:15

After the hint of Luuklag, I found a solution by Siddharth Rout in this stackoverflow thread.

I optimized it for my needs to get the following codes:

in a module:

Public PrevVal_1, PrevVal_2, PrevVal_3 As Variant

in ThisWorkbook:

Private Sub Workbook_Open()

    PrevVal_1 = Worksheets("Sheet1").Range("A1").Value
    PrevVal_2 = Worksheets("Sheet1").Range("A2").Value
    PrevVal_3 = Worksheets("Sheet1").Range("A3").Value

End Sub

in Sheet1:

Private Sub Worksheet_Calculate()

    If Range("A1").Value <> PrevVal_1 Then

        If Range("A1").Value = "x" Then
            Call Calculation(1)        
        ElseIf Range("A1").Value = "" Then
            Call Clean(1)                         
        Else
            Call Error                              
        End If

        PrevVal_1 = Range("A1").Value

    End If

    '-------------------------------------------

    If Range("A2").Value <> PrevVal_2 Then

        If Range("A2").Value = "x" Then
            Call Calculation(2)        
        ElseIf Range("A2").Value = "" Then
            Call Clean(2)                         
        Else
            Call Error                             
        End If

        PrevVal_2 = Range("A2").Value

    End If

    '-------------------------------------------

    If Range("A3").Value <> PrevVal_3 Then

        If Range("A3").Value = "x" Then
            Call Calculation(3)        
        ElseIf Range("A3").Value = "" Then
            Call Clean(3)                          
        Else
            Call Error                              
        End If

        PrevVal_3 = Range("A3").Value

    End If


End Sub

I really hope this helps someone with a similar problem.

RaspiManu

How to start two VBA Macros at once when different trigger cells are changed via formula?

1 Answers1