How to Log Changes on Sheets!

[nick11medic]

I have spreadsheet. I am trying to write a code so that when one specific cell is changed to a number greater than 0 it will record the date and time of this occurrence on another sheet. Then if this cell is changed again to another number greater than 0 I want it to record that date in an ever expanding list. So that every time a number is recorded in that cell it adds to a list on another sheet to capture every time data was entered in that cell that was greater than 0. I would be fine if we also did this as a VBA code that we assign as a macro to a button that opens a text prompt that asks for the number for that cell and when you enter the number and confirm it in the prompt as long as it is greater than 0 it then adds the date of the occurrence to the list in the other sheet. Let me know if you have any questions or concerns.

[Paul_Hossler]

1. Welcome to the forum -- please take a minute and read the FAQs in my sig

2. A more descriptive title that "Help" works better

3. This goes in the worksheet code module, not a standard Module1, ....




Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)
Dim rMagicCell As Range, rLogCell As Range


Set rMagicCell = Range("B4") ' <<<<<<<<<<<<<<<<<<<<<< change

If Intersect(rMagicCell, Target.Cells(1, 1)) Is Nothing Then Exit Sub


If rMagicCell.Value <= 0# Then Exit Sub


With Worksheets("Log")
Set rLogCell = .Cells(.Rows.Count, 1).End(xlUp)

If Len(rLogCell.Value) > 0 Then Set rLogCell = rLogCell.Offset(1, 0)
End With

rLogCell.Value = Now

End Sub


I didn't understand the MsgBox part of your question

[nick11medic]

Thanks Paul. That works perfectly. One more quick question. If I wanted to also do the same thing in the same worksheet, but with B6 also but put it to Worksheets("Log2") how would I do that?

P.s. I will make sure to follow the rules. I apologize for my poor post. I just have been trying to get this code to work for two days. I was just as frustrated as could be. I can't believe you got it working that fast. And you even guessed exactly what I was doing... Putting it in a standard module.

[SamT]

An idea inspired by Paul's Excellent code


Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("B4"), Target) Is Nothing Then LogB4 Range("B4") 'Edit B4 as needed
If Not Intersect(Range("B6"), Target) Is Nothing Then LogB6 Range("B6")
End Sub

Private Sub LogB4(ByVal Target As Range) 'Change Name "LogB4" as needed
If Target = 0 Then Exit Sub
Worksheets("Log").Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0) = Now
End Sub

Private Sub LogB6(ByVal Target As Range)
If Target = 0 Then Exit Sub
Worksheets("Log2").Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0) = Now
End Sub
My way:

Private Sub logSamT(ByVal Target As Range)
'Records time of change, what cell was changed, who changed it and the new value
'To use in worksheet Change sub:
' If Not Intersect(Range("B4"), Target) Is Nothing Then LogSamT Range("B4")
' If Not Intersect(Range("B6"), Target) Is Nothing Then LogSamT Range("B6")
'Repeat for each desired range

Dim NewCell As Range

With Worksheets("logSamT")
Set NewCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
NewCell = Now
NewCell.Offset(0, 1) = Target.Address
NewCell.Offset(0, 2) = Application.UserName
NewCell.Offset(0, 3) = Target.Value
End With
End Sub

[Paul_Hossler]

Showing different techniques

Some techniques can more easily be expanded when requirements change, or bugs need to be squashed






Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)
Dim rLogCell As Range
Dim wsLog As Worksheet

Select Case Target.Cells(1, 1).Address
Case "$B$4"
Set wsLog = Worksheets("Log")
Case "$B$6"
Set wsLog = Worksheets("Log2")
Case Else
Exit Sub
End Select


If Target.Cells(1, 1).Value <= 0# Then Exit Sub


With wsLog
Set rLogCell = .Cells(.Rows.Count, 1).End(xlUp)

If Len(rLogCell.Value) > 0 Then Set rLogCell = rLogCell.Offset(1, 0)
End With

rLogCell.Value = Now


End Sub

[nick11medic]

Y'all are amazing. I just need to sit down with y'all one day and learn how to code better in excel.