Detect when individual cells were changed, cells not in a range...

[spacely]

Hello,

I have a snippet of code which detects when a cell in a range is changed. I'd like to use it to detect when several cells not in a continuous range on the same sheet are changed...one option would be to define a bunch of ranges and do-while through them all, I guess. here's my starting point. Maybe there's a better way?:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range

Set KeyCells = Range("AK98")

If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then
MsgBox "Cell " & Target.Address & " has changed."
End If
End Sub

 

[spacely]

One more question... how can I alter that sub so it's a globally located sub operating on a sheet name that's passed into it?

 

[Joe4]

Regarding your first question, you can list multiple ranges in your range declaration like this. Just note I think there is a 255 character limit:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim KeyCells As Range
    Set KeyCells = Range("AK98, AL99, AN101:AN104")

    If Not Application.Intersect(KeyCells, Target) Is Nothing Then
        MsgBox "Cell " & Target.Address & " has changed."
    End If
    
End Sub

Also, this is just going in circles!

[COLOR=#333333]Range(Target.Address)[/COLOR]

Its the same as

Target

Target, by definition, is a range variable. So why get its address, and then go to the range? It is unnecessary, since you are ending up where you started!

Regarding your second question, event procedures, like "Workbook_Change" MUST be named a certain way - NOTHING can be altered in the Sub line:

Private Sub Worksheet_Change(ByVal Target As Range)

So you cannot pass a sheet name into it.

There is a Workbook_SheetChange event procedure which runs against ALL sheets. You could then add a Loop or If statement to exclude specific sheets.
It all depends on exactly what exactly is that you are trying to accomplish.

 

[spacely]

Thank you. That helped.

 

[Joe4]

You are welcome.

 

[spacely]

Oh oh... one more followup. The routine detects when individual cells are edited. But when there's a copy of 1 cell, and 5 cells are highlighted and a pasted-special is done to fill the 5 cells, only the 1st highlighted cell in the range-to-paste is detected as an changed cell. Is that an excel thing with Worksheet_Change? Or maybe I'm saying with range "detect only once an edit anywhere inside the range, even if 50 cells were edited"...?

 

[Joe4]

Oh oh... one more followup. The routine detects when individual cells are edited. But when there's a copy of 1 cell, and 5 cells are highlighted and a pasted-special is done to fill the 5 cells, only the 1st highlighted cell in the range-to-paste is detected as an changed cell. Is that an excel thing with Worksheet_Change? Or maybe I'm saying with range "detect only once an edit anywhere inside the range, even if 50 cells were edited"...?

I don't see that behavior exhibited. It seems to be working as expected for me.

Can you post your code, as you currently have it, and walk us through the exact steps you are doing that is not working the way you want?

 

[spacely]

Sure. I watch one big range for single edits. I think that's the issue. Prohibitive to watch every cell as an individual "range":

Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range
If [captureCellHistory].Value = 1 Then
Set KeyCells = Range("S:ZZ") ' should be big enough to capture all hardware added, I hope...specify non-continuous ranges like: Range("AK98, AL99, AN101:AN104")
If Not Application.Intersect(KeyCells, Target) Is Nothing Then
lastRow = Worksheets("cell histories").Cells(Rows.Count, 1).End(xlUp).Row ' add onto bottom of cell history list
Worksheets("cell histories").Cells(lastRow + 1, 1).Value = Now ' date/time of this edit
Worksheets("cell histories").Cells(lastRow + 1, 2).Value = Application.UserName ' username of person who made edit
Worksheets("cell histories").Cells(lastRow + 1, 3).Value = ActiveSheet.name ' sheet name of this edit
Worksheets("cell histories").Cells(lastRow + 1, 4).Value = Target.Row ' row number of this edit
Worksheets("cell histories").Cells(lastRow + 1, 5).Value = Target.Column ' column number of this edit
Worksheets("cell histories").Cells(lastRow + 1, 6).Value = Target.Value ' cell value of this edit
End If
End If
End Sub

 

[Joe4]

The issue is that your intersection may be multiple cells, so you actually need to loop through each cell in your intersection, like this:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim KeyCells As Range
    Dim isect As Range
    Dim cell As Range

    If [captureCellHistory].Value = 1 Then
        Set KeyCells = Range("S:ZZ") ' should be big enough to capture all hardware added, I hope...specify non-continuous ranges like: Range("AK98, AL99, AN101:AN104")
        Set isect = Intersect(KeyCells, Target)
        If isect Is Nothing Then
            For Each cell In isect
                lastRow = Worksheets("cell histories").Cells(Rows.Count, 1).End(xlUp).Row ' add onto bottom of cell history list
                Worksheets("cell histories").Cells(lastRow + 1, 1).Value = Now ' date/time of this edit
                Worksheets("cell histories").Cells(lastRow + 1, 2).Value = Application.UserName ' username of person who made edit
                Worksheets("cell histories").Cells(lastRow + 1, 3).Value = ActiveSheet.Name ' sheet name of this edit
                Worksheets("cell histories").Cells(lastRow + 1, 4).Value = cell.Row ' row number of this edit
                Worksheets("cell histories").Cells(lastRow + 1, 5).Value = cell.Column ' column number of this edit
                Worksheets("cell histories").Cells(lastRow + 1, 6).Value = cell.Value ' cell value of this edit
            Next cell
        End If
    End If
    
End Sub

 

[spacely]

OK, you just forgot the Not, and now it works for cut 'n pasted values too:

Dim KeyCells As Range
Dim isect As Range
Dim cell As Range

If [captureCellHistory].Value = 1 Then
Set KeyCells = Range("S:ZZ") ' should be big enough to capture all hardware added, I hope...specify non-continuous ranges like: Range("AK98, AL99, AN101:AN104")
Set isect = Intersect(KeyCells, Target)
If Not isect Is Nothing Then
For Each cell In isect
lastRow = Worksheets("cell histories").Cells(Rows.Count, 1).End(xlUp).Row ' add onto bottom of cell history list
Worksheets("cell histories").Cells(lastRow + 1, 1).Value = Now ' date/time of this edit
Worksheets("cell histories").Cells(lastRow + 1, 2).Value = Application.UserName ' username of person who made edit
Worksheets("cell histories").Cells(lastRow + 1, 3).Value = ActiveSheet.name ' sheet name of this edit
Worksheets("cell histories").Cells(lastRow + 1, 4).Value = cell.Row ' row number of this edit
Worksheets("cell histories").Cells(lastRow + 1, 5).Value = cell.Column ' column number of this edit
Worksheets("cell histories").Cells(lastRow + 1, 6).Value = cell.Value ' cell value of this edit
Next cell
End If
End If

...but, other cells pointing to those changing cells that also themselves change, do not register as a change. And they are in the checked range. So, cell T30 is =Z30 and changes when Z30 changes. The routine only picks up the Z30 change.

Solvable?