Solved

Auto Running A Macro

Posted on 2011-09-11
2
207 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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

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 …
This article will show you how to use shortcut menus in the Access run-time environment.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

930 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

9 Experts available now in Live!

Get 1:1 Help Now