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
17,975
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
17,975
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,523
...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
17,975
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,080
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
17,975
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,078,504
Messages
5,340,779
Members
399,395
Latest member
KJAC

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top