Adding Comments

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
7,603
Office Version
2016
Platform
Windows
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 ?
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,728
Office Version
2013
Platform
Windows
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

Well-known Member
Joined
Sep 18, 2013
Messages
3,640
Office Version
365
Platform
Windows
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?
 
Last edited:

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
7,603
Office Version
2016
Platform
Windows
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.
 
Last edited:

StephenCrump

Well-known Member
Joined
Sep 18, 2013
Messages
3,640
Office Version
365
Platform
Windows
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

Well-known Member
Joined
Dec 5, 2002
Messages
7,603
Office Version
2016
Platform
Windows
@StephenCrump

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

JamesPW

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

Well-known Member
Joined
Oct 27, 2005
Messages
18,418
Office Version
2013
Platform
Windows
I still stand by post #8
:devilish:
 
Last edited:

JamesPW

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

Watch MrExcel Video

Forum statistics

Threads
1,089,946
Messages
5,411,453
Members
403,372
Latest member
andrepi

This Week's Hot Topics

Top