我目前有將所做的任何更改記錄到單獨的更改日志表中的代碼。我需要添加將用戶帶到更改日志中的最新條目的代碼，以便他們必須記錄更改原因。我正在探索這種被帶到該條目的選項，或者在進行更改時出現一個彈出文本框，提示用戶輸入一個注釋，然后該注釋將與該條目一起保存在日志中。
這是我的作業代碼：
Dim oldValue As String
Dim oldAddress As String
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim sSheetName As String
Data = "Data"
Dim ssSheetName As String
MoreData = "MoreData"
If ActiveSheet.Name <> "LogDetails" Then
Application.EnableEvents = False
Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = ActiveSheet.Name & " – " & Target.Address(0, 0)
Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 1).Value = oldValue
Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 2).Value = Target.Value
Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 3).Value = Environ("username")
Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 4).Value = Now
If ActiveSheet.Name = Data Then
Sheets("LogDetails").Hyperlinks.Add Anchor:=Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 5), Address:="", SubAddress:="'" & Data & "'!" & oldAddress, TextToDisplay:=oldAddress
ElseIf ActiveSheet.Name = MoreData Then
Sheets("LogDetails").Hyperlinks.Add Anchor:=Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Offset(0, 5), Address:="", SubAddress:="'" & MoreData & "'!" & oldAddress, TextToDisplay:=oldAddress
End If
Sheets("LogDetails").Columns("A:D").AutoFit
Application.EnableEvents = True
End If
End Sub
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Count = 1 Then
oldValue = Target.Value
End If
oldAddress = Target.Address
End Sub
uj5u.com熱心網友回復：
我忍不住做了一些重構：
為這樣的作業創建一個單獨的子例程總是一個好主意 - 然后從 worksheet_change-event 呼叫例程。
此外，我首先創建一個包含要記錄的值的陣列 - 然后將此陣列寫入日志表。通常這是出于性能原因 - 此日志記錄并非如此。
但正如您所看到的：閱讀和理解代碼要容易得多——因為讀者不必沿著長長的代碼行“走”來查看正在發生的事情。
通過使用目標范圍的變數，以后很容易選擇它。
關于您的基本問題： 此代碼首先通過輸入框向用戶詢問評論。如果他/她沒有給出答案，相應的單元格將被突出顯示，并再次要求用戶發表評論。
把它放到一個普通的模塊中
Public Sub addLogEntry(rgCellChanged As Range, oldValue As String, oldAddress As String)
Dim wsChanged As Worksheet
Set wsChanged = rgCellChanged.Parent
Dim wsLogData As Worksheet
Set wsLogData = ThisWorkbook.Worksheets("LogDetails")
'we don't need logging on the logsheet
If wsChanged Is wsLogData Then Exit Sub
'Get comment from user
Dim commentChange As String
commentChange = InputBox("Please enter a comment, why you made this change.", "Logging")
Application.EnableEvents = False
'Collect data to log
Dim arrLogData(6) As Variant
arrLogData(0) = wsChanged.Name & " - " & rgCellChanged.Address(0, 0)
arrLogData(1) = oldValue
arrLogData(2) = rgCellChanged.Value
arrLogData(3) = Environ("username")
arrLogData(4) = Now
arrLogData(6) = commentChange '>>> adjust the column in case your comment column is not G
'get cell to enter log data
Dim rgLogData As Range
Set rgLogData = wsLogData.Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
'write data
rgLogData.Resize(, 7).Value = arrLogData
'create hyperlink
wsLogData.Hyperlinks.Add rgLogData.Offset(, 5), Address:="", SubAddress:="'" & wsChanged.Name & "'!" & oldAddress, TextToDisplay:=oldAddress
'>>> optional: activate log sheet and select comment cell
'If user hasn't entered a comment, activate logsheet and cell
If LenB(commentChange) = 0 Then
wsLogData.Activate
MsgBox "Please enter the comment, why you made the change.", vbExclamation, "Logging"
rgLogData.Offset(, 6).Select
End If
Application.EnableEvents = True
End Sub
這就是您的 worksheet_change 的樣子
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
addLogEntry Target, oldValue, oldAddress
End Sub
另一個優點：如果代碼閱讀器了解了這一點，他/她會立即了解會發生什么（將添加一個日志條目） - 無需閱讀整個代碼即可理解它
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/467232.html