How to make the automatic recalculation of Excel trigger a routine in VBA?

Question

I have a graphic scheme on Excel which is done by way of conditional formatting (see figure below), ie in the colored cells there is no content, only the background colors are changed based on values of other cells.

The dashed blue line is done by a routine on VBA (the connections are complex to make also the borders by conditional formatting), occurs that the user after launching the values when they need to be changed, the figures are updated dynamically by own Excel, but the dashed line requires a button to be triggered so that the VBA redo the connections (which is natural), that is, there is visual discrepancy between the data and the graphic results presented until the routine is triggered.

This harms the data release itself that depends on the desired outcome of what is being analyzed graphically. The figure below shows this situation, the disconnection of the dashed line that is not changed dynamically.

The next figure shows the result after the routine is triggered by the button.

The routine redoes the connections immediately without any waiting time. What I couldn’t find is a way for routine to be dynamically triggered as soon as the Excel automatically redo calculations for any changes made to the spreadsheet.

The Application.Calculate triggers the calculation in Excel (idem typing F9) from the VBA, What I need is the reverse, the automatic recalculation of the Excel trigger the routine in VBA.

It is possible?

Answer 1

Unfortunately there is no simple way to do this; it is necessary to break your head and devise a system of on/off of the resource Application.Calculation. The modes of this feature are:

Application.Calculation = xlCalculationAutomatic     'Cálculos automáticos
Application.Calculation = xlCalculationManual        'Cálculos automáticos desativados
Application.Calculation = xlCalculationSemiautomatic 'Cálculos automáticos, menos em tabelas

One suggestion I give you is to apply the following idea in the Worksheet module that you change the data and display the 'graph':

Private Sub Worksheet_Activate() 
    Application.Calculation = xlCalculationManual
End Sub 

Private Sub Worksheet_Deactivate() 
    Application.Calculation = xlCalculationAutomatic 
End Sub 

So, when you select the spreadsheet with the graph, the VBA will run the event Activate, turn off the automatic calculation, and it will automatically restore itself when accessing any other spreadsheet. To complement the answer to your question, simply add a line with Calculate in the code that runs with your button to see all graphic changes simultaneously at the click of the button. Place at the beginning of the code to have the results of the calculations before handling the dashed edges.

Obviously, you can think of other ways to apply this feature, but keep in mind that it will ALWAYS apply to all Excel (all files opened).

Answer 2

Use the event Calculate for the Worksheet on which the button and graph is located, thus:

Private Sub Worksheet_Calculate()

    Call Sub_Usada_no_Botao

End Sub

So every time some calculation occurs in your spreadsheet, the Sub will be cocked.

Answer 3

The same can be done for trigger a routine only when there is change in one or more cells specified in a Range.

Based on one of the answers to the question How can I run a VBA code each time a Cell get is value changed by a formula?, found a simpler way to get the same result:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim AlterouNaFaixaDeDados As Range

 'Indique na Range a faixa que queira acionar uma rotina dinamicamente se houver alteração nas suas células
 Set AlterouNaFaixaDeDados = Application.Intersect(Range("B2:E5"), Target)

 'Se houve alteração em uma ou mais células desta Range, a rotina será acionada
 If Not AlterouNaFaixaDeDados Is Nothing Then

   'Coloque aqui a rotina a ser acionada
   Call Sub_Rotina

 End If

End Sub