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

spacely

Board Regular
Joined
Oct 26, 2007
Messages
241
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

Board Regular
Joined
Oct 26, 2007
Messages
241
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,771
Office Version
365
Platform
Windows
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:
Code:
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!
Code:
[COLOR=#333333]Range(Target.Address)[/COLOR]
Its the same as
Code:
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:
Code:
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.
 
Last edited:

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,771
Office Version
365
Platform
Windows
You are welcome.
 

spacely

Board Regular
Joined
Oct 26, 2007
Messages
241
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,771
Office Version
365
Platform
Windows
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

Board Regular
Joined
Oct 26, 2007
Messages
241
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,771
Office Version
365
Platform
Windows
The issue is that your intersection may be multiple cells, so you actually need to loop through each cell in your intersection, like this:
Code:
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

Board Regular
Joined
Oct 26, 2007
Messages
241
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? :)
 

Forum statistics

Threads
1,085,307
Messages
5,382,843
Members
401,807
Latest member
xlWatcher

Some videos you may like

This Week's Hot Topics

Top