Showing multiple cells as comments
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 |
Showing multiple cells as comments
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 "RGreen" wrote: 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 |
Showing multiple cells as comments
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 "RGreen" wrote: 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 "RGreen" wrote: 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 |
All times are GMT +1. The time now is 08:32 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com