More on text in Comment Boxes.........

[Steve Jones]

Mike H replied with the code below on 09.07 in answer to my question.

This works perfectly on a spreadsheet where you are entering the vlaues in
cells A1 & A2.

In my spreadsheet the results in cells A1 & A2 are as a result of a lookup
table after the user has selected from a list.

I am trying to return the values of cells A1 & A2 in a Comments box in for
example cell C1.

Thanks once again.

Steve



Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Not Intersect(Target, Range("A1:A2")) Is Nothing Then
Application.EnableEvents = False
With Range("C1")
.ClearComments
.AddComment
.Comment.Text Range("A1").Value & Range("A2").Value
End With
Application.EnableEvents = True
End If
End Sub

 

[Rick Rothstein \(MVP - VB\)]

Does this do what you want?

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Not Intersect(Target, Range("A1:A2").Precedents) Is Nothing Then
Application.EnableEvents = False
With Range("C1")
.ClearComments
.AddComment
.Comment.Text Range("A1").Value & Range("A2").Value
End With
Application.EnableEvents = True
End If
End Sub

Rick

 

[Steve Jones]

Thanks Rick - Excellent!

Does this do what you want?

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Not Intersect(Target, Range("A1:A2").Precedents) Is Nothing Then
Application.EnableEvents = False
With Range("C1")
.ClearComments
.AddComment
.Comment.Text Range("A1").Value & Range("A2").Value
End With
Application.EnableEvents = True
End If
End Sub

Rick