[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.
    Thanks

  • 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.


    Code
    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.
    thanks

  • 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
    Selection.ClearComments
    End Sub

Participate now!

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