-1

I have a workbook where each sheet has column E labeled "Date Modified" and column F labeled "Date Created." I want that whenever I edit a cell in columns A:D, the current date is entered into cell E of that row. If I am adding a new row, I want the current date to be entered into cell F of that row. I have the following macro saved in ThisWorkbook (and I have other Subs in there that do currently run as expected, so it's not an issue with macros being disabled or anything like that.)

Private Sub Worksheet_Change(ByVal Target As Range)
 MsgBox("Macro fired!")
 If Not Intersect(Target, Range("A:D")) Is Nothing Then
    If Target = Selection Then
        ThisRow = Target.Row
        If Range("F" & ThisRow) Is Nothing Then
            Range("F" & ThisRow) = Date
        Else
            Range("E" & Target.Row) = Date
        End If
    End If
 End If
End Sub

I don't even get the MsgBox, which makes me think the Worksheet.Change event isn't firing for some reason. (It also still doesn't work even if I remove the If Target = Selection logic, which is just intended to ensure that it works on cells that I'm updating, not that are updated by another macro, for example. Prepending ActiveSheet to each Range makes no difference either.)

Any ideas why this macro doesn't run when I edit a cell?

Improve this question
6
  • And before this gets closed as a duplicate, I've looked at stackoverflow.com/questions/72007609 already and I can't see any differences in formatting or syntax that would make mine fail Commented May 2, 2023 at 16:38
  • 4
    That sub should be in a worksheet code module. In ThisWorkbook you could use Workbook_SheetChange to handle an update on any sheet in the workbook. What's the purpose of If Target = Selection Then though ? Commented May 2, 2023 at 16:39
  • the code: If Range("F" & ThisRow) Is Nothing Then .... is always TRUE because "Is Nothing" checks the Range as object whitch always EXIST, not the Value of the Range Commented May 2, 2023 at 17:56
  • Re And before this gets closed as a duplicate. There are significant differences between your code and the answer to that Q. If you study it, it'll leed you to your solution. Commented May 2, 2023 at 22:11
  • Re I don't even get the MsgBox. That suggests that at some point you've turned Application.EnableEvents off. Commented May 2, 2023 at 23:23

1 Answer 1

Reset to default
0

Since you have several sheets with the same function, you must have a code for all of them. Put the Public Sub in a module and you will call it through the Worksheet_Change of every sheet you want. If necessary, you can have different columns (not exclusively "A:D", E and F) in a sheet since these are optional parameters with default values. Try to use: Option Explicit

Option Explicit


Public Sub When_Worksheet_Change(ByVal Target As Range, Optional checkColumns As String = "A:D", Optional colCreate As String = "F", Optional colUpdate As String = "E")
   Dim ws As Worksheet, targetRow As Long
   Set ws = Target.Worksheet
   If Not Intersect(Target, ws.Range(checkColumns)) Is Nothing Then
      targetRow = Target.Row
      With ws.Range(colCreate & targetRow)
         If .Value = "" Then .Value = Date Else ws.Range(colUpdate & targetRow) = Date
         'use Now() if need the exact time of creation and update
         'If .Value = "" Then .Value = Now() Else ws.Range(colUpdate & targetRow) = Now()
      End With
   End If
End Sub

From each sheet you call it as below:

Private Sub Worksheet_Change(ByVal Target As Range)
   Call When_Worksheet_Change(Target)
End Sub
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.