Oh, Loathe is too nice a word...

August 6, 2011 3:44 PM

How can I use a column in an excel sheet to automatically become the comments for a corresponding column?

Imagine two columns.. one is a word, the second is a definition. I'd like to mouse-over the word in the Excel sheet and the description pop up a la the little red indicator that you have a comment. I don't want to have to go into each individual cell, open up the comment window, delete the auto-text filled in, and then type in my definition. Especially since that definition is in the column next to the word already.

posted by rich to Technology (3 answers total)

If you are on a PC, this seems like a good starting point for writing a script to do the work.
posted by ifandonlyif at 4:53 PM on August 6, 2011

I don't have excel handy, but it'll be something like:

for each c in range("A1:A10")
c.comment.text = c.offset(0,1).value
next c

for whatever range.
posted by pompomtom at 7:08 PM on August 6, 2011

Best answer: I wrote this pretty quickly, and there are undoubtedly easier ways to do it, but this gets the job done. I did this with Excel 2007, it should work with any recent version, but I can't guarantee it:

Press Alt-F11 for the VBA editor, double click on the appropriate workbook on the top left pane. In the pane that opens on the right past this in:

Sub CommentIt()
    
    Dim myRange As String
    Dim mySheetName As String
    Dim myWord As Range
    Dim myDef As String
    Dim noDefMsg As String
    
    'UPDATE THE CELL RANGE IN THE NEXT LINE
    myRange = "A1:B6"
        
    'UPDATE THE SHEET NAME IN THE NEXT LINE
    mySheetName = "Sheet1"
    
    'IF THERE ARE CASES WITHOUT DEFINITIONS, THE FOLLOWING MESSAGE IS USED
    noDefMsg = "(No Definition)"
    
    
    With Worksheets(mySheetName).Range(myRange)
        .ClearComments
        For r = 1 To .Rows.Count
            Set myWord = .Cells(r, 1)
            myDef = .Cells(r, 2).Value
            If myDef <> "" Then
                myWord.AddComment myDef
            Else
                myWord.AddComment noDefMsg
            End If
         Next r
    End With
        
End Sub

Change the MyRange, MySheet, and noDefMsg values toward the top to reflect the right worksheet name, the right cell range, and if you want a comment to appear when there's no definition. With your mouse cursor somewhere inside the pasted text, you can press F5 and it will create comments in the first column that contain the text in the second (in my example, A1 gets a comment based on B1 and so on through row 6).
posted by i love cheese at 7:14 PM on August 6, 2011 [2 favorites]