XL2003: Move a Comment Into a Different Cell?

[Daddy]

Is it possible to move a comment into a different cell?

For example: I have a comment in cell B4; can I move it to cell A4?

I realize that I could always copy the comment to the new cell and then
delete the comment in the old cell, but surely there's an easier way?
Copy-and-delete becomes tedious when you have to do it for dozens of cells
individually.

Daddy

 

[Dave Peterson]

You could use a macro...

Option Explicit
Sub testme()

Dim FromCell As Range
Dim ToCell As Range

Set FromCell = Nothing
On Error Resume Next
Set FromCell = Application.InputBox _
(Prompt:="Select the From Cell", _
Default:=ActiveCell.Address, _
Type:=8).Cells(1)
On Error GoTo 0

If FromCell Is Nothing Then
Exit Sub 'user hit cancel
End If

If FromCell.Comment Is Nothing Then
MsgBox "No comment in that cell!"
Exit Sub
End If

Set ToCell = Nothing
On Error Resume Next
Set ToCell = Application.InputBox _
(Prompt:="Select the To Cell", _
Type:=8).Cells(1)
On Error GoTo 0

If ToCell Is Nothing Then
Exit Sub
End If

FromCell.Copy
ToCell.PasteSpecial Paste:=xlPasteComments

FromCell.Comment.Delete

Application.CutCopyMode = False

End Sub

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

 

[Daddy]

Thanks for your help. It's time I learned about macros, and this could be a
good way to start.

So, I get the impression from your answer that Excel can't do this on its
own - without writing a macro?

Daddy

 

[L. Howard Kittle]

Hey Daddy,

No need to copy to new cell and delete from old cell. Select the old cell
and move your cursor to the edge of the cell untill you get a four-sided
arrow and left click and hold and drag to new location and release.

As far as "... Excel can't do this on its own-" I would say the drag and
drop is about as 'on its own as you can get'. Maybe the down side of drag
and drop is that the cell contents goes with you on this method.

Dave P's code will likely be as top notch as it gets using the macro route.
The cell content does not follow to the new cell. If you want that feature
in Dave P's code add this just above <FromCell.Comment.Delete> line :

FromCell.Copy ToCell
FromCell.ClearContents

HTH
Regards,
Howard

 

[L. Howard Kittle]

One more note on the altering of Dave P's code. If you have a formula in
that cell the new cell will give you a REF# error.

Drag and drop takes the formula to the new cell.

You have choices.

HTH
Regards,
Howard

 

[Dave Peterson]

There's no way to move a comment via the user interface--except for the way you
described in the first post. (Well, I'm not aware of any way.)

 

[Dave Peterson]

This is doing much more than moving the comment, though.