Run-time Error 1004 while Adding Comments to Cells with VBA in Excel

Question

I am getting a Excel VBA run-time error 1004 : Application-defined or object-defined error while adding a comment to range of Excel cells with VBA.

The text for the comment comes out of a userform:

    Description = TextBox1.Value
    StartTime = TextBox2.Value
    EndTime = TextBox3.Value


InputText = StartTime & " - " & EndTime & "  " & Description
MsgBox (InputText)

This part works great. Afterwards there is some code to format the cells. In the end VBA should add a comment to each cell.

Dim Cell As Range
For Each Cell In Selection
    Cell.AddComment
        Cell.Comment.Visible = False
        Cell.Comment.Text Text:=InputText  **'// ERRORLINE//**
    Next Cell

I already tried changing some code, without any luck:

Dim Cell As Range
For Each Cell In Selection
    'Cell.Comment.Delete
    Set Comment = Cell.Comment
        Cell.Comment.Visible = False
        Cell.Comment.Text Text:=InputText

        Next Cell

What works without any problems is:

Dim Cell As Range
For Each Cell In Selection
    'Cell.Comment.Delete
    Set Comment = Cell.Comment
        Cell.Comment.Visible = False
        Cell.Comment.Text Text:="InputText"

        Next Cell

What is causing this error?

DisplayName DisplayName · Accepted Answer · 2018-12-27T11:01:24

clear the comment before adding a new one:

For Each cell In Selection
    With cell
        .ClearComments
        .AddComment
        .Comment.Visible = False
        .Comment.Text Text:=InputText
    End With
Next cell

Run-time Error 1004 while Adding Comments to Cells with VBA in Excel

1 Answers