I’d like to time stamp and add application username multiple different ranges on the same worksheet when any changes are made to rows in each range

huangapple
go评论111阅读模式
英文:

I’d like to time stamp and add application username multiple different ranges on the same worksheet when any changes are made to rows in each range

问题

我表格的示例
我使用了这个事件宏来针对单个表格，但我想知道是否有一种方式可以使用类似的宏，但它适用于所有表格。例如，如果我在表格1（V3:AG34）中进行更改，我希望列AH中的每一行都有一个用户名/时间戳，该时间戳会在表格内发生更改的每一行。如果我对表格5（BP3:BU160）进行更改，我希望列BV中的每一行都有一个用户名/时间戳，该时间戳会在表格内发生更改的每一行。这是否可能，并且是否可以编写以在这些表格中添加行时更新？
工作表中的一些表格是使用Xlookup填充的。

  1. Private Sub Worksheet_Change(ByVal Target As Range)
  2.     Dim r As Range, Intersection As Range, cell As Range
  3.     Dim s As String
  5.     Set r = Range("B3:CA1003")
  6.     Set Intersection = Intersect(r, Target)
  7.     s = vbCrLf & Environ("USERNAME") & vbCrLf & Application.UserName
  9.     If Intersection Is Nothing Then Exit Sub
  11.     Application.EnableEvents = False
  12.     For Each cell In Intersection
  13.         Range("A" & cell.Row).Value = Date & " " & Time & s
  14.     Next cell
  15.     Application.EnableEvents = True
  16. End Sub

任何帮助将不胜感激。

提前感谢！

我已经在互联网上搜索了任何关于这个问题的答案，但我无法将它转换为同一工作表上的多个范围。

英文:

Example of one of my tables
I used this event macro for a single Table, but I would like to see if there is a way to use a similar macro but it applies to all tables. For example, if I make a change in table1 (V3:AG34) I would like column AH to have a username/timestamp in each row that has a change within that table. If I make a change to Table5 (BP3:BU160) I would like column BV to have a username/timestamp in each row that has a change in within that table. Is this possible, and can be written to update if I add rows to those tables?
Some of the tables in the worksheet are being populated using Xlookup.

  1.     Private Sub Worksheet_Change(ByVal Target As Range)
  2.         Dim r As Range, Intersection As Range, cell As Range
  3.         Dim s As String
  4.     
  5.         Set r = Range("B3:CA1003")
  6.         Set Intersection = Intersect(r, Target)
  7.         s = vbCrLf & Environ("USERNAME") & vbCrLf & Application.UserName
  8.     
  9.         If Intersection Is Nothing Then Exit Sub
  10.     
  11.         Application.EnableEvents = False
  12.             For Each cell In Intersection
  13.                 Range("A" & cell.Row).Value = Date & " " & Time & s
  14.             Next cell
  15.         Application.EnableEvents = True
  16.     End Sub

Any help would be greatly appreciated.

Thanks in advance!

I’ve searched the internet for any sort of semblance of an answer to this and I’ve not been able to convert this to multiple ranges on the same worksheet.

答案1

得分: 0

私有子程序工作表更改（多个Excel表格ListObjects）

  • 请注意，假定您已向每个表格添加了一个“标记”列。

I’d like to time stamp and add application username multiple different ranges on the same worksheet when any changes are made to rows in each range

  1. Private Sub Worksheet_Change(ByVal Target As Range)
  3.     Dim lo As ListObject, irg As Range, drg As Range, urg As Range
  5.     For Each lo In Me.ListObjects
  6.         With lo.DataBodyRange
  7.             Set irg = Intersect(.Resize(, .Columns.Count - 1), Target)
  8.             If Not irg Is Nothing Then
  9.                 Set drg = Intersect(irg.EntireRow, .Columns(.Columns.Count))
  10.                 If urg Is Nothing Then
  11.                     Set urg = drg
  12.                 Else
  13.                     Set urg = Union(urg, drg)
  14.                 End If
  15.                 Set irg = Nothing ' reset for the next iteration
  16.             End If
  17.         End With
  18.     Next lo
  20.     If urg Is Nothing Then Exit Sub
  22.     Dim Stamp As String: Stamp = Now & vbLf & Environ("USERNAME") & vbLf & Application.UserName
  24.     Application.EnableEvents = False
  25.         urg.Value = Stamp
  26.     Application.EnableEvents = True
  28. End Sub

