Variable Text in Comments box.

[Steve Jones]

Hi

Is it possible to have for example the contents of cell A1 & A2 in the
comments box?

Therefore changing the comment as the cells change.

I have tried looking on Contextures site.

Thanks very much

Steve

 

[Mike H]

Hi,

You don't give too much details but this enters a comment in C1 comprising
the Text in A1 & A2. Right click your sheet tab, view code and paste this in

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

Mike

 

[Steve Jones]

Thanks Mike - excellent.

Hi,

You don't give too much details but this enters a comment in C1 comprising
the Text in A1 & A2. Right click your sheet tab, view code and paste this
in

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

Mike

 

[Steve Jones]

Hi Mike

I used the code in a blank spreadsheet and it does exactly what I wanted.

In my spreadsheet however the results in cells A1 and a2 are text and
results from a lookup table.

Because I'm not entering anything in cells A1 and A2 the code doesn't run.

Any ideas?

Thanks once again

Steve