Adding Comments

JamesPW

Board Regular
Joined
Nov 4, 2005
Messages
51
Office Version
  1. 2019
Platform
  1. MacOS
I'm writing a macro to add a custom comment. I have everything up to adding text to the comment working. How do I stop the macro from advancing while I'm adding text?
Then once I'm done adding text, how do I detect a mouse click so I can turn the macro back on so I can finish and close the comment?

I'd include the code I have but it's a real mess.

Thanks in advance.
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
I'd include the code I have but it's a real mess.
So why is that a problem ??
We all have to learn to modify code sometime !!
 
Upvote 0
I looked for snippets of code that I can use or understand and modify for my situation. I found lots of code but I didn't understand them enough to extract what I needed. And believe me, I tried.

But I did figure out how to keep the comment open for adding text. It's do nothing. Now I have to figure out how to close it. I might use "If ActiveCell <> original cell (meaning the user clicked somewhere outside the comment) close and continue but I'm hoping there's a clean and easy way to do it. I'm going to search on "mouseClick" to see if I can use it. But it has to keep the comment open if I click in the comment.
Wish me luck
 
Upvote 0
Well, it appears yoy don't want to share the code...so I'll suggest you use an InputBox to provide your comment text to the comment bubble !
 
Upvote 0
Well, it appears yoy don't want to share the code...so I'll suggest you use an InputBox to provide your comment text to the comment bubble !

I'm sorry, I forgot. Here's the code
What I'm looking for is how do I do what I call "MouseClick" in my comments.

Sub InsertNewComment()

'where am I?
location = ActiveCell.Address
MsgBox "You are at " & location
'Does Comment Already Exist?
If Selection.Comment Is Nothing Then
MsgBox "Cell doesn't have a comment. Set format."
Selection.AddComment ("")
' Selection.AddComment ("12pt, not bold")
With Selection.Comment.Shape.TextFrame
.Characters.Font.Size = 12
.Characters.Font.Bold = False
End With
With Selection.Comment
.Shape.Width = 150
.Shape.Height = 200
End With
Selection.Comment.Visible = True
MsgBox "New comment, should be open to add text." & vbCrLf & _
"Exit If/ and go to MouseClick test."
Else
ActiveCell.Comment.Visible = True
MsgBox "Cell already has a comment, should be open to edit text. " & vbCrLf & _
"Exit If-Then, go to MouseClick test"
End If
MsgBox "Either way, wait here and test for click." & vbCrLf & _
"ActiveCell is " & ActiveCell & vbCrLf & _
"Location is " & location
If ActiveCell.Address <> location Then
MsgBox "MouseClick. Close comment, go to end"
Selection.Comment.Visible = False
Else
MsgBox "Stay in MouseClick test."
End If
MsgBox "After Selection.Comment.Visible=False " & vbCrLf _
& "Comment should be closed"
ActiveCell.Comment.Visible = False

End Sub
'
 
Upvote 0
...so I'll suggest you use an InputBox to provide your comment text to the comment bubble !

As Michael M suggests, perhaps:

Code:
Sub AddOrEditComment()

    Dim MyCell As Range
    Dim response As Variant

    Set MyCell = ActiveCell
    
    If MyCell.Comment Is Nothing Then
        response = Application.InputBox("New Comment", "Enter your new comment for cell " & MyCell.Address(0, 0))
        If response = False Then GoTo UserCancelled
        With MyCell.AddComment
            .Visible = True
            .Shape.Width = 150
            .Shape.Height = 200
            With .Shape.TextFrame.Characters
                .Text = response
                .Font.Size = 12
                .Font.Bold = False
            End With
        End With
    Else
        response = Application.InputBox("Edit Comment", "Enter your replacement comment for cell " & MyCell.Address(0, 0), MyCell.Comment.Text)
        If response <> False Then MyCell.Comment.Shape.TextFrame.Characters.Text = response
    End If

UserCancelled:

End Sub
 
Upvote 0
Thanks, but...
You're putting up an InputBox for the comment text then copying it into the comment. I want to add text to the comment directly. As I said and my code shows, I've got everything figured out except how to use a mouse click outside the comment to trigger the Comment.Visible = false

Thanks anyway
 
Upvote 0
I don't believe you can do it that way !!
Once the macro is stopped to insert a comment, a new macro would have to be initiated to continue
 
Upvote 0
My thought's also. I have been watching this thread and have wondered when someone would point that out.

I don't believe you can do it that way !!
Once the macro is stopped to insert a comment, a new macro would have to be initiated to continue
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

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