Hello Excel users and experts,

I offered this code to a poster about 4 or 5 posts below this one (Gary
11:35) and now I find that the codes seems to crash after I clear the series
of comments it compiles in column A. Not 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 may lie in the lines

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

Here, you exit the sub without restoring EnableEvents back to
True.


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



"L. Howard Kittle" <(E-Mail Removed)> wrote in message
news:hs6dnQaigdpLcGXeRVn-(E-Mail Removed)...
> Hello Excel users and experts,
>
> I offered this code to a poster about 4 or 5 posts below this
> one (Gary 11:35) and now I find that the codes seems to crash
> after I clear the series of comments it compiles in column A.
> Not 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, I changed...

If Target <> Range("F1") Then Exit sub

To:

If Target <> Range("F1") Then
Application.EnableEvents = True
Exit Sub
End If

WOW, seems to have solved the problem.
Have to admit, I do not understand why the "select rows > delete comments"
event would bust the code. Also I delete cell contents... but whatever???

Thanks a ton Chip, always a pleasure!
Regards,
Howard

New code...

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Application.EnableEvents = False

If Target <> Range("F1") Then
Application.EnableEvents = True
Exit Sub
End If

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



"Chip Pearson" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> The problem may lie in the lines
>
>> Application.EnableEvents = False
>> If Target <> Range("F1") Then Exit Sub
>
> Here, you exit the sub without restoring EnableEvents back to True.
>
>
> --
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel
> Pearson Software Consulting, LLC
> www.cpearson.com
>
>
>
> "L. Howard Kittle" <(E-Mail Removed)> wrote in message
> news:hs6dnQaigdpLcGXeRVn-(E-Mail Removed)...
>> Hello Excel users and experts,
>>
>> I offered this code to a poster about 4 or 5 posts below this one (Gary
>> 11:35) and now I find that the codes seems to crash after I clear the
>> series of comments it compiles in column A. Not 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
>>
>
>