How to trigger a program to run automatically

[HighPanda]

A want to do some VBA codes on a spreadsheet and the codes can run automatically.

For instance, if I input assign 1 to A1 and 3 to B1, I want to write a set of codes behind a cell, say, C1 to work out sum of A1 and B1 and display the result in that cell.

I remember there is a command(s)/statement(s) which will trigger Excel to run the program as long as the content in either cell A1 or cell B1 is changed, but I can’t remember now.....

Can anybody help me?

HP

 

[Denial]

I think you want the Worksheet Change event handler. Here is a skeleton bit of code for it.

Private Sub Worksheet_Change(ByVal Target As Range)
'
'  Do the necessary things when the worksheet changes.
'
'  The EnableEvents=False serves to prevent recursive behaviour,
'  where the event-handler makes a change to the spreadsheet that in
'  turn re-triggers the event-handler, and so on, ad infinitum.
'  When you disable EnableEvents, you must make sure it is re-enabled
'  later.
'
Application.EnableEvents = False
'
'  Code placed here will be run if a change is made to ANY cell in
'  the worksheet.
'
If Not Intersect(Target, Range("YourNamedRangeOnSheet")) Is Nothing  Then
    '
    '  Code placed here will run only if the spreadsheet cell that is
    '  changed is inside "YourNamedRangeOnSheet".
    '
End If
'
'  Now all required work has been done, turn EnableEvents back on.
'
Application.EnableEvents = True
'
End Sub

This code lies behind the appropriate sheet object, not in a general module.

Hope this helps.

 

[cummings54]

You can use the intersect method to tell if a changing cell in one of interest. For example,

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1,B1")) Is Nothing Then Exit Sub
Range("C1").Value = Range("A1").Value + Range("B1").Value
End Sub

 

[HighPanda]

Thanks!!!!