CODEWITHSUNDEEP
excelvbaruntime-error
Nathalii.
Nathalii.

Reputation: 126

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

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?

Upvotes: 0

Views: 2377

Answers (1)

DisplayName
DisplayName

Reputation: 13386

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

Upvotes: 5

Related Questions