Help with Macro

  • Thread starter Thread starter shell
  • Start date Start date
S

shell

Is it correct that cells only show 250 characters? I need a macro that will
copy all the contents of a cell and paste into a comment so the users can
read the entire block of information by hovering over the cell. I then need
this to be done automatically to each and every cell with text. I then need
the macro to update all cells and comments automatically once the cell has
been edited.

Does this make sense? Please help.
 
You must format your cells as text and also use the wrap functionality
available in the Aligment Tab under the format cells menu.
 
Put this event macro in the worksheet code area:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim c As Comment, r As Range
Application.EnableEvents = False
If ActiveSheet.Comments.Count > 0 Then
For Each c In ActiveSheet.Comments
c.Delete
Next
End If
For Each r In ActiveSheet.Cells.SpecialCells(xlCellTypeConstants)
r.AddComment
r.Comment.Visible = False
r.Comment.Text Text:=r.Text
Next
Application.EnableEvents = True
End Sub


Because it is worksheet code, it is very easy to install and automatic to use:

1. right-click the tab name near the bottom of the Excel window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you have any concerns, first try it on a trial worksheet.

If you save the workbook, the macro will be saved with it.


To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm
 
Back
Top