VBA, Open a comment threaded in the edit box (Excel 365, version 2211, 32 bits)-CodePudding

I am creating a new comment threaded from my own popup menu and I want the user to be able to enter the text in the comment threaded box. Since there is no Visible property on the comments threaded, how can I show the comments threaded box?

The only way I can find to create a new threaded comment is with .AddCommentThreaded("")

The user then has to move the mouse over the cell to bring up the edit box and then press the pencil icon to edit the new note. This is unprofessional.

To open a comment (a note) in edit mode, I use Application.SendKeys " {F2}", True For example:

With MyCell
    .AddComment
    .Comment.Visible = True
    .Comment.Text Text:=Application.UserName & ":" & vbLf
    .Comment.Shape.TextFrame.Characters(1, Len(.Comment.Text) - 1).Font.Bold = True
    Application.SendKeys " {F2}", True
End With

Is there any way to use something similar?

CodePudding user response：

What you pass a string as an argument to the .AddCommentThreaded method to fill-in the comment from the user. You just need to collect the comment from the user beforehand.

For instance, you could use and InputBox like this:

    Dim UserComment As String
    UserComment = Application.InputBox(Prompt:="Please enter your comment:", Title:="Comment thread", Type:=2)
    ThisWorkbook.Worksheets(1).Range("A1").AddCommentThreaded UserComment

CodePudding user response：

Thanks DecimalTurn, it's a good idea although I intend to do something as close to the original Excel process (show comment box after clicking the popup menu).

If, after creating the comment, I place (with the SetCursorPos API) the mouse pointer over the cell, the edit comment box is displayed.

But now I need to find a method to get the coordinates of the cell. I'm working on it because this topic is also complicated. I hope to answer my question when I have it solved.