Formatting comments

[Special-K]

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:

 

[sykes]

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):

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 :

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]

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]

This code will go through all the comments on the activeworksheet and autosize....

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

 

[Special-K]

Cheers!

Thank you for all the great guidance!

Kevin.