我有一个代码来记录excel表中的使用情况，但是我遇到一个错误，一个问题 [英] I have a code to log usage in an excel sheet, but I get one bug, and one issue

问题描述

这是一个通用的日志系统，这里和我自己已经创建了几个人。我为此感到骄傲...我遇到两个问题：如果有人可以帮助解决它是伟大的。

这是代码：

  Option Explicit 
 Dim PreviousValue 
 
 Private Sub Worksheet_Change（ByVal Target As Range）
 Dim sLogFileName As String，nFileNum As Long，sLogMessage As String 
 
 sLogFileName = ThisWorkbook.path& Application.PathSeparator& Log.txt
 
 On Error Resume Next'打开错误处理
如果Target.Value<> PreviousValue然后
'检查我们是否有错误
如果Err.Number = 13然后
 PreviousValue = 0 
结束如果
'关闭错误处理
 On Error GoTo 0 
 sLogMessage = Now& Application.UserName& 改细胞& Target.Address _ 
& from& PreviousValue& 到& Target.Value 
 
 nFileNum = FreeFile'下一个文件编号
打开sLogFileName为Append As #nFileNum'创建文件（如果不存在）
打印#nFileNum，sLogMessage'append信息
关闭#nFileNum'关闭文件
如果
结束Sub 
 
私有子Worksheet_SelectionChange（ByVal Target As Range）
 PreviousValue = Target（1 ）.Value 
 End Sub 
  

以下是两个问题。

1. 如果不止一次单元被选中，并尝试写入，则脚本错误输出。


2. 如果有人编辑一个单元格将其留空，它将显示 8/30/2012 1:45:01 PM Matthew Ridge将单元$ K $ 3从测试更改为而不是 8/30/2012 1:45:01 PM Matthew Ridge将单元格$ K $ 3从测试更改为空白或空白

解决方案

Matt

几件事

1. On Error Resume Next 不正确处理。应该避免，除非是绝对必要的。


2. 当您使用 Worksheet_Change 事件时，最好关闭事件，然后将其重新开始，以避免可能的无休止循环。


3. 如果您正在切换事件，则必须使用正确的错误处理。


4. 由于您只在 PreviousValue 中存储单个单元格，所以我假设您不希望代码在用户选择多个单元格时运行？

我认为这是您正在尝试的（未定义）？

  Option Explicit 
 
 Dim PreviousValue 
 
 Private Sub Worksheet_Change（ByVal Target As Range）
 Dim sLogFileName As String，nFileNum As Long，sLogMessage As String 
 Dim NewVal 
 
 On Error GoTo Whoa 
 
 Application.EnableEvents = False 
 
 sLogFileName = ThisWorkbook.Path& Application.PathSeparator& Log.txt
 
如果没有Target.Cells.Count> 1然后
如果Target.Value<> PreviousValue Then 
如果Len（Trim（Target.Value））= 0则_ 
 NewVal =BlankElse NewVal = Target.Value 
 
 sLogMessage = Now& Application.UserName& _ 
更改单元格& Target.Address& from& _ 
 PreviousValue& 到& NewVal 
 
 nFileNum = FreeFile 
打开sLogFileName作为#nFileNum 
打印#nFileNum，sLogMessage 
关闭#nFileNum 
结束如果
结束如果
 LetsContinue：
 Application.EnableEvents = True 
 Exit Sub 
 Whoa：
 MsgBox Err.Description 
 Resume LetsContinue 
 End Sub 
 
 Private Sub Worksheet_SelectionChange（ByVal Target As Range）
 PreviousValue = Target（1）.Value 
 End Sub 
  

This is a universal log system, that a few people here and myself have created. I'm rather proud of it... I am running into two issues... if someone can help with the sollution it be great.

Here is the code:

Option Explicit
Dim PreviousValue

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim sLogFileName As String, nFileNum As Long, sLogMessage As String

    sLogFileName = ThisWorkbook.path & Application.PathSeparator & "Log.txt"

 On Error Resume Next ' Turn on error handling
    If Target.Value <> PreviousValue Then
        ' Check if we have an error
        If Err.Number = 13 Then
           PreviousValue = 0
        End If
        ' Turn off error handling
        On Error GoTo 0
        sLogMessage = Now & Application.UserName & " changed cell " & Target.Address _
        & " from " & PreviousValue & " to " & Target.Value

        nFileNum = FreeFile                         ' next file number
        Open sLogFileName For Append As #nFileNum   ' create the file if it doesn't exist
        Print #nFileNum, sLogMessage                ' append information
        Close #nFileNum                             ' close the file
    End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    PreviousValue = Target(1).Value
End Sub

Here are the two issues.

1. If more than once cell is selected, and attempted to be written to, the script errors out.
2. If someone edits a cell and leaves it blank, it will show 8/30/2012 1:45:01 PM Matthew Ridge changed cell $K$3 from Test to instead of 8/30/2012 1:45:01 PM Matthew Ridge changed cell $K$3 from Test to Blank or Empty

解决方案

Matt

Few Things

1. On Error Resume Next is not proper handling. It should be avoided unless and until it is absolutely necessary.
2. When you are working with Worksheet_Change event, it is better to switch off events and then turn them back on at the end to avoid possible endless loop.
3. If you are switching events off then it is a must that you use proper error handling.
4. Since you are storing the just a single cell in the PreviousValue so I am assuming that you do not want the code to run when the user selects multiple cells?

I think this is what you are trying (UNTESTED)?

Option Explicit

Dim PreviousValue

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim sLogFileName As String, nFileNum As Long, sLogMessage As String
    Dim NewVal

    On Error GoTo Whoa

    Application.EnableEvents = False

    sLogFileName = ThisWorkbook.Path & Application.PathSeparator & "Log.txt"

    If Not Target.Cells.Count > 1 Then
        If Target.Value <> PreviousValue Then
            If Len(Trim(Target.Value)) = 0 Then _
            NewVal = "Blank" Else NewVal = Target.Value

            sLogMessage = Now & Application.UserName & _
            " changed cell " & Target.Address & " from " & _
            PreviousValue & " to " & NewVal

            nFileNum = FreeFile
            Open sLogFileName For Append As #nFileNum
            Print #nFileNum, sLogMessage
            Close #nFileNum
        End If
    End If
LetsContinue:
    Application.EnableEvents = True
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume LetsContinue
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    PreviousValue = Target(1).Value
End Sub

这篇关于我有一个代码来记录excel表中的使用情况，但是我遇到一个错误，一个问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！