Using intersect to see if value has changed

[Richynero]

I have a list of names in cells C6:C17. If a user changes anything in this range I want to record the user name and date. The below macro serves this purpose to an extent.

This event driven macro is fired if the user goes into the cell and then makes NO changes. How can I get the macro to check if the cell value has actually changed?

Private Sub Worksheet_Change(ByVal Target As Range)
[INDENT]If Not Intersect(Target, Range("C6:C17")) Is Nothing Then
[INDENT]Target.Offset(0, 5).Value = Application.UserName
Target.Offset(0, 6).Value = Now
[/INDENT]End If
[/INDENT]End Sub

Thanking you all in advance.

Richard

 

[Firefly2012]

Hi Richard

In a standard module in your workbook, place the following line at the top of the module before any Sub or Function declarations:

Public v As Variant

In your ThisWorkbook module, place the following code in the Workbook open:

Private Sub Workbook_Open()
v = Sheet1.Range("C6:C17").Value  'amend the sheet to whichever one holds the data
End Sub

remembering to amend the sheet to suit.

Then change your Change code to:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range, cell As Range
Set rng = Intersect(Target, Range("C6:C17"))
If Not rng Is Nothing Then
    For Each cell In rng
        If cell.Value <> v(cell.Row - Range("C6:C17").Row + 1, 1) Then
            Application.EnableEvents = False
            cell.Offset(, 5) = Application.UserName
            cell.Offset(, 6) = Now
            v(cell.Row - Range("C6:C17").Row + 1, 1) = cell.Value
        End If
    Next cell
End If
Application.EnableEvents = True
End Sub

You will need to run the Workbook Open code to successfully riun the Worksheet_Change, so you can either do this by placing your cursor in the Workbook Open and hitting F5 or saving, closing and reopening the workbook.

 

[Richynero]

Firefly, I must say that I am quite impressed with your rapid and well worded response.

I have yet to test the code but will do so as soon as I get the chance. I will report back and let you know how I got on.

If you could clarify something which I am struggling to get my head round.

v(cell.Row - Range("C6:C17").Row + 1, 1)

v(cell.Row - Range("C6:C17").Row + 1, 1) = cell.Value

The first line is looking for the difference but I can't get my head round the syntax.

The second line cleverly updates v with the new value, but again, cant get my head around the syntax.

Thank you so much for your help. It is greatly appreciated. With your help I have done quite a bit of learning this morning and have only been at my disk for 90mins!

 

[Firefly2012]

The Workbook_Open code creates a 2 dimensional variant array when it assigns the Range to v. This 2D array has elements starting at 1 up to the number of rows, and has 1 column.

When a cell in the C6:C17 range is changed, this cell has a row property (eg change C6, then the cell's row property is 6).

We need to determine which element of array v holds the old value of cell. So that line is effectively determining which cell in C6:C17 the changed cell c represents. C6 is the first cell (so in v it is v(1,1)). cell.Row returns 6, Range("C6:C17").Row also returns 6, add 1 and thus the cell is:

6 - 6 +1 = 1st cell in range("C6:C17")

Hence we compare this against the first element of v.

Does this help or hinder?

 

[Richynero]