首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >MS Excel工作表更改旧单元格值对新值的事件保存记录

问MS Excel工作表更改旧单元格值对新值的事件保存记录
EN

Stack Overflow用户
提问于 2016-02-19 18:05:51
回答 1查看 2.7K关注 0票数 2

我是这个论坛的新手,但由于我当前角色的VBA需求,在过去的几个月里,我一直在积累我的编码经验。今天的问题让我浏览了很多网站(还有我的Excel用于Dummies的书),但我还没有完全解决它。

我试图在Excel中为我们公司的风险登记册制作一个审计跟踪器文件。其想法是,一旦建立了风险登记册,任何更改都将创建一个审计跟踪(在一个单独的选项卡上),它同时显示旧记录和新记录。

我已经使用Change事件处理程序编写了代码。每当发生更改时,我希望我的宏触发,并执行以下操作:

1.引用旧的单元格值(用户刚刚覆盖的内容)

2.跳转到“审核跟踪”选项卡并粘贴两份完整的风险记录--每个风险记录都是一行数据,占据17列

3.在这17列的第一份副本中,计算编辑了哪一列,并用旧的单元格值替换这个单元格(在步骤1中捕获)

4.插入时间戳

5.有条件格式,突出显示已更改此函数的记录,因为我已经在电子表格中设置了它

6.跳转回用户刚刚编辑的单元格(在“风险登记册”选项卡上)

我已经管理了步骤1、2和4-7,但是我在让代码将“旧单元格值”输入到‘审核跟踪器’选项卡的正确位置时遇到了问题。如果我手动定义要粘贴到其中的单元格范围,我就可以得到它,但我似乎不能使它具有动态性,这样它就可以自动识别用户正在更改的字段,并确保在审计跟踪中修改相同的字段。

对于"PasteRange.Value =工作表“(”风险登记册“).Range(”oldValuePaste“)行为什么不起作用,我真的很感激。

我的代码如下:

代码语言:javascript
复制
    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命令,但我仍然看到一些屏幕闪烁--有什么想法吗?

提前感谢您的帮助!

EN

回答 1

Stack Overflow用户

回答已采纳

发布于 2016-02-19 18:42:07

在查看您的代码时,我看到了一些我认为不会像您想象的那样起作用的事情,并且还认识到您的代码可以变得简单得多,只需要从Worksheet_Change事件中调用。

下面是重构代码,如果您有问题,请告诉我:

代码语言:javascript
复制
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 Sub
票数 1
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/35511913

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档