copy cell into comment on another sheet

KeythStone

New Member
Joined
Mar 30, 2011
Messages
36
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

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,166
Code:
    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

New Member
Joined
Mar 30, 2011
Messages
36
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

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,166
Here's one way to do it...

Code:
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

New Member
Joined
Mar 30, 2011
Messages
36
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

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,166
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?

Code:
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

New Member
Joined
Mar 30, 2011
Messages
36
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
 

Forum statistics

Threads
1,081,575
Messages
5,359,725
Members
400,545
Latest member
Damntheman30

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top