locked
Add-in adding Range.Comment via VBA; where's it get the .Author from? RRS feed

  • Question

  • Before we go down the wrong track, I'm not asking about the author name that the Excel Add Comment UI puts in the actual comment text. I'm asking about the name that winds up showing here:

    I'm working on replacing an obsolete .XLA that, among other things, adds comments to cells. When I run the add-in, the comments it adds end up showing as added by "Author" as shown above. The .XLA file's properties has no author. Whatever I put in the document properties of my .XLSM, or .XLAM, I still seem to get my name there. Any idea how the existing .XLA gets "its" name there and I can't seem to stop my code from getting my name there instead? I can't find anything in the legacy Add-In VBA that's doing anything different from what I'm doing.

    Friday, November 9, 2018 1:09 AM

Answers

  • "Your code copies the current workbook username into the comment text..."

    No, it copies the application.username into the comment, which may or may not be the same as the workbook's author property.

    Indeed the Range.Comment.Author property is the application.username at the time the comment was added. I thought we had established that.

    It's a read only property, so the only way to change it would be delete the comment and add it back under a different, perhaps temporary, xl.username. That would be easy to do but ensure you've got bullet proof error handing if for use in other user's systems. You could also change it temporarily while adding new comments with code -

    sOrig = Application.UserName
    Application.UserName = "user ?"
    'add comment
    Application.UserName = sOrig 

    What you describe as a bot addin displaying your name in the status bar is misplaced. No bot or addin, it's been standard behaviour in Excel to display the text in the status bar as shown in your screenshot in all versions since at least XL-2000.

    I still don't know where your "Author" comes from. But I'm not clear if you have access to the code in your xla. You quote a line from it starting 'QueryRange.Cells(1,1)', QueryRange is not an Excel method, where did that come from...


    • Marked as answer by Dick Watson Saturday, November 10, 2018 3:52 PM
    Saturday, November 10, 2018 12:33 PM

