0

I have it set up right now so when the cell data changes, it updates the date. When refreshing the workbook, it also updates the date, even if I haven't modified the cell contents.

I only want to update the date if I modify the cell contents. I am pulling my data from a CSV file so I constantly have to refresh every now and again. How do I get around this so that it only shows the date upon which a cell is truly modified?

I have a public function set up like this at the moment, but it does not work.

Public Function worksheetChange(ByVal Target As Range)

If Not Application.Intersect(Target, [D4]) Is Nothing Then [L4] = Date

End Function

Even when I refresh the worksheet and I haven't modified the cell, it still updates the date, how do I get around this?

EDIT: I've come up with this type of VBA code, but I'm not sure how to get it working. 

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Application.Intersect(Target, [D4]) Is Nothing Then
If Not ActiveWorkbook.RefreshAll Then [L4] = Date

End Sub

In hindsight, this code means that if the application doesn't intersect then take D4 and also, if the workbook doesn't refresh, update L4 as todays date.

Thank you.

juiceb0xk
  • 949
  • 3
  • 19
  • 46
  • Could it be that [D4] is recalculated when you refresh the worksheet, although the new value equals the old one? – A.S.H Nov 22 '16 at 02:30
  • I would assume so, it's pulling the data from the csv file when refreshing. I only want the date changed only if the cell contents change (ie. banana to orange, not banana to banana) I've updated my code in order to get a grasp of what I'm trying to achieve. – juiceb0xk Nov 22 '16 at 02:34
  • How about some programmatic solution by saving the old value somewhere (say in another, hidden worksheet) and testing if the value has changed? – A.S.H Nov 22 '16 at 02:52
  • I guess I will have to resort to that if I can't find a solution, thanks though. – juiceb0xk Nov 22 '16 at 02:58

1 Answers1

1

Refer the link

Dim oldValue

Public Sub Worksheet_SelectionChange(ByVal Target As Range)
  oldValue = Target.Worksheet.Range("D4").Value
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Target.Worksheet.Range("D4")) Is Nothing Then
      If oldValue <> Target.Worksheet.Range("D4").Value Then
        Target.Worksheet.Range("L4").Value = Date
      End If
    End If
End Sub
Community
  • 1
  • 1
Mukul Varshney
  • 3,131
  • 1
  • 12
  • 19
  • It still updates the date even if the cell contains the same value as it did when refreshing. I'm only wanting to update the date as the cell changes contents; trying to evade the date updating when refreshing data of the same value. – juiceb0xk Nov 22 '16 at 04:44
  • It works, thanks! Now to find out how to make this as a function as I want to spread this across a multiple range of cells. – juiceb0xk Nov 22 '16 at 23:36