VBA and Excel 2019 Comments

[JamesPW]

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

 

[Dan_W]

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]

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

 

[Dan_W]

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

 

[Dan_W]

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]

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]

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]

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.

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

 

[JamesPW]

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]

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.