初始帖子

工作表更改：时间戳和用户

  1. Private Sub Worksheet_Change(ByVal Target As Range)
  3.     Const FIRST_ROW_RANGE As String = "B3:CA3"
  4.     Const STAMP_COLUMN As String = "A"
  6.     Dim trg As Range
  8.     With Me.Range(FIRST_ROW_RANGE)
  9.         Set trg = .Resize(Me.Rows.Count - .Row + 1)
  10.     End With
  12.     Dim irg As Range: Set irg = Intersect(trg, Target)
  13.     If irg Is Nothing Then Exit Sub
  15.     Dim drg As Range
  16.     Set drg = Intersect(irg.EntireRow, Me.Columns(STAMP_COLUMN))
  18.     Dim Stamp As String: Stamp = Now & vbCrLf & Environ("USERNAME") & vbCrLf & Application.UserName
  20.     Application.EnableEvents = False
  21.         drg.Value = Stamp
  22.     Application.EnableEvents = True
  24. End Sub
英文:

A Worksheet Change: Multiple Excel Tables (ListObjects)

  • Note that it is assumed that you have added a 'stamp' column to each table.

I’d like to time stamp and add application username multiple different ranges on the same worksheet when any changes are made to rows in each range

  1. Private Sub Worksheet_Change(ByVal Target As Range)
  3.     Dim lo As ListObject, irg As Range, drg As Range, urg As Range
  4.     
  5.     For Each lo In Me.ListObjects
  6.         With lo.DataBodyRange
  7.             Set irg = Intersect(.Resize(, .Columns.Count - 1), Target)
  8.             If Not irg Is Nothing Then
  9.                 Set drg = Intersect(irg.EntireRow, .Columns(.Columns.Count))
  10.                 If urg Is Nothing Then
  11.                     Set urg = drg
  12.                 Else
  13.                     Set urg = Union(urg, drg)
  14.                 End If
  15.                 Set irg = Nothing ' reset for the next iteration
  16.             End If
  17.         End With
  18.     Next lo
  19.                         
  20.     If urg Is Nothing Then Exit Sub
  21.                     
  22.     Dim Stamp As String: Stamp = Now _
  23.         & vbLf & Environ("USERNAME") & vbLf & Application.UserName
  25.     Application.EnableEvents = False
  26.         urg.Value = Stamp
  27.     Application.EnableEvents = True
  28.                     
  29. End Sub

Initial Post

A Worksheet Change: Time Stamp and User

<!-- language: lang-vb -->

  1. Private Sub Worksheet_Change(ByVal Target As Range)
  2.     
  3.     Const FIRST_ROW_RANGE As String = &quot;B3:CA3&quot;
  4.     Const STAMP_COLUMN As String = &quot;A&quot;
  5.     
  6.     Dim trg As Range
  7.     
  8.     With Me.Range(FIRST_ROW_RANGE)
  9.         Set trg = .Resize(Me.Rows.Count - .Row + 1)
  10.     End With
  11.     
  12.     Dim irg As Range: Set irg = Intersect(trg, Target)
  13.     If irg Is Nothing Then Exit Sub
  14.     
  15.     Dim drg As Range
  16.     Set drg = Intersect(irg.EntireRow, Me.Columns(STAMP_COLUMN))
  17.     
  18.     Dim Stamp As String: Stamp = Now _
  19.         &amp; vbCrLf &amp; Environ(&quot;USERNAME&quot;) &amp; vbCrLf &amp; Application.UserName
  20.     
  21.     Application.EnableEvents = False
  22.         drg.Value = Stamp
  23.     Application.EnableEvents = True
  25. End Sub

huangapple
  • 本文由 发表于 2023年3月21日 03:06:33
  • 转载请务必保留本文链接：https://go.coder-hub.com/75794331.html
  • excel
  • timestamp
  • vba
  • worksheet
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定