Inserting dates into comments automatically problem

[akaj321akaj]

First off here is the code im using for this

Private Sub worksheet_change(ByVal Target As Range)
     'This sub is to  record cell  changes and use corresponding cells to record  date time, user id and pc id.
    If Target.Cells.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range("A2:ae3000")) Is Nothing Then
        Target.AddComment.Text Now & FindNetUserName & "  " & FindComputerName
    End If
    If Target.Cells.Count > 1 Or Target.HasFormula Then Exit Sub
     'this sub forces all text into uppercase
    On Error Resume Next
    Application.EnableEvents = False
    Target = UCase(Target)
    Application.EnableEvents = True
    On Error GoTo 0
End Sub

What it does is it inserts a comment with the current date onto the cell that i just edited.
The problem im having with it is that whenever i goto edit the box that i just did it comes up with a error and doesnt work.
Im trying to make it to where i can delete the selected cell and it will also delete the comment, allowing me to enter a new value and it also add a new comment without crashing?
Any ideas on what i can do to make this work? its greatly appreciated!

 

[Marcelo Branco]

Hi,

Maybe, instead of Now try CStr(Now)

I'm assuming that FindUserName and FindComputerName are defined and working ok.

M.

 

[akaj321akaj]

still giving me the same problems

 

[hyat]

Try:

Private Sub worksheet_change(ByVal Target As Range)
     'This sub is to  record cell  changes and use corresponding cells to record  date time, user id and pc id.
    If Target.Cells.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range("A2:ae3000")) Is Nothing Then
        Target.ClearComments
        Target.AddComment.Text Now & FindNetUserName & "  " & FindComputerName
    End If
    If Target.Cells.Count > 1 Or Target.HasFormula Then Exit Sub
     'this sub forces all text into uppercase
    On Error Resume Next
    Application.EnableEvents = False
    Target = UCase(Target)
    Application.EnableEvents = True
    On Error GoTo 0
End Sub

 

[hyat]

And if you want the cell to contain no comment if it is blank then:

Private Sub worksheet_change(ByVal Target As Range)
     'This sub is to  record cell  changes and use corresponding cells to record  date time, user id and pc id.
    If Target.Cells.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range("A2:ae3000")) Is Nothing Then
        Target.ClearComments
        If Len(Target.Value) > 0 Then Target.AddComment.Text Now & FindNetUserName & "  " & FindComputerName
    End If
    If Target.Cells.Count > 1 Or Target.HasFormula Then Exit Sub
     'this sub forces all text into uppercase
    On Error Resume Next
    Application.EnableEvents = False
    Target = UCase(Target)
    Application.EnableEvents = True
    On Error GoTo 0
End Sub

 

[Marcelo Branco]

hyat, good point.

Target.ClearComments
and
CStr(Now)

worked perfect for me

M.

 

[hyat]

Thanks for confirming M.

 

[Marcelo Branco]

Thanks for confirming M.

hyat,

You are welcome.

Let me ask:it worked for you without the CStr(Now)? Didnt work for me...

 

[hyat]

Yes it is working for me both ways. Excel 2007, Windows XP here.

 

[Marcelo Branco]

Yes it is working for me both ways. Excel 2007, Windows XP here.

Yes, you are right. Working both way also here. Excel 2010 Win 7