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.

 

[My Aswer Is This]

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

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]

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

 

[StephenCrump]

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

Did you try Jaafar's code? It does exactly what you're looking for, and far more elegantly.

Here it is on a plate: https://app.box.com/s/ul85syr3xsvzjy8os2fla2u0u0ek8nb7

 

[StephenCrump]

Excel 2011 for mac ....

The fact that you're using Mac is generally useful information to know up front.

 

[JamesPW]

Sorry, My bad.
Better late than never-
macBook pro, latest updates
Excel 2011, latest updates

 

[JamesPW]

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]

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.