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

kbishop94

Active Member
Joined
Dec 5, 2016
Messages
322
Office Version
  1. 2010
Platform
  1. Windows
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:
Code:
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:
Code:
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.
icon14.png


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

ei8knr.jpg
 
Last edited:

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

Watch MrExcel Video

Forum statistics

Threads
1,127,974
Messages
5,627,949
Members
416,283
Latest member
casea14

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top