Excel VBA - Intersect () helps to trigger the Macro

Besides using Button, is there any other way to trigger the Macro in Excel?

The answer is yes.

A Macro runs when a cell value changes.

The following code reacts to changes in cells on worksheets.  

For example,

 I build a macro named as CalcPV, which can calculate the present value of the future cash stream. The cash flow is saved from Cell I8 to Cell I3000. The Cells K3 to K5 are used to save the loan date, discounted rate and compounding method.

If any of the above values changes, the Macro CalcPV runs and the new present value is calculated.

Private Sub Worksheet_Change(ByVal Target As Range)

   If Not Intersect (Target, Range("I8:I3000,K3:K5")) Is Nothing Then

   Call CalcPV

   End If

End Sub

With the modification (change the blue parts), the above code can be used for different cells in the Excel worksheet.

© Copyright Exceltipsandkeys All Rights Reserved.