Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Showing Comments | Excel Discussion (Misc queries) | |||
Format multiple comments | Excel Discussion (Misc queries) | |||
how can we copy cells comments text and paste to cells | Excel Discussion (Misc queries) | |||
Comments not showing on peer computer | Excel Discussion (Misc queries) | |||
Excel XP: Showing Trace Precedents Arrows for Multiple Cells... | Excel Discussion (Misc queries) |