[Solved] VBA: Comment

  • Hi to everybody, I have just entered the forum. I am an Italian boy.
    I have the necessity to save in a variable the contained comment in a cell, it is possible.

  • Here's a quick example. I created a comment in Cell A1 on Sheet1. I have created the object variable "objComment" to hole the comment.

    Public Sub main()
      Dim objComment As Comment
      Set objComment = Sheets("Sheet1").Range("A1").Comment
      MsgBox objComment.Text
    End Sub
  • Denis, thanks for your rapids answer, but it is not what serves me.
    I would like power click with the right key of the mouse on a containing cell a comment and to save the content of the comment in a variable to use elsewhere.

  • No, I would like to copy the content of the comment of a cell to assign it to a variable to use apart.
    Have I resolved however this way?

    Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
    'For the insertion of the comments
    Cancel = True
    nr = ActiveCell.Row
    nc = ActiveCell.Column
    Dim msg As String
    On Error GoTo MyErr
    msg = Cells(nr, nc).Comment.Text
    MsgBox (msg)
    Exit Sub
    MyErr: MsgBox "No there are comments"
    End Sub

    Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
    'To cancel the comment
    MyAnswer = MsgBox("Are you sure to want to cancel the comment??", vbOKCancel)
    If MyAnswer = vbCancel Then
    Exit Sub
    End If
    End Sub

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!