Excel 2010: change default comment text alignment

Question

I'm trying to create a default comment format.  I've removed my name, and now want to set the vertical AND horizontal text alignment to Centered, and to keep the "Automatic size" box checked.  Can / how do I accomplish this?  Do I need a macro?  If so, who do I ask to help me with that, as I have no idea how to create macros in Excel.

Thanks.

Answer 1

Hi,

I know it's been long time since I asked my original question - I only use Excel for personal use, and that's been infrequent for awhile - but I'm reactivating it in hopes of getting some some answers to an edited version of my second set of questions.

Thanks.

1.  Is using a macro the only way to customize the default comment format?

If so, how much can a macro automate?  In other words, how close can a macro get me to the following custom "default" format?

      a) be totally blank (i.e. no user name or other character)

      b) be centered both horizontally & vertically DONE

      c) set the size to automatic

      d) have plain text (not bolded) that doesn't affect anything else (like ToolTips)? The font and size are fine.

2. If one macro can't do it, which of (a), (c) and/or (d) can you add to the macro you've already written?

3.  Is it possible for this or any macro to run automatically when you open any workbook, so that whenever I insert a comment it's ready to go?

Answer 2

Thanks, Mike, for taking this on.

To follow up:

I gather that one can't customize the default comment format without using macros.  Is this assumption correct?

If so, how much can a macro automate?  How close can a macro bring me to a custom "default" format?

Specifically,

1.  Can a macro give me the following result when I insert a new comment?

a) be totally blank (i.e. no user name or other character)

b) be centered both horizontally & vertically (which your current macro does)

c) set the size to automatic

d) and have plain text (not bolded) that doesn't affect anything else (like ToolTips) in Excel? The font and size are fine.

2. If one macro can't do it, can you add 1 and 3 to the macro you've already written?
3. It looks like I need to run your macro after I've entered content in the comment and closed it. Is that correct?

4.  Is it possible for this or any macro to run automatically when you open a workbook, so that whenever I insert a comment it's ready to go?

I know that's a lot, but like I said, I know nothing about macros in Excel.

Yes, I would really like to know a simple way to set the default format for comments.  I don't want my name to show on every comment, and I'd like to default to a larger font and larger box size.  Is there a way to do this without having to change it every time?

Answer 3

Thanks, Mike, for taking this on.

To follow up:

I gather that one can't customize the default comment format without using macros.  Is this assumption correct?

If so, how much can a macro automate?  How close can a macro bring me to a custom "default" format?

Specifically,

1.  Can a macro give me the following result when I insert a new comment?

a) be totally blank (i.e. no user name or other character)

b) be centered both horizontally & vertically (which your current macro does)

c) set the size to automatic

d) and have plain text (not bolded) that doesn't affect anything else (like ToolTips) in Excel? The font and size are fine.

2. If one macro can't do it, can you add 1 and 3 to the macro you've already written?
3. It looks like I need to run your macro after I've entered content in the comment and closed it. Is that correct?

4.  Is it possible for this or any macro to run automatically when you open a workbook, so that whenever I insert a comment it's ready to go?

I know that's a lot, but like I said, I know nothing about macros in Excel.

Answer 4

Hi,

Here's a way with a macro and if it's your first time they're easy.

1. Hold down the ALT key and tap F11 to open VB editor.
2. On the left side of the new window you should see 'Project Explorer, if you cant then CTRL + R
3. Right click 'ThisWorkbook' and insert module and paste the code below in on the right
4. Nearly done now, close VB editor.

Back on the worksheet

1. View tab|Macros
2. You should see the macro name  "Align comment text"
3. select it and click RUN and you're done.

What you can do is create a shortcut for your macro. In that Popup click the Options box and enter a letter as your shottcut and now CTRL + letter runs the code.

Sub Align_Comment_Text()

  Dim c As Comment

  If ActiveSheet.Comments.Count > 0 Then

    For Each c In ActiveSheet.Comments

       With c.Shape.TextFrame

        .HorizontalAlignment = xlHAlignCenter

        .VerticalAlignment = xlHAlignCenter

        End With

    Next

  End If

End Sub