Create comment from cell range
 

Does anyone know if it's possible to automatically create notes based off of
data from a range of cells?

For example Sheet1, Cell A2 would have a note that displays the data that
exists in the cells on Sheet 2, Cells B5-B7. As the data in Cells B5-B7
change, the note would automatically change as well, since it is displaying
whatever is contained in those cells at that time.

Create comment from cell range
 

This is easy using VBA:


Private Sub Worksheet_Change(ByVal Target As Range)
Dim r1 As Range
Dim r2 As Range
Set r1 = Range("B5:B7")
Set r2 = Sheets("Sheet1").Range("A2")

If Intersect(Target, r1) Is Nothing Then
Exit Sub
Else
s = Range("B5").Value & Range("B6").Value & Range("B7").Value
If r2.Comment Is Nothing Then
Else
r2.Comment.Delete
End If
r2.AddComment
r2.Comment.Visible = False
r2.Comment.Text Text:=s
End If
End Sub

Please that this goes in Sheet2 code, not a regular module, not in Sheet1
code.


Whenever the contents of B5 or B6 or B7 changes in Sheet2, the comment in
cell A2 in Sheet1 will be updated.
--
Gary's Student


"MikeG" wrote:

Does anyone know if it's possible to automatically create notes based off of
data from a range of cells?

For example Sheet1, Cell A2 would have a note that displays the data that
exists in the cells on Sheet 2, Cells B5-B7. As the data in Cells B5-B7
change, the note would automatically change as well, since it is displaying
whatever is contained in those cells at that time.

Create comment from cell range
 

Gary,
That's great! I honestly didn't expect an answer, but this works perfectly.
Thanks so much!

"Gary''s Student" wrote:

This is easy using VBA:


Private Sub Worksheet_Change(ByVal Target As Range)
Dim r1 As Range
Dim r2 As Range
Set r1 = Range("B5:B7")
Set r2 = Sheets("Sheet1").Range("A2")

If Intersect(Target, r1) Is Nothing Then
Exit Sub
Else
s = Range("B5").Value & Range("B6").Value & Range("B7").Value
If r2.Comment Is Nothing Then
Else
r2.Comment.Delete
End If
r2.AddComment
r2.Comment.Visible = False
r2.Comment.Text Text:=s
End If
End Sub

Please that this goes in Sheet2 code, not a regular module, not in Sheet1
code.


Whenever the contents of B5 or B6 or B7 changes in Sheet2, the comment in
cell A2 in Sheet1 will be updated.
--
Gary's Student


"MikeG" wrote:

Does anyone know if it's possible to automatically create notes based off of
data from a range of cells?

For example Sheet1, Cell A2 would have a note that displays the data that
exists in the cells on Sheet 2, Cells B5-B7. As the data in Cells B5-B7
change, the note would automatically change as well, since it is displaying
whatever is contained in those cells at that time.