Adding Comments

JamesPW · Dec 13, 2018

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.

My Aswer Is This · Dec 13, 2018

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

JamesPW · Dec 13, 2018

I'm still holding out for the "Wait for MouseClick" approach. I'll give it another day of research and if I don't figure it out I'll go to the InputBox code which I'm thankful you took the time to give me.
I'll let everyone know what I find late tomorrow.

Michael M · Dec 13, 2018

yell out if you need more rope....

StephenCrump · Dec 13, 2018

JamesPW said:
I'm still holding out for the "Wait for MouseClick" approach.

It can be done that way, but with a lot more code and overhead.

Just FYI ... here's some MouseClick code from Jaafar Tribak:https://www.mrexcel.com/forum/excel-questions/921978-nifty-class-trapping-mouse-clicks-cells.html

You could also use the native Workbook_SheetSelectionChange event, although this won't trigger if you exit the comment by clicking back on the ActiveCell.

As Michael noted, you'd need to trigger another macro, and if you have some comments visible and some not, your code would need to keep track of which comment you'd just edited to make sure you hid the right one.

Here's a simpler way you may want to adapt:

Code:

Sub AddOrEditComment()
     
     On Error Resume Next
     ActiveCell.AddComment
     On Error GoTo 0
     
     SendKeys "+{F2}"
     
 End Sub

Jaafar Tribak · Dec 14, 2018

JamesPW said:
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 am not sure I understand ... Are you talking about adding the text to the comment manually ?

Jaafar Tribak · Dec 14, 2018

I think I understand what you mean.

See if this works for you :

Add a new Class Module to your project and give the class module the name of CAllowCommentEditing

Place the following code in the class module:

Code:

Option Explicit

Private WithEvents cmbrs As CommandBars
Private oComment As Comment

Public Sub AddComment(ByVal Comment As Comment)
    Set cmbrs = Application.CommandBars
    Set oComment = Comment
    Comment.Visible = True
    Comment.Shape.Select
    Application.CommandBars.FindControl(ID:=1401).Execute
End Sub

Private Sub cmbrs_OnUpdate()
    On Error Resume Next
     If Selection.Name <> oComment.Shape.Name Then
         Debug.Print "Comment Unselected"
         oComment.Visible = False
        Set cmbrs = Nothing
    End If
End Sub

Then (in a Standard Module) just pass the comment to the AddComment Public Method of the Class as follows :

Code:

Option Explicit

Private oCAllowCommentEditing As CAllowCommentEditing

Sub AddOrEditComment()

    Dim oComment As Comment
    
    If ActiveCell.Comment Is Nothing Then
        Set oComment = ActiveCell.AddComment
[B][COLOR=#008000]        'Set the desired comment attributes here (Font, ForeColor, Comment Size .. etc )[/COLOR][/B]
    Else
        Set oComment = ActiveCell.Comment
    End If
    
    Set oCAllowCommentEditing = New CAllowCommentEditing
    oCAllowCommentEditing.AddComment oComment
End Sub

JamesPW · Dec 14, 2018

Michael M-
Look up the thread. I did post it and it WAS the whole macro. Why would I only post part of a macro? I didn't want to post it at first because, as I said, it was a mess and I didn't think you needed it to answer me question anyway. After your post, I cleaned it up and posted it.

I thought I said thanks but I don't see it. It may have been another thread. So I'll say it now- Thanks to all!

Michael M · Dec 14, 2018

James
We almost always request the entire macro, mainly because of set variables earlier in the code and other things that may be adversely affecting the section of code in question!
Sometimes the user THINKS it is a certain of code that is causing the issue, when it's actually something that they don't / can't see .
Hope you managed to get the desired result....

Michael M · Dec 14, 2018

AND
I hope you realise the text at the bottom of my post regarding post whole macros and please and thank you......are simply part of my signature block, which is an inclusion of EVERY one of my posts !!

My Aswer Is This · Dec 14, 2018

So did we ever get a answer to this question that worked. If so I would like to see it. I know there was some code posted but I did not see any answer as to did this work.

I do realize almost anything is possible with vba with enough coding.

Adding Comments

JamesPW

Board Regular

My Aswer Is This

Well-known Member

Excel Facts

JamesPW

Board Regular

Michael M

Well-known Member

StephenCrump

MrExcel MVP

Jaafar Tribak

Well-known Member

Jaafar Tribak

Well-known Member

JamesPW

Board Regular

Michael M

Well-known Member

Michael M

Well-known Member

My Aswer Is This

Well-known Member

Similar threads

Forum statistics

Share this page

Adding Comments

Board Regular

Well-known Member

Excel Facts

Board Regular

Well-known Member

MrExcel MVP

Well-known Member

Well-known Member

Board Regular

Well-known Member

Well-known Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock