Eng-Tips is the largest engineering community on the Internet

Intelligent Work Forums for Engineering Professionals

  • Congratulations waross on being selected by the Eng-Tips community for having the most helpful posts in the forums last week. Way to Go!

How to trigger a program to run automatically

Status
Not open for further replies.

HighPanda

Civil/Environmental
Nov 28, 2007
40
GB

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
 
Replies continue below

Recommended for you

I think you want the Worksheet Change event handler. Here is a skeleton bit of code for it.
Code:
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.
 
You can use the intersect method to tell if a changing cell in one of interest. For example,
Code:
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top