Add a comment with VBA

[S1lverface]

Hello again. Can someome help please.
I understand the below code can insert a comment to my Excel spreadsheet
cell A1. I would like to insert the text which is contained in cell H1 as my
comment. Cell H1 text will be changed daily. I will run the macro each day.

Any help would bemuch appreciated.

Sub AddCommentTest()
Range("A1").AddComment "Test"
End Sub

 

[Dave Peterson]

Option Explicit
Sub AddCommentTest()
With ActiveSheet
If .Range("A1").Comment Is Nothing Then
'do nothing
Else
'delete existing comment
.Range("A1").Comment.Delete
End If
.Range("a1").AddComment Text:=.Range("H1").Text
End With
End Sub

 

[Bernard Liengme]

Sub AddCommentText()
mycomment = Worksheets("Sheet1").Comments(1).Text
Range("A1") = mycomment
End Sub

But this copy the text and the author

 

[Shane Devenshire]

Hi,

If you already have a comment is the cell then you can use

Sub ReviseComment()
[A1].Comment.Text [H1].Text
End Sub

 

[Dave Mills]

Hello again. Can someome help please.
I understand the below code can insert a comment to my Excel spreadsheet
cell A1. I would like to insert the text which is contained in cell H1 as my
comment. Cell H1 text will be changed daily. I will run the macro each day.

You may want to consider using the change event on cell H1 such that the comment
is updated as soon as the value in H1 changes. Thus you would not need to run
the macro daily.