Code seems to crash after clearing comments

Hello Excel users and experts,

Excell 2002

I offered this code to a poster about four or five posts below this one
(Lucas Budlong, 1:13PM) and now I find that when I clear the series of the
comments it produces in column A, it will not work again until I close Excel
and reopen. That ain't no good!!

Any ideas where I am going wrong?

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Application.EnableEvents = False
If Target <> Range("F1") Then Exit Sub
Range("A100").End(xlUp).Offset(1, 0).Select

With Selection
.AddComment
.Comment.Text Text:=Range("E1").Value & ":" _
& Chr(10) & Range("F1").Value
.Comment.Visible = True
.Value = Range("F1").Value
End With

Range("F1").ClearContents
Range("F1").Select
Application.EnableEvents = True
End Sub

Thanks,
Howard

The problem is
Application.EnableEvents = False
If Target <> Range("F1") Then Exit Sub

You never set EnableEvents back to True.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com






"L. Howard Kittle" <[email protected]> wrote in message
news:[email protected]...
> Hello Excel users and experts,
>
> Excell 2002
>
> I offered this code to a poster about four or five posts below
> this one (Lucas Budlong, 1:13PM) and now I find that when I
> clear the series of the comments it produces in column A, it
> will not work again until I close Excel and reopen. That ain't
> no good!!
>
> Any ideas where I am going wrong?
>
> Option Explicit
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> On Error Resume Next
> Application.EnableEvents = False
> If Target <> Range("F1") Then Exit Sub
> Range("A100").End(xlUp).Offset(1, 0).Select
>
> With Selection
> .AddComment
> .Comment.Text Text:=Range("E1").Value & ":" _
> & Chr(10) & Range("F1").Value
> .Comment.Visible = True
> .Value = Range("F1").Value
> End With
>
> Range("F1").ClearContents
> Range("F1").Select
> Application.EnableEvents = True
> End Sub
>
> Thanks,
> Howard
>
>
>

Thanks Chip, that makes sense now that I've been enlightened.

BTW, I was mistaken on who I offered this code, it was in Functions, not
Programming.

Regards,
Howard

"L. Howard Kittle" <[email protected]> wrote in message
news:[email protected]...
> Hello Excel users and experts,
>
> Excell 2002
>
> I offered this code to a poster about four or five posts below this one
> (Lucas Budlong, 1:13PM) and now I find that when I clear the series of the
> comments it produces in column A, it will not work again until I close
> Excel and reopen. That ain't no good!!
>
> Any ideas where I am going wrong?
>
> Option Explicit
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> On Error Resume Next
> Application.EnableEvents = False
> If Target <> Range("F1") Then Exit Sub
> Range("A100").End(xlUp).Offset(1, 0).Select
>
> With Selection
> .AddComment
> .Comment.Text Text:=Range("E1").Value & ":" _
> & Chr(10) & Range("F1").Value
> .Comment.Visible = True
> .Value = Range("F1").Value
> End With
>
> Range("F1").ClearContents
> Range("F1").Select
> Application.EnableEvents = True
> End Sub
>
> Thanks,
> Howard
>
>
>