Can excel or VB tell you when and where a change occurs in a defined range? (By the way, Thanks D.H

Posted by Mark Foss on March 28, 2001 2:02 PM

I have a range of cells (X57:AO96) filled entirely with TRUE/FALSE statements that needs to be watched for any changes and also i need to know what the most recent change was.

I am using this to keep a set of selections from being duplicates with check boxes. I have most of the code and everything works fine but I can not figure out how to get the most recently selected check box to remain active and the one that had been selected prior to be deselected.

Again I can do this but the order that the code deselects the duplicates is either from the top-left, then across and down to the bottom-right or in the reverse order as above.

What I need again is to find out which selection was made most recently and which one is the older selection. I believe I can work the rest out for myself.

Any help would be greatly appriciated,

Mark Foss

Posted by Dave Hawley on March 28, 2001 2:37 PM

Hi Mark

Not too sure if I'm on the right track here, but try this:

Right click on the sheet name tab, select "View Code" and paste in this:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim WatchRange As Range
If Target.Cells.Count > 1 Then Exit Sub
Set WatchRange = Range("X57:AO96")
If Not Intersect(Target, WatchRange) Is Nothing Then
Range("A1") = Target.Address
End If
End Sub

Is that close ?

Dave

OzGrid Business Applications