copy cell into comment on another sheet

[KeythStone]

Hello to all,

I want to know if I can copy text in one cell on Sheet "test" and paste into a new comment on Sheet "report". I've seen some codes that go the other way around, extract the comment and move to another sheet. Kind of want to go the other way. Any ideas?

Stay smooth,

KeythStone

 

[AlphaFrog]

    Dim cmt As Comment
    
    With Sheets("report").Range("C1")
        Set cmt = .Comment
        If cmt Is Nothing Then Set cmt = .AddComment
    End With
    cmt.Visible = False
    cmt.Text Text:=Sheets("test").Range("A1").Text

Excel Comments VBA

 

[KeythStone]

Thanks AlphaFrog. This helps.

I need to use the above code as i cycle through two ranges. one range identifies where the comment boxes go, the other is from where the data text is stored to be put into the comment box. I have this so far:

Sub AddComments()
Dim commrange As String
Dim cmt As Comment

For Each c In Range("commrange")
If cmt Is Nothing Then Set cmt = c.AddComment
cmt.Visible = True
cmt.Text Text:=Sheets("test").Range("notes").Text
Next c
End Sub

I can see the inherent gap in logic as in the code, it doesn't know how to move through the range. And I do not know how to do so either. Thanks everyone.

Stay Smooth,
KeythStone

 

[AlphaFrog]

Here's one way to do it...

Sub AddComments()
    
    Dim i      As Long
    Dim cmt    As Comment
    
    For i = 1 To Range("commrange").Count
        Set cmt = Range("commrange")(i).Comment
        If cmt Is Nothing Then Set cmt = Range("commrange")(i).AddComment
        cmt.Visible = True
        cmt.Text Text:=Sheets("test").Range("notes")(i).Text
    Next i
    
End Sub

 

[KeythStone]

Thanks AlphaFrog for all your help. It works now. I hope to take this understanding and run with it. Here is the code I used:

Sub AddComments()

Dim i As Long
Dim cmt As Comment

For i = 1 To Range("commrange").Count
Set cmt = Range("commrange")(i).Comment
If cmt Is Nothing Then Set cmt = Range("commrange")(i).AddComment
cmt.Visible = False
cmt.Text Text:=Sheets("test").Range("notes")(i).Text
If Sheets("test").Range("notes")(i).Text = "" Then Exit For
Next i

End Sub

 

[AlphaFrog]

You're welcome. I'm glad you got it to work.

Two minor comments.

1.) shouldn't you test if then Notes text is equal to "" at the top of the loop?

Sub AddComments()

    Dim i      As Long
    Dim cmt    As Comment

    For i = 1 To Range("commrange").Count
        [COLOR="Red"]If Sheets("test").Range("notes")(i).Text = "" Then Exit For[/COLOR]
        Set cmt = Range("commrange")(i).Comment
        If cmt Is Nothing Then Set cmt = Range("commrange")(i).AddComment
        cmt.Visible = False
        cmt.Text Text:=Sheets("test").Range("notes")(i).Text
    Next i

End Sub

2.) Forum Tip: Pasting VBA code in the forum editor
For future reference, it would be best if you surround your VBA code with code tags e.g [CODE]your VBA code here[/CODE]
It makes reading your VBA code much easier.
When you're in the forum editor, highlight your pasted VBA code and then click on the icon with the pound or number sign #.

 

[KeythStone]

This weekend i will sit down and read the rules to post on this site. I'd like to follow them, everyone here is so very helpful. Thanks,

Stay Smooth
KeythStone