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,493
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,493
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,493
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,493
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,082,246
Messages
5,364,001
Members
400,773
Latest member
jwolfe

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top