Adding Comments

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,728
Office Version
2013
Platform
Windows
I has been following this posting from the beginning.
I still do not understand why you need to stop the script. Do something then start the script again.

Here is a simple little script which will activate when you enter any value in column A

A Comment will be added to the cell with the value you entered in column A

And the script formats the comment

Now this script can be modified to format comment exactly like you want.

But wanting to stop a script enter a comment format the comment then start the script again can require a lot of scripting.

Try this and see how it works.

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A:A")) Is Nothing Then
'Modified  12/17/2018  2:44:06 AM  EST
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
Dim cmt As Comment
Set cmt = Target.Comment
 
    If cmt Is Nothing Then
    
    Target.AddComment Text:=Target.Value
        Target.Comment.Shape.TextFrame.Characters.Font.Size = 16
        Target.Comment.Shape.TextFrame.AutoSize = True
        Target.Comment.Shape.Fill.ForeColor.SchemeColor = 3
 End If
End If
End Sub
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

JamesPW

New Member
Joined
Nov 4, 2005
Messages
26
To answer your question-
What I want to do is automate changing the format of a comment whenever I added or opened it. I use a lot of comments. Doing it whenever I added or opened a comment was a real pain in the butt. My first thought was to change the defaults. Unfortunately Excel 2011 for mac doesn't give you access to them.

Next thought was to write a macro. Opening and formatting a new or existing comment was easy. Leaving it open for editing then closing it when the user was done had me stumped. Closing it by clicking on another cell (like a normal a comment) didn’t work.

So… After a week or so of trying to do it in one macro, it came to me to try to do it in 2 macros. One to add/open the comment for editing (InsertNewComment). Another one to close it (CloseComment). Each one would be triggered by a button on the toolbar. The code I came up with is below. It works great except-

Yesterday I was getting an error from CloseComment whenever it ran with the cursor in the comment.- Run-time Error '1004': “Command not available in microsoft excel for macintosh” on the line Selection.Comment.Visible = False. Worked fine as long as the cursor wasn’t in the comment. The weird thing is (and I have no idea why), it’s working fine today. Go figure. I’ll be back with more info if it starts crashing again. Here’s the code. Any ideas of what would cause the error or why it isn't happening today would be greatly appreciated.
Thanks in advance.

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
Selection.Comment.Visible = True
End Sub
======================
Sub CloseComment()
'The cell the comment is attached has to be active to close the comment.
'Typing in the comment de-activates the cell
'This code re-activates the cell the comment is attached to so it can be closed
'Original cell address comes from the macro that added the comment- InsertNewComment2.
'Activate the original comment cell, close comment, end
NewCell = ActiveCell.Address
'MsgBox "OldCell= " & oldcell & vbCrLf & _
'"NewCell= " & NewCell
range(oldcell).Activate
Selection.Comment.Visible = False
End Sub
 

JamesPW

New Member
Joined
Nov 4, 2005
Messages
26
Sorry, My bad.
Better late than never-
macBook pro, latest updates
Excel 2011, latest updates
 

JamesPW

New Member
Joined
Nov 4, 2005
Messages
26
StephenCrump- to see that code I'd have to sign up to that website but it looks a little too iffy for my tastes. I'd still like to see that code. I assume you're a member, any chance you could post the code here?
 

StephenCrump

Well-known Member
Joined
Sep 18, 2013
Messages
3,636
Office Version
365
Platform
Windows
to see that code I'd have to sign up to that website ...
I have just implemented Jaafar's code, which is included in Post#16.

You don't need to sign up to box.com to look at my workbook. There is a download button anyone can use.
 

Watch MrExcel Video

Forum statistics

Threads
1,089,913
Messages
5,411,191
Members
403,347
Latest member
do17d0n

This Week's Hot Topics

Top