Text cells into comments (Excel 2000 macro)

[ChrisGreaves]

(Mon 3-May 2010: better version(s) below)
Select the cell holding text (large circle), select the cell to receive the comment (small circle), run the macro.
I wrote this to semi-automate a cleanup process I'm running on a set of workbooks, where the workbook designers didn't know about Comments, and used cells way-over to the far right to annotate their work.

Code: Select all

Sub LoadCellAsComment()
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ''' Function:   LoadCellAsComment
    '''
    ''' Comments:   Load the text of one cell as the comment of a second cell.
    '''
    ''' Arguments:  none
    '''
    ''' Returns:    none
    '''
    ''' Comments:
    '''
    ''' Date        Developer           Action
    ''' --------------------------------------------------------------------------
    ''' 2010/05/02  Chris Greaves   Created
    '''
    If Selection.Areas.Count = 2 Then
        Dim rngSource As Range
        Set rngSource = Selection.Areas(1).Cells(1) ' use the first cell of the selected area.
        Dim rngTarget As Range
        Set rngTarget = Selection.Areas(2).Cells(1) ' use the first cell of the selected area.
        '''
        Dim strComment As String
        strComment = rngSource.Value
        rngSource.Delete ' clear the text once we have it in hand.
        '''
        With rngTarget
            .ClearComments ' in case any present from a previous (failed) attempt
            .AddComment
            .Comment.Visible = False
            .Comment.Text Text:=Application.UserName & Chr(10) & strComment
        End With
    Else
        MsgBox "Select a cell with text then Ctrl-Select a cell to receive that text as comment"
    End If
End Sub

1.JPG

[HansV]

Just a very small comment

The lines

Code: Select all

            .AddComment
            .Comment.Text Text:=Application.UserName & Chr(10) & strComment

can be combined into

Code: Select all

            .AddComment Text:=Application.UserName & Chr(10) & strComment

[ChrisGreaves]

Just a very small comment

(No comment!)
Hans, when I disable the standalone .AddComment I got a run-time-error 91 "Object variable or With block variable not set".
(Later) I suspect I hadn't spotted your switch from .Comment to .AddComment.

Code: Select all

        Dim strComment As String
        strComment = Application.UserName & Chr(10) & rngSource.Value
        rngSource.Delete ' clear the text once we have it in hand.
        '''
        With rngTarget
            .ClearComments ' in case any present from a previous (failed) attempt
            .AddComment (strComment)
            .Comment.Visible = False
        End With

This seems to work.
Thanks for the heads up.

[HansV]

It would even be possible to shorten

Code: Select all

            .AddComment (strComment)
            .Comment.Visible = False

to

Code: Select all

            .AddComment(strComment).Visible = False

since AddComment returns a Comment object, but one might argue that this makes the code less readable and harder to debug.

[ChrisGreaves]

It would even be possible to shorten...

True.

...makes the code less readable and harder to debug...

Can we move this thread to the APL forum? (grin!)
I'm all for succinct, or perhaps concise (where is the pedant when you need him/her/it?) code when it is deep-level, perhaps within a library; but I'll go for readable when it needs to be inspected and/or maintained on a regular basis.
Hard to believe but it's 40 years since I was told to stop writing in assembly language - and that was at a compiler-developer lab!

[HansV]

APL - now that was fun but really hard to read...

(~R∊R∘.×R)/R←1↓⍳R

(Generates a list of all prime numbers from 2 to R)

[ChrisGreaves]

I realize now that I should have used .Clear rather than .Delete:
"rngSource.Clear ' clear the text once we have it in hand."

Code: Select all

Sub LoadCellAsComment()
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ''' Function:   LoadCellAsComment
    '''
    ''' Comments:   Load the text of one cell as the comment of a second cell.
    '''
    ''' Arguments:  none
    '''
    ''' Returns:    none
    '''
    ''' Comments:
    '''
    ''' Date        Developer           Action
    ''' --------------------------------------------------------------------------
    ''' 2010/05/02  Chris Greaves   Created
    '''
    If Selection.Areas.Count = 2 Then
        Dim rngSource As Range
        Set rngSource = Selection.Areas(1).Cells(1) ' use the first cell of the selected area.
        Dim rngTarget As Range
        Set rngTarget = Selection.Areas(2).Cells(1) ' use the first cell of the selected area.
        '''
        Dim strComment As String
        strComment = Application.UserName & Chr(10) & rngSource.Value
        rngSource.Clear ' clear the text once we have it in hand.
        '''
        With rngTarget
            .ClearComments ' in case any present from a previous (failed) attempt
            .AddComment (strComment)
            .Comment.Visible = False
        End With
    Else
        MsgBox "Select a cell with text then Ctrl-Select a cell to receive that text as comment"
    End If
End Sub

[HansV]

You no doubt know this, but for others reading this thread:

rngSource.ClearContents deletes the contents of the range but leaves the formatting alone.
rngSource.ClearFormatting removes the formatting of the range but leaves the contents alone.
rngSource.Clear deletes both the contents and the formatting of the range (it is .ClearContents and .ClearFormatting combined into one command).
rngSource.Delete deletes the range and moves adjacent cells up or to the left to replace the deleted range.

[ChrisGreaves]

You no doubt know this, but for others reading this thread:

Thanks Hans, yes I did know it, but neglected to apply my knowledge.
Only when my cell-references went awry did I stumble on the fact that I was doing something wrong :slaponwrist:.