Run macro on cell value change - cell is not active cell

[Sather]

I want to run a macro whenever certain cell values change. About a month ago I was attempting to run the macro when the formula in a cell changed the cell value, but ran into problems that I couldn't solve, even with the help of MrExcel. I think because the macro kept changing various cells and it got into an endless loop.

Now I'm back taking a fresh start with the problem. I started with a Microsoft support scheme (http://support.microsoft.com/kb/142154) that I've modified to meet my needs:
============
Sub auto_open()

' Run the macro DidCellsChange any time a entry is made in a cell in Display.
ThisWorkbook.Worksheets("Display").OnEntry = "DidCellsChange"

End Sub

Sub DidCellsChange()
' Define which cells should trigger the KeyCellsChanged macro.
Dim KeyCells As String
KeyCells = "M38:R38, M51:M52, O51:O54, Q51:Q58"

' If the Activecell is one of the key cells, call the MaxGW macro.
If Not Application.Intersect(ActiveCell, Range(KeyCells)) _
Is Nothing Then MaxGW

End Sub
======

This works perfectly every time I manually change a value in one of the KeyCells - it executes my macro "MaxGW". However, I don't want to change the cell values manually - I currently change KeyCells values with control bars, list boxes, and drop down menus. So technically, my KeyCells don't have formulas in them, they have values derived from the control toolbox tools. And the control bar doesn't really control the value in the cell it links to, but the linked cell controls the position on the control bar.

But my modified Microsoft routine only seems to work when I make a change to one of the KeyCells manually, i.e., I am making a change in the current ActiveCell. But I have been able to figure out how to invoke MaxGW when any value in KeyCells changes for any reason. I'm thinking it should be fairly simple, I just haven't been able to find an example yet.

Any suggestions are greatly appreciated!

Scott Sather
Excel 2003/MVB 6.5

 

[Matt Rogers]

Hi Scott.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("B1:B10,M38:R38,M51:M52,O51:O54,Q51:Q58")) Is Nothing Then Exit Sub
'.... your macrocode
End Sub

 

[Smitty]

Welcome to the Board!

You can use the Calculate event to evaluate when formulas update.

HTH,

 

[Sather]

Matt,

The Worksheet_Change sub didn't seem to activate when I move the control bars.

Scott

 

[Sather]

Smitty, I was concerned that the Calculate event would create an endless loop (because my macro causes calculations to be performed), but instead it seemed to not get invoked. So I'm still looking for a solution.

Scott

You can use the Calculate event to evaluate when formulas update.

HTH,