Adding Comments

[JamesPW]

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.

 

[Michael M]

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 !!

 

[JamesPW]

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

 

[Michael M]

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 !

 

[JamesPW]

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
'

 

[StephenCrump]

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

As Michael M suggests, perhaps:

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

 

[JamesPW]

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

 

[Michael M]

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

 

[My Aswer Is This]

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

 

[Michael M]

@My Aswer Is This
I thought that first up, but suggested the InputBox as an obvious option....guess not..