Sizing all comments at once

[Guest]

Is it possible to resize all the comments on one spreadsheet at one time? This sheet is used monthly and for some reason every month the sizes shrink--too small to see the whole comment, there are many comments so it would be convenient to resize all at once instead of one at a time taking a couple of hours..

 

[Tom Ogilvy]

You can use a macro. Is the size the same for each. If not, does autosize
work manually?

--
Regards,
Tom Ogilvy

Is it possible to resize all the comments on one spreadsheet at one time?

This sheet is used monthly and for some reason every month the sizes
shrink--too small to see the whole comment, there are many comments so it
would be convenient to resize all at once instead of one at a time taking a
couple of hours...

 

[Guest]

I don't know anything at all about programming, as I only work in accounting. They are not all the same size, some of them are long from side to side but very thin from top to bottom. I don't know how to go about autosizing...sure wish I did.

 

[Dave Peterson]

Try this manually:

Find a comment that looks bad.
Rightclick on that cell and select Edit|comment
Now click on the comment's border
Format|Comment

If you only see one tab (Font), you missed the border.

If you see a tab named Alignment, select it.

Now check the "Automatic Size" box.

(Ok your way out.)

Did it look ok?

If yes, then you could have a macro that fit them all.

Open a new workbook (where we'll store this macro)
Save it as: FixComments.xls
hit alt-F11 to see the VBE where macros live
hit ctrl-R to see the project explorer (like windows explorer)

Look for VBAProject (fixcomments.xls)

Right click and select Insert|Module.
Paste this into that new window that just opened.

Option Explicit
Sub AutoSizeComments()
Dim cmt As Comment
For Each cmt In ActiveSheet.Comments
cmt.Shape.TextFrame.AutoSize = True
Next cmt
End Sub

Hit alt-Q to close this and go back to excel.

Save this workbook with your new macro.

Open your workbook that has the worksheet with all the comments.

Go to that worksheet.

Tools|Macro|macros...
look for fixcomments.xls!AutoSizeComments
select it and click Run.

Whenever you need to do it again, just open that workbook and
tools|macro|macros...

 

[Dave Peterson]

I don't recognize that error (and I just tried it once more and it worked ok for
me).

What line pops up with the error?

(and if you didn't copy|paste from the newsgroup posting, post your subroutine.)

 

[Guest]

Thank you very much for all your help Dave...when I was going into the macros dialog box--more than one sheet name was showing up, so I clicked the "step into" button to see if I can figure out why it's not working. I made a copy before doing this (in case I deleted something important), then deleted whatever was there before. It then only showed the macro you had me create, I ran it and it worked!

 

[Dave Peterson]

Glad you got it working.

Thank you very much for all your help Dave...when I was going into the macros dialog box--more than one sheet name was showing up, so I clicked the "step into" button to see if I can figure out why it's not working. I made a copy before doing this (in case I deleted something important), then deleted whatever was there before. It then only showed the macro you had me create, I ran it and it worked!