Formatting comments

Special-K

Board Regular
Joined
Apr 18, 2006
Messages
63
The comments boxes throughout my workbook are too small. I know how to edit each comment to adjust to "Automatic Size" ... but is there a way to change this to the default throughout? Or, must I go to each comment and adjust the comment formatting manually?

Thanks!
k. :wink:
 

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

sykes

Well-known Member
Joined
May 1, 2002
Messages
1,759
Office Version
  1. 365
Platform
  1. Windows
Hi K

I got fed up with this a long time ago, just like you have by the sounds of it!

I seem to recall that the only way to change all of the comments involves changing something like the computer's tool tip text (or something like that) which meant that the changes were manifested elsewhere as well, which was a pain.

What I did was to write a few lines into my personal workbook so that now if I want a comment I only ever have to right-click, and type into an inputbox - job's a goodun!

This is my code for the comment, when it's called for (it resides in a standard module in my personal workbook so it's always available to all workbooks):
Code:
Sub addcomm()
' this is the main macro for adding comments into a worksheet
' All workbooks use this macro - don't delete!!!
ActiveCell.AddComment
With ActiveCell.comment.Shape.TextFrame
.AutoSize = True
End With
With ActiveCell.comment.Shape.TextFrame.Characters.Font
.Name = "arial"
.Size = 12
.Bold = False
End With
t = InputBox("Do you want to put any text into your new comment now?", "Comment Text")
ActiveCell.NoteText " " & t
End Sub
....and this is the on-off code for adding the menu item to the right-click menu :
Code:
Sub bung()
'Very useful bit of code for adding menu items to the
'right -click menu. Don't delete!!!

Dim newcontrol As CommandBarControl
Set newcontrol = Application.CommandBars("cell").Controls.Add
With newcontrol
.Caption = "insert new comment"
.OnAction = "addcomm"
.BeginGroup = True
End With
End Sub
As you'll see I've specified my own font properties, and taken away the annoying habit of the name being added.
 

sykes

Well-known Member
Joined
May 1, 2002
Messages
1,759
Office Version
  1. 365
Platform
  1. Windows
I've just realised that I haven't actually answered your question have I?
This may help in the future, but you need to change existing comments by the looks of it!
 

wongm003

Board Regular
Joined
Aug 8, 2005
Messages
237
This code will go through all the comments on the activeworksheet and autosize....

Code:
Public Sub autosize_comments()
on error resume next

    'go through each comment and autosize.
    For Each cmnt In ActiveSheet.Comments
        cmnt.Shape.TextFrame.AutoSize = True
    Next cmnt

End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,114,406
Messages
5,547,759
Members
410,811
Latest member
adustin42
Top