Adding Comments

JamesPW

New Member
Joined
Nov 4, 2005
Messages
26
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

Well-known Member
Joined
Oct 27, 2005
Messages
18,138
Office Version
2013
Platform
Windows
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

New Member
Joined
Nov 4, 2005
Messages
26
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-known Member
Joined
Oct 27, 2005
Messages
18,138
Office Version
2013
Platform
Windows
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

New Member
Joined
Nov 4, 2005
Messages
26
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

Well-known Member
Joined
Sep 18, 2013
Messages
3,554
...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
 

JamesPW

New Member
Joined
Nov 4, 2005
Messages
26
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

Well-known Member
Joined
Oct 27, 2005
Messages
18,138
Office Version
2013
Platform
Windows
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

Well-known Member
Joined
Jul 5, 2014
Messages
16,272
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

Well-known Member
Joined
Oct 27, 2005
Messages
18,138
Office Version
2013
Platform
Windows
@My Aswer Is This
I thought that first up, but suggested the InputBox as an obvious option....guess not..:banghead:
 

Forum statistics

Threads
1,081,543
Messages
5,359,431
Members
400,526
Latest member
Brook1083

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top