VBA code to record only data entered in a cell with a comment

[Zimmerman]

The code I have below records the last three entries with a comment box with the users name, date, and that they did. The question I have is how do I get this to record only if data is entered? Right now if I hit a back space in the cell or clear contents it will record that. And I'd prefer not to have it record it.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim CellComment As Comment
Dim CommentText As String
Dim aryVals As Variant
Dim i As Long
If Target.Count = 1 Then
Set CellComment = Target.Comment
If Not CellComment Is Nothing Then
If InStr(1, CellComment.Text, "User: ") > 0 Then
If InStr(1, Mid(CellComment.Text, InStr(1, CellComment.Text, ":") + 1), ",") Then
aryVals = Split(Mid(CellComment.Text, InStr(1, CellComment.Text, ":") + 1), ",")
Select Case Len(CellComment.Text) - Len(Replace(CellComment.Text, ",", vbNullString))
Case 1
aryVals = Array(Environ("Username ") & Chr(32) & "entered " & Chr(32) & Target.Value & Chr(32) & "on " & Format(Now(), "mm/dd/yy"), _
aryVals(0), _
aryVals(1))
Case 2
For i = UBound(aryVals) To LBound(aryVals) + 1 Step -1
aryVals(i) = aryVals(i - 1)
Next
aryVals(LBound(aryVals)) = Environ("Username ") & Chr(32) & "entered " & Chr(32) & Target.Value & Chr(32) & "on " & Format(Now(), "mm/dd/yy")
Case Else
Exit Sub
End Select
CellComment.Text "User: " & Join(aryVals, ",")
Else
CellComment.Text "User: " & Environ("Username ") & Chr(32) & "entered " & Chr(32) & Target.Value & Chr(32) & "on " & Format(Now(), "mm/dd/yy") & ", " & _
Mid(CellComment.Text, InStr(1, CellComment.Text, ":") + 1)
End If
End If
Else
Target.AddComment "User: " & Environ("Username ") & Chr(32) & "entered " & Chr(32) & Target.Value & Chr(32) & _
"on " & Format(Now(), "mm/dd/yy")
End If
End If
End Sub

Excel 2003
Windows XP

 

[shg]

Maybe like this?

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim sText     As String
    Dim oComm     As Comment
    Dim asComm()  As String
 
    If Target.Count = 1 Then
        If Not IsEmpty(Target.Value2) Then
            sText = "User " & Environ("username") & " entered " & Target.Value & " on " & Format(Date, "mm/dd/yyyy")
            Set oComm = Target.Comment
 
            If oComm Is Nothing Then
                Target.AddComment sText
                Target.Comment.Shape.TextFrame.AutoSize = True
 
            Else
                asComm = Split(oComm.Text, vbLf)
                If UBound(asComm) > 1 Then ReDim Preserve asComm(1)
                oComm.Text sText & vbLf & Join(asComm, vbLf)
                oComm.Shape.TextFrame.AutoSize = True
            End If
        End If
    End If
End Sub

 

[Zimmerman]

Absolutely brilliant!! It works perfect. One more question I have and hopefully you can help me. I keep the sheet protected but there are times when I need to unprotect it for some modifications or to add more things in. So the problem I have is I can unprotect it but as soon as I do something to the sheet on any cell, it automatically protects it again then. I can see why it does, but is there a way in which I could put in if a user unprotects the sheet that it will stay unprotected until they protect it again regardless of what they do to it when it's unprotected?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim sText As String
Dim oComm As Comment
Dim asComm() As String
If Target.Count = 1 Then
ActiveSheet.Unprotect Password:="password"
If Not IsEmpty(Target.Value2) Then
sText = "User " & Environ("username") & " entered " & Target.Value & " on " & Format(Date, "mm/dd/yyyy")
Set oComm = Target.Comment

If oComm Is Nothing Then
Target.AddComment sText

Else
asComm = Split(oComm.Text, vbLf)
If UBound(asComm) > 1 Then ReDim Preserve asComm(1)
sText = sText & vbLf & Join(asComm, vbLf)
oComm.Text sText
oComm.Shape.TextFrame.AutoSize = True
ActiveSheet.Protect Password:="password"
End If
End If
End If
End Sub

 

[shg]

    If Target.Count = 1 Then
[COLOR=red]       If Me.ProtectContents Then Me.Protect UserInterfaceOnly:=True[/COLOR]
        
        If Not IsEmpty(Target.Value2) Then

Don't add the other Protect line.

 

[Zimmerman]

That seemed to fix that but it brought up a new problem. Now when i enter any data in any cell it's asking me for a password. Since the comments that are being added automatically with VBA I was needing to put that active sheet unprotect and then protect in in order for the code to actually run. Any ideas on how to get around that?

 

[shg]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim bProt     As Boolean
    Dim sText     As String
    Dim oComm     As Comment
    Dim asComm()  As String
 
    If Target.Count = 1 Then
        If Not IsEmpty(Target.Value2) Then
            bProt = Me.ProtectContents
            If bProt Then Me.Unprotect
 
            sText = "User " & Environ("username") & " entered " & Target.Value & " on " & Format(Date, "mm/dd/yyyy")
            Set oComm = Target.Comment
 
            If oComm Is Nothing Then
                Target.AddComment sText
                Target.Comment.Shape.TextFrame.AutoSize = True
 
            Else
                asComm = Split(oComm.Text, vbLf)
                If UBound(asComm) > 1 Then ReDim Preserve asComm(1)
                oComm.Text sText & vbLf & Join(asComm, vbLf)
                oComm.Shape.TextFrame.AutoSize = True
            End If
 
            If bProt Then Me.Protect
        End If
    End If
End Sub

 

[Zimmerman]

That works the same as the other. While the sheet is unprotected everything is fine and I can change the sheet as I want without it protecting itself. Now when I protect the sheet and type in any cells the unprotect Sheet password box pops up. So what i really need is to figure out how to unprotect the comments from running when i type in the cells. Hopefully this makes sense. Thanks again for all your help though.

 

[shg]

Now when I protect the sheet and type in any cells the unprotect Sheet password box pops up.

If the cells are locked and the sheet is protected, you should get a message, "The cell or chart you are trying to change is protected ...", not a password prompt.

What other behavior would you expect?

 

[Zimmerman]

The cells that I'm trying to type in aren't locked. I have the sheet protected and only some cells aren't locked, these are the ones the user has to fill in. What I'm trying to do is have the comment keep track of the changes that were made in each cell. But the VBA code won't run without unprotecting the unlocked cells. Maybe I'm doing it wrong or something or am going at this the wrong way.