Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    May 2003
    Location
    Marshalltown, Iowa, USA
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Insert Comments (Excel 97 & Excel Mac X)

    Has anyone found a way to change the default "format" of cell comments?
    I use the comments quite often and I don't like the default font style and size, so I am changing it each time I use the "insert comment".

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Insert Comments (Excel 97 & Excel Mac X)

    Unfortunately, its format is linked to the tooltip format (if I remember correctly) of windows control panel.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    New Lounger
    Join Date
    May 2003
    Location
    Marshalltown, Iowa, USA
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Insert Comments (Excel 97 & Excel Mac X)

    Thanks for the note. You are correct, unfortunately it is tied to the Windows Control Panel. But, just the background color. Fonts ???

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Insert Comments (Excel 97 & Excel Mac X)

    This might get you started. This will go thru all the comments on a sheet and:
    remove the user name
    Change the font to BOLD, Arial size 12 and red
    Change the bkgrd of the box to white

    Change as appropriate

    Steve

    <pre>Sub ReformatComments()
    Dim cmt As Comment

    For Each cmt In ActiveSheet.Comments
    cmt.Text Text:=Application.WorksheetFunction.Substitute _
    (cmt.Text, Application.UserName & ":" & Chr(10), "")
    With cmt.Shape.TextFrame.Characters.Font
    .Name = "Arial"
    .Bold = True
    .Size = 12
    .Color = vbRed
    End With
    cmt.Shape.Fill.ForeColor.RGB = RGB(255, 255, 255)
    Next cmt
    End Sub</pre>


  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Insert Comments (Excel 97 & Excel Mac X)

    The font and color of the DEFAULT tooltip is also tied to the DISPLAY in control panel.

    You can change the comments manually or use a macro to change them all AFTER the fact. It is required that any NEW ones would need to have the macro run.

    Depending on your needs, this is fine, but if you have comments with various custom looks, you will have to check them and have the macro NOT change them.

    Steve

  6. #6
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Pa, Pennsylvania, USA
    Posts
    122
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Insert Comments (Excel 97 & Excel Mac X)

    Steve, Thanks for the VB code. This is good when I already have sheets with comments. What I really need to do is change the "default" somewhere. I have tried the Windows settings but to no avail.
    It never was this way but something happened some time ago, probably with a program that I installed.
    It is just frustrating at times.
    Thanks,
    Tim

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Insert Comments (Excel 97 & Excel Mac X)

    As far as I know there is no way to change this default. It is hard-coded into excel.

    You must either delete your name when entering the comment box, or delete them with a macro like I referenced after many are created.

    Steve

Posting Permissions

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