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,199
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,199
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,555
...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,199
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,285
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,199
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,082,273
Messages
5,364,159
Members
400,784
Latest member
reddsables

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top