VBA assist

[onejay]

Hello. I am trying to create an Excel document that can record a running list of each change to a particular cell. I'm using the below VBA formula, but the problem I'm running into is that when I close out and reopen the document, the list of changes resets. So basically, I'm looking for a way to save every change no matter how many times the document is opened or closed. Thank you for any assistance.

Dim xVal As String
Private Sub Worksheet_Change(ByVal Target As Range)
Static xCount As Integer
Application.EnableEvents = False
If Target.Address = Range("B3").Address Then
Range("A6").Offset(xCount, 0).Value = xVal
xCount = xCount + 1
Else
If xVal <> Range("B3").Value Then
Range("A6").Offset(xCount, 0).Value = xVal
xCount = xCount + 1
End If
End If
Application.EnableEvents = True
End Sub

 

[James006]

Hi,
Just make sure your cell A5 is holding something

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
If Target.Address <> "$B$3" Then Exit Sub
' Make Sure Cell A5 is holding something ''''''''''''''''''''
Range("A" & Rows.Count).End(xlUp).Offset(1, 0) = Target.Value
End Sub

 

[onejay]

This is perfect! Thank you very much!

 

[James006]

Glad to hear this could help