VBA and Excel 2019 Comments

JamesPW

New Member
Joined
Nov 4, 2005
Messages
35
I just up?graded to MS Office for Mac and Excel 2019 for Mac. I'm trying to duplicate macros I had in Excel 2011 in Excel 2019.
I'm finding there are many changes to VBA between 2011 and 2019..

My problem is with comments. I'm trying to set the text in the comment attached to the current cell to size 12, font bold to false, size to 200x75.
This is only for the currently selected cell, not across the whole spreadsheet.
Here's the code from Excel 2011,

Option explicit
Sub FormatComment()
'Format text
With Selection.Comment.Shape.TextFrame
.Characters.Font.Size = 12
.Characters.Font.Bold = False
End With
'Set size
With Selection.Comment
.Shape.Width = 200
.Shape.Height = 75
End With
Selection.Comment.Visible = True
End Sub

This runs fine in VBA 2011, in VBA 2019 it chokes on "With Selection.Comment.Shape.TextFrame"
Run-Time Error '1004':
Application-Defined or Object-Defined Error

I've googled the dickens out of this and have not found a solution.
So- how do you format a comment in VBA 2019
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Dan_W

Active Member
Joined
Jul 11, 2018
Messages
254
Office Version
  1. 365
Platform
  1. Windows
So I tried your code, and it worked fine for me. The only thing I would suggset is that you are trying to run your code against a cell that doesn't have a comment in it. What were previously called comments are now called 'notes' (but for VBA purposes are still called comments). But the new 'comments' that you see in the ribbon are different, and are referenced in VBA as the CommentThreaded object. As far as I am aware, you can't change the font properties of those.
 

JamesPW

New Member
Joined
Nov 4, 2005
Messages
35
Dan- thanks for your response
>> there is code prior to this code that adds a comment if one does not already exist.
>> I don't understand the 'notes' portion of your response and how it effects comments on cells. Could you expand in it? thanks
If you are referring to adding buttons on the ribbon and the inability to modify (or even see) them, I am painfully aware of this.

What system/ app versions are you running? This ran fine on a 2012 MacBook Pro running Mojave v10.14.6 v10.14.6, Office 2011, Excel for Mac 14.7.7 , VBA 14.7.7.
Now I'm on my new machine- 2020 MacBook Pro running Catalina 10.15.4, Office for Mac 2020, Excel for Mac 16.39 and VBA 7.1
In transferring other macros from one machine to the other I've run into code that would error and had to be re-written or avoided. It looks like there are some commands in VBA for Windows that are different/ just don't run in VBA for Mac. My gut is telling me you re not on a Mac.
Anyway-
I just copy/pasted the code you gave and it gagged on

"With Selection.Comment.Shape.TextFrame
.Characters.Font.Size = 12"

With Error 1004, application-defined or object-defined error.

Is there a setup command or configuration I am missing? Some code pre-sets I'm missing?

Do you or anyone else know of a compilation of Win VBA vs. Mac VBA commands?

Thanks
 
Last edited:

Dan_W

Active Member
Joined
Jul 11, 2018
Messages
254
Office Version
  1. 365
Platform
  1. Windows
This is what I meant when I referred to "Notes". I just wanted to make sure that you were not referring to the new "Comments" which are dealt with differently in VBA - here's an overview of the difference: Difference between threaded Comments and Notes

1596044210695.png
 

Dan_W

Active Member
Joined
Jul 11, 2018
Messages
254
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

But I think that the error message you're getting means that the problem is likely something else. Can you share more of the code? The use of Selection might be the problem.
 

JamesPW

New Member
Joined
Nov 4, 2005
Messages
35
OK. It looks like I want notes instead of comment. Let me play around with notes for a day or so and I'll update you. Thanks
 

JamesPW

New Member
Joined
Nov 4, 2005
Messages
35

ADVERTISEMENT

OK. I've looked at Comments vs Notes and it appears to me the difference between them is Comments are now "conversations". So I want "Notes"
I've scoured the web for information on excel "notes" and haven't found very much. 9/10 searches refer to the old comment (non-conversation) function. Even MS only has one thin page on NoteText. Fairly useless. Nowhere have I found anything on the conversation type comments

If you are aware of a good source of info on notes vs comments, please let me know.

Here's my code. It's test code and doesn't do much but the problem is below the End If where I try to format the note. When I run it I get
Runtime error '1004'
method 'TextFrame' of object 'shape' failed.

I have yet to find a "note" equivalent to "comment.shape.textframe".

One thing I've run into before is there are some commends that work in Win but don't on a Mac. "Dir" is one. Something about Mac and "sandboxing" that I don't fully understand.
Any ideas will be greatly appreciated.

Sub OpenComment()
'InsertNewComment Macro
'Triggered by Insert New Comment button on Home ribbon
Dim cmt As String
cmt = ActiveCell.NoteText 'old note
cmt = InputBox("Enter Note", Title2, cmt) 'ask for new note

l = Len(cmt)
'Where am I? Save address for Close macro."
loca = ActiveCell.Address

'is new note blank? (cmt =0)
If l = 0 Then 'cmt is empty, clear note,
ActiveCell.NoteText cmt

Else 'add new note
ActiveCell.NoteText cmt
cmtt = ActiveCell.NoteText
End If

'XXXXXXXXX important stuff below XXXXXX
With ActiveCell.Comment.Shape.TextFrame <<<<Runtime error 1004 here
.Characters.Font.Size = 12
.Characters.Font.Bold = False
End With
With ActiveCell.Comment
.Shape.Width = 200
.Shape.Height = 75
End With
ActiveCell.Comment.Visible = True

End Sub
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,430
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Are you protecting the cell or using it on a merged cell? asking because I just ran the code below on a cell with a comment (old terms, Note in new terms) in it using 365 on Windows 10 and it changed the font fine.

VBA Code:
   With Range("C7").Comment.Shape.TextFrame
        .Characters.Font.Size = 30
        .Characters.Font.Bold = False
    End With
 

JamesPW

New Member
Joined
Nov 4, 2005
Messages
35
Not protected (as far as I know), not merged cells. It's looking like a mac vs win thing. I'll update if I find anything out.
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,430
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
It's looking like a mac vs win thing.
Maybe, I haven't got a Mac in front of me to test but comments aren't something that I am aware of having a Mac v Win issue.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,117
Messages
5,546,038
Members
410,721
Latest member
adi772
Top