Changing Excel defaults (XP – 2002)

Topic

New Reply

I would like to change the way comments are done. Currently, when I enter a comment, it begins with my name and I would like to stop this. Also, the comment automatically uses an 8 pt font and I would like to change this to automatically use a 12 pt font. Is this possible? if so, how?

Reply | Quote

Replies

I don’t think you can leave your name out of comments automatically. You could use Data | Validation instead, and set an input message without title.

Furthermore, I think (this has come up before in this forum) that the font used by both comments and validation is that for tool tips; it is set in Display Settings, Appearance tab (click Advanced button in Windows XP.)

Reply | Quote

You can change them AFTER the fact with VB.
This code (for example) will eliminate the Username, and change the font to NON-bold, Arial, size 12, and red.

NOTE: If the Username was edited it will not remove the edited version. Also if you customize the format of a comment this will overwrite it.

Steve

Option Explicit
Sub ReformatComments()
    Dim cmt As Comment
    For Each cmt In ActiveSheet.Comments
        cmt.Text Text:=Application.WorksheetFunction.Substitute _
            (cmt.Text, Application.UserName & ":" & Chr(10), "")
        cmt.Shape.Visible = True
        cmt.Shape.Select
        With Selection.Font
            .Name = "Arial"
            .Bold = False
            .Size = 12
            .Color = vbRed
        End With
    Next cmt
End Sub

Reply | Quote

I believe the source for your name is the Tools/Options/General…at the bottom is the User name box which holds the name inserted in the comment. I often just highlight and delete it in the comment…sometimes I change the name of the user to Napoleon Bonaparte…or leave it blank. Try it and see.

Reply | Quote

That will also effect the Workbook Properties.

Reply | Quote