insert comments

[kamranyd]

is there any vba code for inserting comments in a active cell and if a cell has already comments than show pop message that cell has already comments do you want to edit or no or delete comments.

is there any vba code who can do this.

 

[igold]

Editing post...

 

[My Aswer Is This]

Without any code if you right click on a cell with a comment and try to add a comment. You will see edit comment. Which tells you there is already a comment in that cell.

How are you entering comments?
Manually or part of a larger script?

If using a script show me your script.

 

[igold]

How about this:

Sub CkComments()
    
    Dim cmt As Comment
    Dim resp As String, newcmt As String


    For Each cmt In ActiveSheet.Comments
        If Not cmt.Shape.DrawingObject.Text = "" Then
            resp = MsgBox("Comment found in Cells(" & cmt.Shape.TopLeftCell.Cells.Row _
            & "," & cmt.Shape.TopLeftCell.Cells.Column & ")" & " it reads:" & vbNewLine & _
            vbNewLine & cmt.Shape.DrawingObject.Text & vbNewLine & vbNewLine & "Do you want to change or remove the comment", vbYesNo)
                If resp = vbYes Then
                    newcmt = InputBox("Enter New Comment or enter ""Delete"" to delete the comment")
                    cmt.Shape.DrawingObject.Text = newcmt
                        If newcmt = "Delete" Then
                            cmt.Delete
                        End If
                End If
        End If
    Next
    MsgBox "All Commnents Checked"
    
End Sub

 

[Yongle]

or try this..

[I][COLOR=#ff0000]Place this code in the SHEET module and then double click on any cell to test[/COLOR][/I]
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim OldComment As String, NewComment As String
    Cancel = True
    If Target.Count > 1 Then Exit Sub
    
    If Not Target.Comment Is Nothing Then
        OldComment = Target.Comment.Text
        Target.Comment.Delete
    End If
    NewComment = InputBox("Amend or delete comment", "", OldComment)
    If Not NewComment = vbNullString Then Target.AddComment Text:=NewComment
End Sub

 

[kamranyd]

yes that i know but my sheet is protected when it is protected i cant insert comments. i want to insert comment in protected sheet.

 

[Yongle]

And why did you not tell us that?

 

[Ingolf]

yes that i know but ...

Where in your request you said the sheet is protected ??? You said:

is there any vba code for inserting comments in a active cell and if a cell has already comments than show pop message that cell has already comments do you want to edit or no or delete comments.

is there any vba code who can do this.

 

[kamranyd]

yes forgot that to mention sorry.

Sub InsertComment()    
    Dim ans As String, oComment, Cmnt As String
    ActiveSheet.Unprotect
    Cmnt = InputBox("Select Cell for your comments", "Your Comments")
    With ActiveCell
        If .Comment Is Nothing Then
              .NoteText Cmnt
        Else
             ans = MsgBox("Yes = Add more comments with previous comments" & Chr(10) & "No = Replace your comments with previous comments" & Chr(10) & "Cancel = Cancel for no comments", vbYesNoCancel + vbInformation, "Options")
              If ans = vbYes Then
                  oComment = .Comment.Text
                 .NoteText oComment & Chr(10) & Cmnt
              ElseIf ans = vbNo Then
                  .NoteText Cmnt
           End If
       End If
       ActiveSheet.Protect
    End With
   End Sub

i m using these codes and it is fine, but i was wondering if there are any other ways.

 

[Yongle]

Sub InsertCommentInProtectedSheet()

    Dim OldComment As String, NewComment As String, Target As Range
    Set Target = ActiveCell
    ActiveSheet.Unprotect 

    If Not Target.Comment Is Nothing Then
        OldComment = Target.Comment.Text
        Target.Comment.Delete
    End If
    NewComment = InputBox("Amend or delete comment", "", OldComment)
    If Not NewComment = vbNullString Then Target.AddComment Text:=NewComment

    ActiveSheet.Protect 
End Sub