copy string from a cell into a comment... but... copy it whenever the cell is clicked

[kbishop94]

The way my current code works is like this:

I have a command button that when clicked, it copies all the text inside all the cells within a certain range, and inserts them into comments for those cells. This works 'ok', but it takes a really long time when its running the code... I even reduced the range to only cells to only those that are the ones that are most likely to be viewed (recent additions to the spreadsheet.)

I also have code that sizes the comment box when it shows on the screen to a size that accommodates a more appropriate width (because by default it was waaaaay too wide to even read what is in most of them.)

Lastly, the final code is for when a cell is CLICKED it positions the comment box more centered in the screen (again, because without it many times depending on where the viewer has the scoll bars positioned, the box is shown in a manner where you cant see everything in the box. With this code I found it puts the box where it can be seen in its entirety.)

CODE FOR TAKING THE CONTENTS OF EACH CELL WITHIN A RANGE AND INSERTING THEM INTO COMMENTS FOR THAT CELL:

Private Sub cmdShowComment_Click()
[COLOR=#008000]' commandbutton for SHOWING comments from the PROBLEM and ACTION columns[/COLOR]
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
    Dim ws As Worksheet
    Dim iCell As Range
    Dim rCol As Long
        rCol = ActiveSheet.UsedRange.Rows.Count
    Dim lArea As Long
    Dim MyComments As Comment
    For Each ws In ActiveWorkbook.Worksheets
    
[COLOR=#008000]' THE CODE TAKES THE STRING FROM A SMALL RANGE OF CELLS (BECAUSE DOING THIS FOR[/COLOR]
[COLOR=#008000]' ALL TAKES TOO MUCH TIME)AND INSERTS EACH ONE INTO A COMMENT FOR THAT RESPECTIVE CELL.[/COLOR]
    For Each iCell In Range(Cells(370, 9), Cells(rCol, 10))
            With iCell
                If CStr(.Value) <> "" Then
                    .ClearComments
                    .AddComment
                    .Comment.Visible = False
                    .Comment.Text Text:=CStr(.Value)
                    .Comment.Shape.TextFrame.Characters.Font.ColorIndex = 5
                    .Comment.Shape.TextFrame.Characters.Font.Size = 11
                    .Comment.Shape.TextFrame.Characters.Font.Name = "Lucida Fax"
                    .Comment.Shape.TextFrame.AutoSize = True
                End If
                
[COLOR=#008000]' THIS CODE RESIZES THE COMMENT BOX SO ITS NOT SO WIDE WHERE THE USER CANNOT SEE ALL[/COLOR]
[COLOR=#008000]' OF THE CONTENTS AS IT APPEARS ON THE SCREEN.[/COLOR]
                lArea = .Comment.Shape.Width * .Comment.Shape.Height
                If .Comment.Shape.Width > 262 Then
                    .Comment.Shape.Width = 262
                    .Comment.Shape.Height = (lArea / 250) * 1.3
                End If
            End With
        Next
    Next
Application.Calculation = xlAutomatic
Application.ScreenUpdating = True
End Sub

THIS CODE IS FOR WHEN THE CELL IS CLICKED, THE COMMENT BOX IS SHOWN NEAR THE CENTER OF THE SCREEN:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
[COLOR=#008000] 'www.contextures.com/xlcomments03.html[/COLOR]
 Dim rng As Range
 Dim cTop As Long
 Dim cWidth As Long
 Dim cmt As Comment
 Dim sh As Shape
Application.DisplayCommentIndicator _
      = xlCommentIndicatorOnly
Set rng = ActiveWindow.VisibleRange
cTop = rng.Top + rng.Height / 2
cWidth = rng.Left + rng.Width / 2
If ActiveCell.Comment Is Nothing Then
  'do nothing
Else
   Set cmt = ActiveCell.Comment
   Set sh = cmt.Shape
   sh.Top = cTop - sh.Height / 2
   sh.Left = cWidth - sh.Width / 2
   cmt.Visible = True
End If
End Sub

So, what I am wanting to do, is modify that last piece of code (when the cell is clicked) and only then take the contents of that particular cell and copy it into a comment and show it in the same way (instead of hitting the command button and coping the contents of a complete range of cells where its rather time consuming to process that code.) Please and Thank you for any help to modify the code to do just that.

Here is a screen shot (for example purposes) of what is shown on the screen when cell I472 is clicked: