Showing multiple cells as comments

R

RGreen

Hello

Spreadsheets contains, several rows and columns
For example Column ak45:AK56 has data,
In a different part of the spreadsheet, I stated in ss33 "=ak54",
What I like is a macro, when my cell ss33 is active, I would like to see the
information from ak45:AK56

If I in su49 which I may have stated su49 "=al74, I would like to see the
information from al65:al56

I was playing around with Gord's macro, but it only reference,
onecell.offset(0,1), how Can I see the whole range in a comment?

Thanks,
Rick
 
R

RGreen

correction in my second example the range I would like to see as comments is
al65 to al76.

basically I have data in Column A1:a7 and when I go to g15 I would like to
see what is in a1:a7 as a comment.

and If I go to g16 I would like to see what is in a8:a15 as a comment.

and if I go to h15 I would like to see what is in b1:b7 as a comment.

Please help as this dynamic type of stuff gets me confused.

Thanks,

Rick
 
P

p45cal

RGreen;481502 said:
correction in my second example the range I would like to see a
comments is
al65 to al76.

basically I have data in Column A1:a7 and when I go to g15 I woul
like to
see what is in a1:a7 as a comment.

and If I go to g16 I would like to see what is in a8:a15 as a comment.

and if I go to h15 I would like to see what is in b1:b7 as a comment.

Please help as this dynamic type of stuff gets me confused.

Thanks,

Rick
Click to expand...

See if this begins to do what you're looking for. Right-click on th
tab of the relevant sheet, choose *View code...* and place the followin
code at the flashing cursor:private Su
Worksheet_SelectionChange(ByVal Target As Range)
With ActiveCell
If .Row > 14 And .Column > 6 Then
rng = Application.Transpose(Cells((ActiveCell.Row - 15) * 7 + 1
ActiveCell.Column - 6).Resize(7))
'cmnt = Join(rng, vbLf) 'use this instead of line below to have
vertical list in the comment.
cmnt = Join(rng, ",")
.ClearComments
.AddComment
.Comment.Visible = False
.Comment.Text Text:=cmnt
End If
End With
End Sub
You should select the cell for the comment to update.
Tidiest if under the dropdown menus *Tools|Options*, *View *tab, th
*Comment Indicator only* radio button is opted for
 
B

Billy Liddel

Try these in the worksheet code tab

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim lRow As Long, lCol As Long
Dim nRows As Integer, lrngRow As Long, lrngCol As Long
Dim rng2Show As Range
Dim c As Variant, msg As String
Dim cmt As Object

For Each cmt In ActiveSheet.Comments
cmt.Delete
Next
lRow = ActiveCell.Row: lCol = ActiveCell.Column

If lRow <= 14 Or lCol < 6 Then
Exit Sub
ElseIf lRow > 14 And lCol >= 6 Then
lrngRow = lRow - 14
lrngRow = lRow + 7
Set rng2Show = Range(Cells(lRow, lCol - 6), Cells(lrngRow, lCol - 6))
End If

For Each c In rng2Show
If Not IsEmpty(c) Then
msg = msg & c & vbLf
End If
Next
msg = Left(msg, Len(msg) - 1)

With ActiveCell
'.ClearComments
.AddComment
.Comment.Text Text:=msg
.Comment.Visible = True
End With

FormatComment

End Sub

Sub FormatComment()
ActiveCell.Comment.Shape.Select True
Selection.ShapeRange.ScaleHeight 1.76, msoFalse, msoScaleFromTopLeft
Selection.ShapeRange.ScaleWidth 0.41, msoFalse, msoScaleFromTopLeft
Selection.ShapeRange.ScaleHeight 0.88, msoFalse, msoScaleFromTopLeft
ActiveCell.Select
End Sub


Regards
Peter A
 
Top