Adding Comments

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
7,493
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 ?
 

StephenCrump

Well-known Member
Joined
Sep 18, 2013
Messages
3,555
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,493
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,555
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,493
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,199
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
 

Forum statistics

Threads
1,082,273
Messages
5,364,168
Members
400,785
Latest member
Mahar92

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