INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

How to trigger a program to run automatically

How to trigger a program to run automatically

(OP)

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
 

RE: How to trigger a program to run automatically

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.
 

RE: How to trigger a program to run automatically

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

RE: How to trigger a program to run automatically

(OP)
Thanks!!!!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close