All replies

  • Hi Dick,

    >> Any idea how the existing .XLA gets "its" name there and I can't seem to stop my code from getting my name there instead?

    According to your description, you want to remove the "Author" that shown in your picture.

    >> I can't find anything in the legacy Add-In VBA that's doing anything different from what I'm doing.

    Could you please provide more information?

    Best Regards,

    Yuki


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.


    Friday, November 9, 2018 3:10 AM
  • I wouldn't say I want to remove the "Author". What i want is to behave no differently than the existing Add-In. It has no document Author in document properties. It gets "Author" saved as the Range.Comment.Author when i run it. When I run my code, it gets my name there no matter what is or is not in the document Author property.

    Code, FWIW:

    Sub AddComment(strComment As String)
    
        Dim rngCmnt As Range
    
        Set rngCmnt = userDestRange.Cells(1, 1)
        
        rngCmnt.AddComment strComment & " " & Date
    
        ...
    
    End Sub
    

    Friday, November 9, 2018 4:20 AM
  • The line from the original Add-In:

            QueryRange.Cells(1, 1).AddComment transactionComment & Date
    

    Friday, November 9, 2018 4:24 AM
  • Hi Dick,

    For your information:

    Comment.Author Property (Excel)

    Best Regards,

    Yuki


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.

    Friday, November 9, 2018 5:13 AM
  • Was that supposed to be an answer to the question? It's a read-only property. You can't use that method call to SET the Author, only to find out who the author was...
    Friday, November 9, 2018 6:59 AM
  • I don't follow, when you add a comment manually the application's username is automatically entered, not the author in the file's document properties (though by default they are the same when a new workbook is created). When a comment is added with code nothing is automatically entered (until code adds some text). I don't recall any difference between xla v xlam in this respect.

    Your addin could return the name from Application.Username, but if it's applying "Author" I'd assume that's hard-coded in the routine that writes the comment text. Can you see the code that adds the comment? 

    Friday, November 9, 2018 5:02 PM
  • Neither the legacy AddIn or my development replacement XLSM do anything with the Range.Author property.

    The line from the legacy that adds the comment is shown above. Beyond that, all either does with the Comment is change some of the caracter formatting in the comment and set the size of its shape. I simplified how they construct the comment text in both cases quoted, but in neither case is it anything besides Range.AddComment strMyComment

    When the legacy .xla Add-In does a Range.AddComment, the Range.Comment.Author of the comment appears to come from their workbook's (missing) Author property. When my replacement .xlam does a Range.AddComment, the Range.Comment.Author appears to come from the workbook or Application.Username where I called the code from/where the results, and comment, are going.

    I'm not sure I understand your statement "When a comment is added with code nothing is automatically entered (until code adds some text)." Range.AddComment (text) is the only way I see to add a comment. I don't see a way to add a comment, and then, separately, add text to it. It seems to work: call Range.AddComment, get a Range.Comment.Author along for the ride. What I'm trying to understand is how the .Author part is determined, and if this varies in the code's context of .xlsm, .xlam, .xla.

    Friday, November 9, 2018 5:34 PM
  • Sorry I missed your code snippet.

    "the Range.Comment.Author appears to come from the workbook or Application.Username"
    It comes from the latter, presumably the logic being the workbook might be have been created by someone else, but on a given system edited by the current user. I've no idea where "Author" is coming from unless that's the application.username...?

    "not sure I understand your statement "When a comment is added with code nothing is automatically entered (until code adds some text)."

    Even with Range.AddComment (text), first an empty comment is added, then the optional text if any.  Try simply Range.AddComment

    About your legacy addin - as far as running code is concerned it's the same as whatever version is hosting the xla.

    Does this work for you 

    Sub test()
        NewComment Range("c3"), "new comment", "", True
    End Sub
    
    Function NewComment(cel As Range, sText As String, sAuthor As String, bUser As Boolean)
    Dim cmt As Comment
    
        If bUser Then
            sAuthor = Application.UserName
        End If
        If Len(sAuthor) Then sAuthor = sAuthor & ":" & vbLf
    
        Set cmt = cel.Comment
        If Not cmt Is Nothing Then cmt.Delete
    
        Set cmt = cel.AddComment
        cmt.Text sAuthor & sText
        cmt.Shape.TextFrame.Characters(1, Len(sAuthor)).Font.Bold = True
    
    End Function

    The text could have been added with AddComment(text)


    Friday, November 9, 2018 9:25 PM
  • Your code copies the current workbook username into the comment text just like the Excel UI Add Comment does. As noted in my first post, I am not interested in doing that or concerned about how to do it. I want a comment that is just a comment from the AddIn, no username in comment text necessary. That was easy.

    The hard part is that I'm also trying to get the Range.Comment.Author *property*, as opposed to what the comment text reads, to be something besides the username of the person running the AddIn code. (Since Range.Comment.Author is where the text that my big blue arrow in the picture apparently comes from, regardless of the actual text in the comment.) The legacy AddIn works this way. I can't figure out how or why. The picture shown above shows "Author" after the legacy AddIn added the Range.Comment to my workbook when I ran it. My username is not Author. But my username doesn't wind up in Range.Comment.Author when I use the legacy add-in.

    Saturday, November 10, 2018 4:13 AM
  • "Your code copies the current workbook username into the comment text..."

    No, it copies the application.username into the comment, which may or may not be the same as the workbook's author property.

    Indeed the Range.Comment.Author property is the application.username at the time the comment was added. I thought we had established that.

    It's a read only property, so the only way to change it would be delete the comment and add it back under a different, perhaps temporary, xl.username. That would be easy to do but ensure you've got bullet proof error handing if for use in other user's systems. You could also change it temporarily while adding new comments with code -

    sOrig = Application.UserName
    Application.UserName = "user ?"
    'add comment
    Application.UserName = sOrig 

    What you describe as a bot addin displaying your name in the status bar is misplaced. No bot or addin, it's been standard behaviour in Excel to display the text in the status bar as shown in your screenshot in all versions since at least XL-2000.

    I still don't know where your "Author" comes from. But I'm not clear if you have access to the code in your xla. You quote a line from it starting 'QueryRange.Cells(1,1)', QueryRange is not an Excel method, where did that come from...


    • Marked as answer by Dick Watson Saturday, November 10, 2018 3:52 PM
    Saturday, November 10, 2018 12:33 PM
  • Agreed, current workbook username was an imprecision for Application.Username.

    We hadn't established that Range.Comment.Author property is always the application.username at the time the comment was added because I have one example that defies this every time it executes.

    I wasn't saying a bot was displaying the status bar message. I was saying a bot added the comment and has the right to be shown. somehow, as the Comment.Author. I'd be OK with Excel saying "Cell A1 commented by Author" in the status bar. *That* would be just what the legacy add-in achieves, somehow. If I can get Excel to say "Cell A1 commented by myAddInName", I'd be pleased as punch. But the workbook's Application.Username did not "commented" Cell A1. That's why I'm going down this rat hole in the first place.

    I will try your write to Application.Username approach. FWIW, I have searched the legacy VBA code for everything they do with Application. methods and find no reference to .Username. 

    I do have the VBA source for the legacy add-in. Just not the source for the DLL it, in turn, depends on. The 32-bit-ness of that DLL is the reason for its obsolescence and my replacement effort.

    Elsewhere in the legacy code I find

    Public QueryRange As Range

    Hopefully that clears up the mystery about what object's method this line, in the same Class Module, is really calling

    QueryRange.Cells(1, 1).AddComment transactionComment & AccountName & newLine & newLine & importDateComment & Date

     
    • Edited by Dick Watson Saturday, November 10, 2018 3:04 PM
    Saturday, November 10, 2018 3:04 PM
  • Temporarily changing Application.UserName achieved my desired result. Still unanswered, but overcome by events, is the question of how the legacy add-in got "Author" in there without referencing Application.UserName.

    Thanks!

    Saturday, November 10, 2018 3:52 PM
  • "what the legacy add-in achieves, somehow"

    Code in the legacy addin will perform identically if ported to a new xlam. Or you could saveAs your xla to an xlam (use fileFormat:=xlOpenXMLAddIn) and it will work the same. You should be able to step through it and see exactly what it does, maybe adapt slightly:
    Dim cmt As Comment
    Set cmt = QueryRange.Cells(1, 1).AddComment
    Debug.? cmt.Author ' in future that's what should appear as the author in the statusbar

    The xla uses the VBA available to the host Excel application, not some different/older version of VBA that might have existed when the xla was created.

    "If I can get Excel to say "Cell A1 commented by myAddInName", I'd be pleased as punch"

    So temporarily change the xl.Username to myAddInName while it adds the comment.

    QueryRange - OK understood.

    Saturday, November 10, 2018 4:01 PM
  • I didn't see this when I posted my last reply a few moments ago. Hopefully you've got enough now to figure where your "Author" came from.
    Saturday, November 10, 2018 4:05 PM
  • Well, no, I still have no clue where Comment.Author = "Author" came from the other day when I created my legacy add-in outputs reference workbook that I'm using to validate my replacement's output. Indeed, I go look at some old workbooks of mine with the added comments from that add-in generated long ago and I'm shown as the ...Comment.Author, not "Author". Indeed, at the moment, I can't replicate the "Author" Author. Go figure. It's this kind of stuff that's just crazy making.

    Anyway, I am now explicitly controlling what my add-in's comments will show as Author, so I guess I'll just never know where "Author" came from the other day.

    Saturday, November 10, 2018 4:39 PM
  • There's probably a simple explanation!

    FWIW if there's no app.username when the comment is created the author seems to default to environ("username")

    Saturday, November 10, 2018 8:09 PM