对相邻单元格进行更改时，自动在单元格中填写日期 [英] Auto-fill the date in a cell, when change is made into an adjacent cell

问题描述

我发现可以在Excel中工作的代码几乎可以满足我的需求.

我需要在另一列的单元格中进行更改时，将今天的日期自动添加到一个列的单元格中.因此，如果我在第M列第20行的单元格中单击更改数据或添加数据(在本例中为带有下拉列表的Status列)，然后在N列第20行的单元格中放置今天的日期或将旧的日期替换为今天的日期. (每次状态"下拉列表都会更改.)

此代码针对2个不同的列执行此操作，因为我对其进行了更改.

问题:

1. 如果我插入行，它将把今天的日期放在新插入的行中 行，或者如果我删除行，假设3行将添加日期或 覆盖刚删除的3条下面的3行中的日期.这是 不好.我只想添加一个日期，如果我在单元格中进行了更改 本身.当我们添加或更改状态(数据)时，只需简单地自动添加日期 在它左侧的单元格中.

2. 我还需要前9行不受此自动日期的影响 添加.

3. 最后，如果我双击M列第20行的单元格，但没有 输入任何数据，然后在单元格外单击，它仍将日期添加到 N列第20行的单元格.

我在以下位置找到了原始代码: 当用户在相邻单元格中输入信息时，自动在单元格中填写日期

我的代码版本是:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim A As Range, M As Range, X As Range, Inte As Range, r As Range
Set A = Range("M:M,X:X")
Set Inte = Intersect(A, Target)
If Inte Is Nothing Then Exit Sub
Application.EnableEvents = False
    For Each r In Inte
        r.Offset(0, 1).Value = Date
    Next r
Application.EnableEvents = True
End Sub

解决方案

这些修改将照顾您的前两个规范.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("M:M,X:X"), Range("10:" & Rows.Count)) Is Nothing Then
        If Target.Count < Columns.Count Then
            On Error GoTo bm_Safe_Exit
            Application.EnableEvents = False
            Dim r As Range
            For Each r In Intersect(Target, Range("M:M,X:X"), Range("10:" & Rows.Count))
                With r.Offset(0, 1)
                    .Value = Now   'use Now to retain the time as well as the date
                    .NumberFormat = "dd-mmm-yyyy hh:mm:ss"  'change to what you prefer
                End With
            Next r
        End If
    End If
bm_Safe_Exit:
    Application.EnableEvents = True
End Sub

就第三个而言，我会谦虚地建议您习惯使用点击 Esc 放弃单元内"编辑而不是Enter↵

的首选方法. kbd>或选择其他单元格.使用Esc不会更改单元格中的值，并且 Worksheet_Change 事件宏未触发.使用Enter键或选择另一个单元格，可以更改单元格中的值，并且如果可以采用适当的键盘习惯，则针对不足的习惯编写代码根本不值得.

附录:

如果您的手仍在鼠标上，也可以单击编辑栏中的×来[Esc]单元内编辑.

I found code to work in Excel that almost does what I need.

My need is to have today's date added automatically in one column's cell when a change is made in another column's cell. So if I click in Column M Row 20's cell & change data or add data (in this case it is a Status column with a dropdown list) then in Column N Row 20's cell it will put today's date or replace old date with today's date. (Every time Status dropdown is changed.)

This code does that for 2 different columns because I altered it.

The Problems:

1. If I insert rows it will put today's date in the newly inserted rows or if I delete rows, let's say 3 rows it will add the date or overwrite the date in the 3 rows below the 3 just deleted. This is not good. I only want a date added if I make a change in the cell itself. Simply auto add date when we add or change the status (Data) in the cell to the left of it.

2. Also I need the top 9 rows not to be affected by this auto date add.

3. Lastly if I double click in Column M Row 20's cell but do not enter any data then click out of the cell it will still add date to Column N Row 20's cell.

I found the original code at: Auto-fill the date in a cell, when the user enters information in an adjacent cell

My version of the code is:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim A As Range, M As Range, X As Range, Inte As Range, r As Range
Set A = Range("M:M,X:X")
Set Inte = Intersect(A, Target)
If Inte Is Nothing Then Exit Sub
Application.EnableEvents = False
    For Each r In Inte
        r.Offset(0, 1).Value = Date
    Next r
Application.EnableEvents = True
End Sub

解决方案

These modification take care of your first two specifications.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("M:M,X:X"), Range("10:" & Rows.Count)) Is Nothing Then
        If Target.Count < Columns.Count Then
            On Error GoTo bm_Safe_Exit
            Application.EnableEvents = False
            Dim r As Range
            For Each r In Intersect(Target, Range("M:M,X:X"), Range("10:" & Rows.Count))
                With r.Offset(0, 1)
                    .Value = Now   'use Now to retain the time as well as the date
                    .NumberFormat = "dd-mmm-yyyy hh:mm:ss"  'change to what you prefer
                End With
            Next r
        End If
    End If
bm_Safe_Exit:
    Application.EnableEvents = True
End Sub

As far as the third, I would humbly suggest that you get used to using the preferred method of tapping Esc to abandon an 'in-cell' edit rather than Enter↵ or selecting another cell. Using Esc does not change the value in the cell and the Worksheet_Change event macro is not triggered. Using Enter or selecting another cell DOES change the value in the cell and coding against lackadaisical practises is simply not worth the overhead when proper keyboard practises could be applied.

Addendum:

If your hand is still on the mouse, you can also click the × in the formula bar to [Esc] an in-cell edit.

        

这篇关于对相邻单元格进行更改时，自动在单元格中填写日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！