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.

 

[kamranyd]

thnks yongle and others also. really you guys r so much help.

 

[kamranyd]

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

Can u add codes for resizing comment box according to text enter and fonts size using and colors

 

[My Aswer Is This]

Try this:
I added three lines of code to posting made by previous poster.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    'Modified 6/24/18 2:41 AM EDT
    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
    
    Target.Comment.Shape.TextFrame.Characters.Font.Size = 16  'New Line of code
    Target.Comment.Shape.TextFrame.AutoSize = True  ' New Line of code
    Target.Comment.Shape.Fill.ForeColor.SchemeColor = 3  'New line of code
End Sub

'You can modify to your exact needs.

 

[My Aswer Is This]

I missed one part.

Try this instead of my previous post:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    'Modified 6/24/18 7:11 AM EDT
    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
    
    Target.Comment.Shape.TextFrame.Characters.Font.Size = 16  'New Line of code
    Target.Comment.Shape.TextFrame.AutoSize = True  ' New Line of code
   Target.Comment.Shape.Fill.ForeColor.SchemeColor = 3  'New line of code
End If
End Sub

 

[kamranyd]

thnx, but new lines giving compile error "end if without block if"

 

[My Aswer Is This]

thnx, but new lines giving compile error "end if without block if"

Are you using the code I provided in post 14 ??

 

[kamranyd]

Yes post 14 codes

 

[My Aswer Is This]

Yes post 14 codes

Well it works for me.

Post back here the exact code your now using