Adding Comments

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,293
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
 

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,555
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.
 

Forum statistics

Threads
1,082,342
Messages
5,364,783
Members
400,815
Latest member
gangstar67

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