Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Run VBA Code on cell or range change
You test for your range
Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "H1:H10" '<== change to suit On Error GoTo ws_exit Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target ' do your stuff End With End If ws_exit: Application.EnableEvents = True End Sub -- --- HTH Bob (change the xxxx to gmail if mailing direct) "MikeZz" wrote in message ... I want to run some code whenever someone changes a value in a particular cell or range. I know how to do it on Worksheet_Change but don't want to have it actually run code (slowing response down) every time any cell changes. Is there a way to do this? Thanks, MikeZz |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Run VBA Code on cell or range change
"MikeZz" wrote in message
... Hi Bob, I've never used Target/Intersect or even Worksheet_Change before and Excel Help dialog doesn't always spell things out in layman's terms. Worksheet_Change is one of the built-in events, which provide some extremely useful application possibilities. Chip Pearson also noticed the help deficiencies, and has written a good page on them at http://www.cpearson.com/excel/events.htm Can you explain how this line works? "(ByVal Target As Range)" Is "Target" just a variable which is accepting the values in the range that is being changed? How does the routine know to send just the changed values or range? Exactly that. Excel takes care of passing the changed range to the event code, all you have to do is to test whether that range is part of the range that you want to test. and what does "Application.EnableEvents = False" does for me? Based on your code, I could have an error and would exit the routine before EnableEvents is set back to True. Intuitvily this doesn't sound quite right but I've never used it so I'm just asking. Switching EnableEvents off stops the event procedure calling itself over and over if you change some other cells within the code. If the code errors, it gets trapped by the line On Error Goto ws_exit and goes to that code label, which doesn't just exit, the first thing that happens after that is to turn events back on, so order is restored. I've been doing excel programing for years in my job and every time I think I've done just about everything, I find an entire new relm of possibilities. We're all in that boat <g. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Run VBA Code on cell or range change
"MikeZz" wrote in message ... So "Target" is just the range, not the values in the range? By your comment, I'd assume that the "Intersect" is not checking the values to the range in the code, just checking to see if the the changed range is part of the compared range? That's correct. Target is a range object, to get the values you have to use the Value property (and the Cells property if it is a multi-cell range). Your other assumeoption re Intersect is also correct, and that is how you stop the code executing for any change, you can control it to execute just for your specified range, or even take different actions depending upon which which range the changed cell is part of. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Code for a button to change cell location | Excel Discussion (Misc queries) | |||
How do I change cell color based upon data range within the cell? | Excel Worksheet Functions | |||
Change cell colour for a cell or range within a predefined range | Excel Programming | |||
Problem with Running VBA code on Cell Change | Excel Programming | |||
run code after cell contents change | Excel Programming |