title: How to start two VBA Macros at once when different trigger cells are changed via formula?
body: <p>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 <strong>change their value automatically by formula</strong>.</p>
  
  <p>Imagine it like this:</p>
  
  <ul>
  <li>3 pairs of cells spreaded over the sheet (1 pair = 1 trigger cell + 1 output cell)</li>
  <li>1 calculating macro and 1 cleaning macro</li>
  <li>If one trigger cell changes to "x", the calculating macro starts and gives an output to the nearby output cell.</li>
  <li>While this happens, the other trigger cells go to "" what starts the cleaning (ClearContents) of their output cells.</li>
  <li>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.</li>
  </ul>
  
  <p>I'm working with Excel 2010 on Windows 7 and this is my code so far:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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.</p>
  
  <p><strong>What I need to know:</strong>
  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?</p>
  
  <p>It really hope my problem finds your interest.
  Thank you for any help in advance.</p>
  
  <p>RaspiManu</p>