taking text in a cell and putting it into a comment for that cell WHEN YOU CLICK ON THE CELL...

K

kbishop94

Active Member
Joined
Dec 5, 2016
Messages
476
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
the last part in the title of my request here ("WHEN YOU CLICK ON A CELL") is what I am really after...
CURRENTLY I have code in my worksheet that when a commandbutton is clicked, it takes all the text within a RANGE OF CELLS ( which is: Range(Cells(370, 9), Cells(rCol, 10)) ) and puts them into comments for all of them... the problem doing it this way is its takes awhile (maybe 8-10 seconds... maybe more) for all of the comments to load. But I dont really need or want ALL of the comments, it would be much more efficient and easier to have the user click on a certain cell (within the same range shown in the code below, preferably) and THEN only copy the text from that one cell that is clicked into a comment... not ALL of the cells within the range.

my code:
Code: 
Private Sub cmdShowComment_Click()
' commandbutton for SHOWING comments from the PROBLEM and ACTION columns
    
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 EACH CELL WITHIN A RANGE OF CELLS 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 TOO WIDE WHERE THE USER CANNOT SEE ALL OF THE COMMENTS.[/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

screenshot of how it looks after the commandbutton is selected and the text within the range of cells are loaded into comments.

Currently when I run the cursor over the cell it shows the comment as shown... that is really what I would like it to do without having to hit a commandbutton in order to load all of the comments. I just would like it to load the comment for that cell when it is clicked on ... (I'm not even sure if this is possible, tho.)

ac33mr.jpg
 
Last edited:

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Sort by date Sort by votes
Try this:

This script will run when you change or enter any value in any cell on the active sheet in the range

"A1:E100"

Try this and see if it works for you and if you want this same script in all sheets get back with me.

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  5/23/2019  9:53:20 PM  EDT
If Not Intersect(Target, Range("A1:E100")) Is Nothing Then
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
Dim lArea As Long
Application.EnableEvents = False
If Target.Value <> "" Then
With Target
        .AddComment
                    .Comment.Visible = False
                    .Comment.Text Text:=Target.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
                    .Value = ""
                    
                    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
End If
Application.EnableEvents = True
End If
End Sub
 
Upvote 0
My Aswer Is This said:
Try this:

This script will run when you change or enter any value in any cell on the active sheet in the range

"A1:E100"

Try this and see if it works for you and if you want this same script in all sheets get back with me.

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  5/23/2019  9:53:20 PM  EDT
If Not Intersect(Target, Range("A1:E100")) Is Nothing Then
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
Dim lArea As Long
Application.EnableEvents = False
If Target.Value <> "" Then
With Target
        .AddComment
                    .Comment.Visible = False
                    .Comment.Text Text:=Target.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
                    .Value = ""
                    
                    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
End If
Application.EnableEvents = True
End If
End Sub
Click to expand...

Thank you for the help and for the code.

It semi-works..... ;)

Its 'cutting' the entire text (not copying) within the cell and then it places that into the comment. So, its doing exactly what I what, except I need the code not to cut (and remove) the contents of the cell, but to copy everything in that cell and then to the comment. Can that be done?

THanks for you help!
 
Upvote 0
Not sure why you would want to see the data in the cell and the comment but you must have your reasons.

Try this:

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  5/24/2019  6:06:57 PM  EDT
If Not Intersect(Target, Range("A1:E100")) Is Nothing Then
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
Dim lArea As Long

If Target.Value <> "" Then
With Target
        .AddComment
                    .Comment.Visible = False
                    .Comment.Text Text:=Target.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
                    
                    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
End If
End If
End Sub
 
Upvote 0
My Aswer Is This said:
Not sure why you would want to see the data in the cell and the comment but you must have your reasons.

Try this:

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  5/24/2019  6:06:57 PM  EDT
If Not Intersect(Target, Range("A1:E100")) Is Nothing Then
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
Dim lArea As Long

If Target.Value <> "" Then
With Target
        .AddComment
                    .Comment.Visible = False
                    .Comment.Text Text:=Target.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
                    
                    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
End If
End If
End Sub
Click to expand...

Thank you, that did the trick!
icon14.png




and... , I dont want to bore you.. but I do have a reason. ; )

The workbook for this code lists 'incidents' that have occurred where I work (not so much from a safety standpoint as you would probably normally would think of, but more of an incident from a quality standpoint.)

Pictured below is the main worksheet that you see when the workbook opens up. At the top are categories that summarize some of the data for some key metrics. Below that are the incidents that have previously been entered. The button that is green is the one that is currently filtering the data for the particular column... all the fields going across in row 17 can be clicked in and they will filter for that field.

2w36hrs.jpg


The main way to view one of the incidents in detail is to open up one of the forms and use it to view or edit it.. Here is the editing form for incident # 18-492:

9r5da1.jpg


The 2 columns/cells that I have applied the code to that you provided me with (thank you, again, btw) are for the columns that contain the "problem" and the "action" fields. Ususally, these 2 fields are rather lengthy. I want to keep all the rows at the same height for uniformity reason. But, this does not allow the text in those columns to be readily viewed... So, thats why i wanted the code so that if someone wants to take a quick peek at one of those fields,but doesnt want to take the time to open a form to view it, now they can just click on it and see the comment.

Here is what your code allows it to do now... shown below is the same content that is in the "description of the problem" field in the userform picture shown above:
2a75u7p.jpg


This is another way to make it easier for the user to see everything that is in these 2 columns without having to open up one of the forms. (plus I am going to get around to implementing some restrictions that wont allow the userforms to be open but will still allow the comments to be visible.)

Also (something else I didnt mention) I do have additional code that whenever the workbook is opened, and before its updated by any of the forms, all the existing comments within the range are removed. (that way if any of the content in any of those previously populated comment cells changes, the new text will then be populated when (and if) its clicked on to run the code to copy the text into a new comment.

Many thanks again.. have a great weekend!
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

edge37
  • Solved
Inserting more than one comment in a cell
Replies
4
Views
293
edge37
edge37
M
  • Question Question
Space between paragraphs from a cell in wb to bookmark in word.
Replies
0
Views
229
molesy01
M
edge37
  • Solved
Make a formula return a different text when I click on a cell outside of a specified range. (??)
2
Replies
13
Views
908
kevin9999
K
I
  • Solved
Current Code taking 10 Hours to run. Need Help to Optimize
Replies
5
Views
512
ItalianPlatinum
I
I
  • Question Question
How do you update all existing Notes Previously known as comments and put something in the text field using VBA Code
Replies
1
Views
1K
sijpie
sijpie

Forum statistics

Threads
1,223,885
Messages
6,175,180
Members
452,615
Latest member
bogeys2birdies

Share this page

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
Back
Top