Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Dec 2001
    Location
    Houma, Louisiana, USA
    Posts
    100
    Thanks
    1
    Thanked 0 Times in 0 Posts

    comment for multiple cells (Office 2003)

    Is there a way to make a comment in excel point to multiple cells
    OR
    a way to select several cells and add the same comment to each at the same time?

  2. #2
    2 Star Lounger
    Join Date
    Dec 2001
    Location
    Houma, Louisiana, USA
    Posts
    100
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: comment for multiple cells (Office 2003)

    Wow, thanks Hans. That worked perfectly and your directions were a breeze to follow. Thanks for pointing me to Legare's personal.xls tutorial. It took me all of 5 minutes to get the macro done.

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: comment for multiple cells (Office 2003)

    A comment just refers to a single cell. You could add the following macro to your Personal.xls (and assign it to a toolbar button if you like):

    Sub InsertComments()
    Dim strComment As String
    Dim oCell As Range

    On Error GoTo ErrHandler

    strComment = InputBox("Enter comment text")
    If Not strComment = "" Then
    For Each oCell In Selection.Cells
    oCell.AddComment strComment
    Next oCell
    End If

    ExitHandler:
    Set oCell = Nothing
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub

    The macro will ask for a comment text and add it to all cells in the selected range. See Legare Coleman's <!post=Personal.xls Tutorial (All),118382>Personal.xls Tutorial (All)<!/post> if you want to know more about Personal.xls.

  4. #4
    2 Star Lounger
    Join Date
    Dec 2001
    Location
    Houma, Louisiana, USA
    Posts
    100
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: comment for multiple cells (Office 2003)

    I have one little problem. I noticed that the comment boxes are rather large. Much larger than I need them to be. Is there a way to set some sort of default for the size of the boxes?

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: comment for multiple cells (Office 2003)

    You can change

    oCell.AddComment strComment

    to

    oCell.AddComment(strComment).Shape.TextFrame.AutoS ize = True

    This will automatically make the comment box shrink or grow to fit the text.

  6. #6
    2 Star Lounger
    Join Date
    Dec 2001
    Location
    Houma, Louisiana, USA
    Posts
    100
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: comment for multiple cells (Office 2003)

    Works like a charm! Thanks for you help, Hans.

    Can you auto size regular coments as well?

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: comment for multiple cells (Office 2003)

    I suspect this can only be done in VBA, using code similar to that in my previous reply:

    ActiveCell.Comment.Shape.TextFrame.AutoSize = True

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •