Is there any way to set the default size for a comment in Excel 2007?
Every time I insert a comment it opens at the same size then I have to drag it to the size I want or format the comment size manualy by entering numbers, this is time consuming to do. I would like to set the size of the comment and have all additional comments created at that size.
First make sure that the Developer tab is visible. Click the Office button > Excel Options, tick 'Show Developer tab in the Ribbon'.
Press ALT + F11 to open the Visual Basic Editor. Insert > Module. Copy and paste in
Sub AutosizeComments() Dim cmt As Comment, cell As Range On Error Resume Next For Each cell In ActiveSheet.UsedRange Set cmt = cell.Comment If Not cmt Is Nothing Then cmt.Shape.TextFrame.AutoSize = True Next cell End Sub
Close the VBE using the X. Select the Developer tab > Macros > highlight AutosizeComments and click the Run button.
Thanks for answering VoG, I tried what you said and when I ran it all the comments where reduced to pin pricks! There was no chance to enter any size details it just made all the comments very small. Any ideas?
Why does it have to be so difficult? How hard would it be to add an option like "remember last setting" in the format options? I can't believe I'm the only one that wants to do this, and it's not like the program is new, this must be the sixth or later version of it!
This thread is now locked and can not be replied to.