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.

 

[Jaafar Tribak]

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.

Hi ,

Did you try the answer I gave in post#16 ?

 

[My Aswer Is This]

No. It's not my posting. I'm just wondering did the original poster get a answer that worked for him. I saw your code but never saw if this worked for the original poster.

Hi ,

Did you try the answer I gave in post#16 ?

 

[StephenCrump]

Did you try the answer I gave in post#16 ?

I did. Thanks Jaafar, neat.

It would be nice if this approach allowed the user to edit the comment's Text directly, without having to click first inside the TextFrame,
i.e. similar to the SendKeys "+{F2}" approach of Post 14.

Could this be accommodated?

 

[Jaafar Tribak]

I did. Thanks Jaafar, neat.

It would be nice if this approach allowed the user to edit the comment's Text directly, without having to click first inside the TextFrame,
i.e. similar to the SendKeys "+{F2}" approach of Post 14.

Could this be accommodated?

Tahnks Stephen

Actually, that's exactly what my code does at least in my machine... My code is supposed to allow the user to edit the comment's Text directly, without having to click first inside the TextFrame that's why I am using Application.CommandBars.FindControl(ID:=1401).Execute (which is like RightClicking the Comment bx and choosing Edit Text menu)

SendKeys is not always reliable.

 

[StephenCrump]

Actually, that's exactly what my code does at least in my machine... My code is supposed to allow the user to edit the comment's Text directly, without having to click first inside the TextFrame that's why I am using Application.CommandBars.FindControl(ID:=1401).Execute (which is like RightClicking the Comment bx and choosing Edit Text menu)

SendKeys is not always reliable.

Ahh! The problem was my using an ActiveX command button to trigger AddOrEditComment. It works as you describe otherwise.

And I agree, it's better than using SendKeys.

Thanks

 

[Jaafar Tribak]

@StephenCrump

Setting the TakeFocusOn_Click Property to False should solve the problem with the ActiveX commendbutton.

 

[StephenCrump]

Setting the TakeFocusOn_Click Property to False should solve the problem with the ActiveX commendbutton.

Duhh me! Thanks

 

[JamesPW]

OK. I took another approach to do what I was looking for-
See code
InsertNewComment is triggered by a button on the toolbar
At the end of InsertNewComment I have an open comment that I can add text to. Stays open until I hit another button on the toolbar which triggers CloseComment which closes the comment.
InsertNewComment works great. CloseComment works great unless I put the cursor in the comment box as if to add text. Then I get Run-time Error '1004': Command not available in microsoft excel for macintosh on the line Selection.Comment.Visible = False.
Side note: If I change AddComment ("") to AddComment ("some text") I get a comment with "some text" in it. As long as I don't click the mouse within the comment it closes fine when I hit the close button. It's only if I click the mouse in the comment as if I was going to add text that it crashes.

I've googled the snot out of it, searched the forum for anything remotely related. Nada
Any help would be greatly appreciated.

Option Explicit
Dim oldcell As String
Dim NewCell As String
===========================
Sub InsertNewComment2()
'where am I?
'Save address for CloseComment macro.
oldcell = ActiveCell.Address
'Does a Comment already exist? If not, add one
If Selection.Comment Is Nothing Then
Selection.AddComment ("")
End If
'Format comment. Runs for new AND old comment
With Selection.Comment.Shape.TextFrame
.Characters.Font.Size = 12
.Characters.Font.Bold = False
End With
With Selection.Comment
.Shape.Width = 200
.Shape.Height = 75
End With
'Open comment then exit, leaving comment open for editing. This works fine.

Selection.Comment.Visible = True

End Sub
====================

Sub CloseComment()
'Macro will crash if the active cell is not the one the comment is attached to. This line makes sure it is
'OldCell comes from InsertNewComment macro
range(oldcell).Activate
'Close comment
Selection.Comment.Visible = False
End Sub

 

[Michael M]

I still stand by post #8

 

[JamesPW]

============
Post#8 :
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
============

Yes, Thanks for the tip
Your post #8 is why I did it this way