我是这个论坛的新手,但由于我当前角色的VBA需求,在过去的几个月里,我一直在积累我的编码经验。今天的问题让我浏览了很多网站(还有我的Excel用于Dummies的书),但我还没有完全解决它。
我试图在Excel中为我们公司的风险登记册制作一个审计跟踪器文件。其想法是,一旦建立了风险登记册,任何更改都将创建一个审计跟踪(在一个单独的选项卡上),它同时显示旧记录和新记录。
我已经使用Change事件处理程序编写了代码。每当发生更改时,我希望我的宏触发,并执行以下操作:
1.引用旧的单元格值(用户刚刚覆盖的内容)
2.跳转到“审核跟踪”选项卡并粘贴两份完整的风险记录--每个风险记录都是一行数据,占据17列
3.在这17列的第一份副本中,计算编辑了哪一列,并用旧的单元格值替换这个单元格(在步骤1中捕获)
4.插入时间戳
5.有条件格式,突出显示已更改此函数的记录,因为我已经在电子表格中设置了它
6.跳转回用户刚刚编辑的单元格(在“风险登记册”选项卡上)
我已经管理了步骤1、2和4-7,但是我在让代码将“旧单元格值”输入到‘审核跟踪器’选项卡的正确位置时遇到了问题。如果我手动定义要粘贴到其中的单元格范围,我就可以得到它,但我似乎不能使它具有动态性,这样它就可以自动识别用户正在更改的字段,并确保在审计跟踪中修改相同的字段。
对于"PasteRange.Value =工作表“(”风险登记册“).Range(”oldValuePaste“)行为什么不起作用,我真的很感激。
我的代码如下:
Dim oldValue As Variant
Dim LastRow As Long
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.ScreenUpdating = False
If Not Intersect(Target, Range("b13:r13")) Is Nothing Then
oldValue = Target.Value
End If
Application.ScreenUpdating = True
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
If Not Intersect(Target, Range("b13:r14")) Is Nothing Then
If Target.Value <> oldValue Then
'MsgBox "You just changed " & Target.Address
Cells(65, 5).Value = oldValue 'this cell is a named range called: OldValuePaste
Cells(66, 5).Value = Target.row 'this cell is a named range called: OldValueRowNumber
Cells(67, 5).Value = Target.Column 'this cell is a named range called: OldValueColumnNumber
Range(Cells(Target.row, 2), Cells(Target.row, 18)).Copy
'Cells(70, 2).PasteSpecial xlPasteValues
Call Paste_on_AuditSheet
Sheets("Risk Register").Activate
Target.Select
Application.CutCopyMode = False
End If
End If
Application.ScreenUpdating = True
End Sub
_____________________________________________________________________________________________________
Sub Paste_on_AuditSheet()
Application.ScreenUpdating = False
Dim LastRow As Long
Dim ColNum As Long
Dim PasteRange As Range
ColNum = OldValueColumnNumber
Sheets("Audit trail").Select
'MsgBox "Activated " & ActiveSheet.Name
'Find the last used row in a Column: column B in this example
With ActiveSheet
LastRow = .Cells(.Rows.Count, "B").End(xlUp).row
End With
Set PasteRange = Cells(LastRow, ColNum)
'The following two lines bring in the new data and paste into old record and new record sections:
Cells(LastRow + 1, 2).PasteSpecial xlPasteValues
Cells(LastRow + 1, 20).PasteSpecial xlPasteValues
'Then this line goes back over the piece just pasted in and changes one cell in "old record" section to what it was prior to the edit:
'PasteRange.Value = Worksheets("Risk Register").Range("oldValuePaste")
'Above line of code is not working, but can get it to do the right thing using this code (although it's not dynamic):
Range("E3").Value = Worksheets("Risk Register").Range("oldValuePaste")
'Add a time stamp:
Cells(LastRow + 1, 1) = Now
Application.ScreenUpdating = True
End Sub最后一点--尽管我反复使用了Application.ScreenUpdating命令,但我仍然看到一些屏幕闪烁--有什么想法吗?
提前感谢您的帮助!
发布于 2016-02-19 18:42:07
在查看您的代码时,我看到了一些我认为不会像您想象的那样起作用的事情,并且还认识到您的代码可以变得简单得多,只需要从Worksheet_Change事件中调用。
下面是重构代码,如果您有问题,请告诉我:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("b13:r14")) Is Nothing Then
'get oldValue
Dim sNewVal As String, sOldVal As String
sNewValue = Target.Value 'store current or "new" value since this is what is stored after the cell change takes place
With Application
.EnableEvents = False 'turns off event firing so the code will not go into endless loop
.Undo 'undo the change (to store old value in next line)
End With
sOldValue = Target.Value 'store old value
Target.Value = sNewValue 'reset new value
Dim lCol As Long
lCol = Target.Column 'which column of data was changed
'assumes columns A-Q are 17 columns
Me.Range(Me.Cells(Target.Row, 1), Me.Cells(Target.Row, 17)).Copy
With Sheets("Audit Trail")
Dim lRow As Long
lRow = .Range("B" & .Rows.Count).End(xlUp).Offset(1).Row
.Range("B" & lRow).PasteSpecial xlPasteValues
.Range("B" & lRow + 1).PasteSpecial xlPasteValues
.Range("A" & lRow).Value = Now
.Cells(lRow, lCol + 1).Value = sOldValue 'store old value in first pasted line ... add 1 since starting from column B
End With
End If
Application.EnableEvents = True
End Subhttps://stackoverflow.com/questions/35511913
复制相似问题