Enter Time and Date into Excel Comment Box 1

[TriRyche]

I know barely enough c++ to be dangerous, was proficient with BASIC in the 80's, and can (somewhat) grasp the syntax in the VBA editor. I am currently teaching myself VBA, but do not want to reinvent the wheel.
It would be greatly appreciated if someone will point me in the right direction.
(or at least let me know if I'm in over my head!!).
Thanks
I am using the follow code to date-time stamp entries in the adjecent cell: (Thanks Zathras and jcrater!)

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    With Target
       If .Count > 1 Then Exit Sub
       If Not Intersect(Range("A1:AA2000"), .Cells) Is Nothing Then
          Application.EnableEvents = False
          With .Offset(0, 1)
             .NumberFormat = "MM/dd/yy hh:mm:ss"
             .Value = Now
           End With
           Application.EnableEvents = True
       End If
    End With
End Sub

I would like to put the date and time stamp into the comment box of the cell the data is entered in.
Thanks for any help, it is greatly appreciated!

 

[SkipVought]

Hi,

    With Target
        .AddComment
        With .Comment
            .Visible = False
            .Text Text:=Format(Now, "MM/dd/yy hh:mm:ss")
        End With
    End With

Skip,
[sub]
[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart! [/sub]

 

[TriRyche]

Thanks Skip!

Could you help me boobie proof it?
It works until re-entering data.
I'll settle for doing nothing upon re-entering data or appending to the comment.

Thanks!

 

[SkipVought]

    With target
        If .Comment Is Nothing Then
            .AddComment
            With .Comment
                .Visible = False
                .Text Text:=Format(Now, "MM/dd/yy hh:mm:ss")
            End With
        End If
    End With

Skip,
[sub]
[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart! [/sub]

 

[TriRyche]

I still get run time error 1004 if I try to re-enter data into a cell.

 

[SkipVought]

please post your code for this procedure.

Skip,
[sub]
[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart! [/sub]

 

[TriRyche]

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    With Target
       If .Count > 1 Then Exit Sub
       If Not Intersect(Range("A1:IV65536"), .Cells) Is Nothing Then
          Application.EnableEvents = False
          .AddComment
        With .Comment
            .Visible = False
            .Text Text:=Format(Now, "MM/dd/yy hh:mm:ss")
        End With
            With Target
        If .Comment Is Nothing Then
            .AddComment
            With .Comment
                .Visible = False
                .Text Text:=Format(Now, "MM/dd/yy hh:mm:ss")
            End With
        End If
    End With
           Application.EnableEvents = True
       End If
    End With
End Sub

 

[SkipVought]

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    With Target
       If .Count > 1 Then Exit Sub
       If Not Intersect(Range("A1:IV65536"), .Cells) Is Nothing Then
            Application.EnableEvents = False
            If .Comment Is Nothing Then
                .AddComment
                With .Comment
                    .Visible = False
                    .Text Text:=Format(Now, "MM/dd/yy hh:mm:ss")
                End With
            End If
            Application.EnableEvents = True
        End If
    End With
End Sub

Skip,
[sub]
[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart! [/sub]

 

[TriRyche]

Thanks a million!

I swear I entered it like that before, but I got blank comments, and thefore befuddled the code as such.

The self teaching proicess is going slow due to other obligations. I am looking forward to looking back at my starter projects and laughing. But for now this really helps.

BTW, when I would run into an error, I couldn't get the code to run again short of exiting Excel and restarting.
Is there a way to refresh or reactivate the VBA code?

Thanks again for all your help!

 

[SkipVought]

Hi,

If the code errors while EnableEvents is False, then no event will fire.

Rather than exit just run a line of code to assign EnableEvents True during your testing.

Also, this is where Error processing is essential. Use OnError Resume next and then test at various places in your code in order to recover gracefully.

Skip,
[sub]
[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart! [/sub]