Excel VBA Add (non-changing) Time Stamp to Named Cell

Question

I would like to automatically add a non-updating time stamp to NAMED CELLS (Named_Cell_1, Named_Cell_2,...) or a specific NAMED RANGE of cells (Named_Range_Cells_1, Named_Range_Cells_2,...) randomly placed in a worksheet, when entering a value in a directly adjacent cell in a directly adjacent column to the named cell or the named range.

I cannot use unnamed cells or cell ranges (e.g.: A1:A4) to determine the cells where the date stamp needs to be entered, as the cells are part of a dynamic worksheet with ever changing row and columns, therefore the cells where the date stamps need to appear, must be all named cells or ranges.

There are about 5 to 6 named cells that need to be date stamped, so I do not mind hard VBA coding each and every cell.

I realize we need VBA Code for this and that we cannot use Now() or Today().

Your help and time would be appreciated. I found some code snippets that kind of do the job but are too general and do not use cell names or range names.

Any help is very much appreciated.

= = = = = = = = =

Here is the first snippet.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 3 Then
        Application.EnableEvents = False
        Cells(Target.Row, 4) = Date + Time
        Application.EnableEvents = True
    End If
End Sub

Here is a second.

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
    With Target(1, 2)
    .Value = Date
    .Entire Column.AutoFit
    End With
End If
End Sub

Answer 1

One approach: in the Worksheet_Change test if the adjacent cell has a name, if it does test the name of the name to see if it's a "time stamp here" name. Then place the time stamp.

To be clear, it's the cell that will contain the Time Stamp that is named.

Like this, to place Time stamp to the left of changed data

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cl As Range, nm As Name

    For Each cl In Target.Cells
        If cl.Column > 1 Then
            Set nm = Nothing
            On Error Resume Next
            Set nm = cl.Offset(0, -1).Name
            On Error GoTo 0
            If Not nm Is Nothing Then
                ' Keep only one of these three lines
                If nm.Name Like "*Named_Cell_*" Then ' Book or Sheet scoped names
                'If nm.Name Like "Named_Cell_*" Then ' Book scoped names
                'If nm.Name Like "*!Named_Cell_*" Then ' Sheet scoped names

                    Application.EnableEvents = False
                    nm.RefersToRange = Date + Time
                    Application.EnableEvents = True
                End If
            End If
        End If
    Next
End Sub

Like this, to place Time stamp to the right of changed data

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cl As Range, nm As Name

    For Each cl In Target.Cells
        If cl.Column < Me.Columns.Count  Then
            Set nm = Nothing
            On Error Resume Next
            Set nm = cl.Offset(0, 1).Name
            On Error GoTo 0
            If Not nm Is Nothing Then
                ' Keep only one of these three lines
                If nm.Name Like "*Named_Cell_*" Then ' Book or Sheet scoped names
                'If nm.Name Like "Named_Cell_*" Then ' Book scoped names
                'If nm.Name Like "*!Named_Cell_*" Then ' Sheet scoped names

                    Application.EnableEvents = False
                    nm.RefersToRange = Date + Time
                    Application.EnableEvents = True
                End If
            End If
        End If
    Next
End Sub

This handles WorkBook and WorkSheet scoped names. Other options included as commented out lines

Make sure the Name pattern is unique to these "time stamp goes here" names.

You can have as many Named Cells as you wish, just add a suffix to the name. Personally I'd use worksheet scoped names "TimeStampGoesHere_1", "TimeStampGoesHere_2" etc (you can restart the numbering at 1 on each sheet) and change to If to

If nm.Name Like "*!TimeStampGoesHere_*" Then

Note on Scope of Names.

Names may be scoped to the Workbook, or to a single Sheet. This is visible in the Name Manager. It should be clear from that how the If statements work

To see how VBA reports the Names of these named ranges, run this

Sub NameScope()
    Dim nm As Name

    Set nm = Sheet1.Range("A1").Name
    Debug.Print "Workbook Scoped Name", nm.Name

    Set nm = Sheet1.Range("A2").Name
    Debug.Print "Worksheet Scoped Name", nm.Name
End Sub

which shows

Workbook Scoped Name Sample1
Worksheet Scoped Name Sheet1!Sample2