Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cell address in a range | Excel Discussion (Misc queries) | |||
Cell Reference with Range Name | Excel Worksheet Functions | |||
Urgent date/scheduling calc needed | Excel Worksheet Functions | |||
checking that cells have a value before the workbook will close | Excel Worksheet Functions | |||
cell comment | Excel Discussion (Misc queries) |