How do I copy cell text to comments and vise versa

[senthile]

How do I copy cell text to comments in excel 2010 and vise versa (i.e. from comments to cell)

 

[bruderbell]

You can do this with a quick macro:
<code>Sub swap()
If ActiveCell.Comment Is Nothing Then
x = ""
ActiveCell.AddComment
Else
x = ActiveCell.Comment.Text
End If
y = ActiveCell.Value
If y = Empty Then
ActiveCell.Comment.Delete
Else
ActiveCell.Comment.Text y
End If
ActiveCell.Value = x
End Sub
</code>

If you assign the macro a hotkey, you can then quickly swap comments to cell value and cell value to comments.

 

[senthile]

Thanks for details, i am not good at Excel, can you please help me to do this with step by step process...? Also if possible can you please attach the sample sheet?

Thanks in Advance for your help.

 

[senthile]

Hi bruderbell,
I have learned how to use the code given by you, Thanks it is working.

While running the macro, i am facing following two issue.
- But if it copy from comment then it delete the comments
- if it is copy from cell to comment then it is deleting from the cell.

Can you please help me with the code, while running this process it should not delete from the cell as well as from comments. i need copy of the same vice versa.

Thanks a lot for your help.

 

[bruderbell]

I guess I dont' know what your goal is. You can modify the code to just copy the comment to the cell value, or the cell value to the comment.

 

[senthile]

I guess I dont' know what your goal is. You can modify the code to just copy the comment to the cell value, or the cell value to the comment.

When i copy the text from cell to comment, copy of the same should be in comment
similarly when i copy text from comment to cell, comp of text should be in cell. Hope you under stand my requirement.

 

[bruderbell]

When i copy the text from cell to comment, copy of the same should be in comment
similarly when i copy text from comment to cell, comp of text should be in cell. Hope you under stand my requirement.

Try this:
<code>
Sub swap()
If ActiveCell.Comment Is Nothing Then
x = ""
ActiveCell.AddComment
Else
x = ActiveCell.Comment.Text
End If
y = ActiveCell.Value
If y = Empty Then
ActiveCell.Comment.Delete
Else
ActiveCell.Comment.Text y
End If
'ActiveCell.Value = x
End Sub</code>

You can mute any parts of the code with '
If you want it to just copy the cell text into a comment but leave it in the text, use the above. I'll let you play with it to figure out how to leave a comment but copy it to the cell text.

best of luck

 

[senthile]

Try this:
<code>
Sub swap()
If ActiveCell.Comment Is Nothing Then
x = ""
ActiveCell.AddComment
Else
x = ActiveCell.Comment.Text
End If
y = ActiveCell.Value
If y = Empty Then
ActiveCell.Comment.Delete
Else
ActiveCell.Comment.Text y
End If
'ActiveCell.Value = x
End Sub</code>

You can mute any parts of the code with '
If you want it to just copy the cell text into a comment but leave it in the text, use the above. I'll let you play with it to figure out how to leave a comment but copy it to the cell text.

best of luck

HI Burderbell,

This works fine and Great.... for copy cell to comments.

Also can you please share the code for "copy" text from comments to cell (Should not delete from comment)?

 

[bruderbell]

Give this a shot:
<code>Sub swap()
If ActiveCell.Comment Is Nothing Then
x = ""
ActiveCell.AddComment
Else
x = ActiveCell.Comment.Text
End If
y = ActiveCell.Value
If y = Empty Then
'ActiveCell.Comment.Delete
Else
'ActiveCell.Comment.Text y
End If
ActiveCell.Value = x
End Sub</code>

 

[senthile]

Hi Burderbell,

Thanks once again for your support. it is working fine...
if possbile share the link of VBA code learning for new learner with step by step process.

Thanks to Forum....




​