Solved

Auto Running A Macro

Posted on 2011-09-11
2
196 Views
Last Modified: 2012-05-12

EE Pros,

Simple related question;

If I have a macro called "Run_Order" that right now appears as a button with an associated Macro.    I want it to run automatically, each time it sees a change or entry to a particular column of cells, what is the simple code I use and do I put it in "ThisWorkbook" or the Sheet or a Module?  The macro only partains to a particular sheet and only when the sheet is open.

Thank you,

B.
0
Comment
Question by:Bright01
2 Comments
 
LVL 33

Accepted Solution

by:
Norie earned 500 total points
ID: 36519183
Right click the tab of the sheet you want it to work with.

Then select Change from the right drowdown above the code window.

You should now see this, which is where the code should go.
Private Sub Worksheet_Change(ByVal Target As Range)

End Sub

Open in new window

To check if the change was made in a specific column there are various ways, here's one.
Dim rng As Range 

      Set rng = Range("A:A") ' range to check for change

      If Intersect(rng, Target) Is Nothing Then Exit Sub ' if change not in range then exit the code

      Application.EnableEvents = False

      Call Run_Order        ' call the code you want to run

      Application.EnableEvents = True

Open in new window

This is the basic idea, and will only work for the range you specify on the worksheet the code is in when the workbook is open.
       
0
 

Author Closing Comment

by:Bright01
ID: 36519425
Thank you!  Works perfectly.

B.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now