How do I copy cell text to comments and vise versa

senthile

New Member
Joined
Jan 24, 2014
Messages
10
How do I copy cell text to comments in excel 2010 and vise versa (i.e. from comments to cell)
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

bruderbell

Active Member
Joined
Aug 29, 2006
Messages
374
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

New Member
Joined
Jan 24, 2014
Messages
10
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

New Member
Joined
Jan 24, 2014
Messages
10
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

Active Member
Joined
Aug 29, 2006
Messages
374

ADVERTISEMENT

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

New Member
Joined
Jan 24, 2014
Messages
10
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

Active Member
Joined
Aug 29, 2006
Messages
374

ADVERTISEMENT

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

New Member
Joined
Jan 24, 2014
Messages
10
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. :cool:

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

bruderbell

Active Member
Joined
Aug 29, 2006
Messages
374
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

New Member
Joined
Jan 24, 2014
Messages
10
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....




 

Watch MrExcel Video

Forum statistics

Threads
1,122,469
Messages
5,596,316
Members
414,053
Latest member
Dual Showman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top