Inserting Comments in Multiple Cells

[MalcolmWalker]

In an Excel 2007 worksheet I am inserting the same comment in consecutive cells, twenty or more, in a column. Then I could have a different comment to repeat in further multiple cells. I have used Copy and Paste and in doing so, due to the repetition involved, thought of recording a macro. But I would end up with a significant number of macros, many of the comments being different.

As with cell Fill Down is there an elegant way of repeating a comment in multiple cells? My Google search was not inspiring!

Malcolm

[Rudi]

Just a wild guess since i'm not in front of my pc...
Could you not use autofill, since you say the cells are consecutive. Autofill the cell with the comment, then create the new comments and autofill again, etc...

[StuartR]

You could use a couple of simple Macros like

Code: Select all

Dim strCommentText As String

Sub CopyComment()
    strCommentText = ActiveCell.Comment.Text
End Sub

Sub PasteComment()
    Dim cl As Range
    For Each cl In Selection
        If Not cl.Comment Is Nothing Then cl.Comment.Delete
        cl.AddComment strCommentText
    Next cl
End Sub

[HansV]

Alternatively, here is a macro that will prompt you for a text string, then add it to every cell in the current selection:

Code: Select all

Sub AddComments()
    Dim rngCell As Range
    Dim strText As String
    strText = InputBox("Enter comment")
    If strText <> "" Then
        For Each rngCell In Selection
           If Not rngCell.Comment Is Nothing Then
              rngCell.Comment.Delete
            End If
            rngCell.AddComment Text:=strText
        Next rngCell
    End If
End Sub

[MalcolmWalker]

Gentlemen, Thank you each for your response. It's very pleasing to have your solution so promptly in answer to my enquiry.

I have tried Hans solution and it comprehensively fulfils my requirements to the letter.

Thank you Rudi, Stuart and Hans,

Malcolm