Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Default Formatting for Cell Comments (v97 SR2 and Above)

    In what ways can default format settings for Cell Comments be created and saved by a user. For example; I'd like to stop "Your Username" from appearing each time I create a new Cell Comment. Also, is there a way to standardize the size, shape and font of a new textbox.

  2. #2
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Default Formatting for Cell Comments (v97 SR2 and Above)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> arctutus16a

    Check John Walk's web site or This page also on John's site for issues like this one.

    Hope this helps.

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Default Formatting for Cell Comments (v97 SR2 and Above)

    As Wassim pointed out, the only way to change the default Comment attributes is to change the system Tooltip settings. However a possible solution might be to use a macro to insert the comment :

    The following code will inert a blank comment with the font settings user chosen :

    Sub InsertComment()
    ActiveCell.AddComment
    With ActiveCell.Comment.Shape.TextFrame.Characters.Font
    .Name = Arial ' Name of Font
    .Size = 10 ' Font Size
    .Bold = False ' Bold = True or false
    End With
    End Sub

    To set the default settings for Textboxes and other Authoshapes, is a little easier. If a file called Book.xlt exists in your XLStart folder, Excel will use that workbook as the default for all new workbooks.

    In Book.xlt, create a Textbox or other Autoshape and set all the attributes up as you want, color, font border, shadow etc. Then select the shape and from the Drawing menu select Set AutoShape defaults and save the workbook.

    If the workbook is saved as Book.xlt in XLStart then all new workbooks will be based on it. You can also set any other default characteristics you might want for basic workbooks.

    Pity that Comment settings are not associated with the default Autoshape rather than the system tooltips !

    Andrew C

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Default Formatting for Cell Comments (v97 SR2 and Above)

    Thanks Andrew. I think this is what I'm looking for. When I run the code you posted the cell comment closes. If I try to record a macro to edit the comment I get the code:

    Sub EditCellNote()
    'EditCellNote Macro
    Range("J8").Comment.Text Text:="Yada yada yada"
    Range("J8").Select
    End Sub

    What code can I substitute for Range("J8") so that I am only editing the CellComment for the ActiveCell? Also, if I would like to change the Fill, Line, Frame or other attributes, what code enables me to select Selection.ShapeRange. (ActiveCell doesn't work for me there either).

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Default Formatting for Cell Comments (v97 SR2 and Above)

    The following should work PROVIDED the active cell has a comment attached.

    Sub EditCellNote()
    ActiveCell.Comment.Text Text:="Yada yada yada"
    End Sub

    Another posibility is something like :

    Sub InsertComment()
    Dim strComment As String
    strComment = InputBox("Please Enter comment text : ")
    If Len(strComment) > 0 Then
    ActiveCell.AddComment.Text Text:=strComment
    With ActiveCell.Comment.Shape.TextFrame.Characters.Font
    .Name = Arial ' Name of Font
    .Size = 10 ' Font Size
    .Bold = False ' Bold = True or false
    End With
    End If
    End Sub

    The following example formats the comment, adding a shadow, changing the color and line style. This example also requires that the active cell has a comment attached.

    Sub FormatComment()
    With ActiveCell.Comment.Shape
    .Fill.ForeColor.SchemeColor = 41
    With .Line
    .Style = msoLineThinThick
    .Weight = 6
    .ForeColor.SchemeColor = 62
    End With
    .Shadow.Type = msoShadow6
    End With
    End Sub

    Hope the above is of assistance,

    Andrew C

  6. #6
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    199
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Default Formatting for Cell Comments (v97 SR2 and Above)

    Hi Arcturus16a,

    Along with all the other good advice you already have here is my tuppence worth.

    I wanted to be able to add comments for users without my name appearing on the top of every one - this macro does exactly that:

    <pre>Sub NewComment()
    ' Insert Comments without the User Name
    '
    On Error Resume Next
    strComment = InputBox("Enter your comment: ", "Add Comment")
    strCommentHdr = "Your Std Comment Header"
    strCommentHdr.Bold = True
    ActiveCell.AddComment
    ActiveCell.Comment.Visible = False
    ActiveCell.Comment.Text Text:=strCommentHdr & vbCrLf & strComment
    End Sub
    </pre>



    Good Luck!

    Peter Moran
    Two heads are always better than one!!

Posting Permissions

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