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.