Applying the Comma Style

I like the Comma style. It’s by far my favorite. What I don’t like is how hard it is to apply that style with the keyboard. In 2003, all you have to do is:

Alt + O + S + DownArrow + UpArrow x 4 + Enter

Unless you’re me. If you’re me, it’s Control+M. That’s because I have this macro in my PMW.

Sub ApplyCommaStyle()
    If TypeName(Selection) = “Range” Then
        Selection.Style = “Comma”
    End If
End Sub

And this line in the Auto_Open macro:

Application.OnKey “^m”, “ApplyCommaStyle”

Don’t think you can use ^M either. That means Ctrl+Shift+M because the M is capital. That one always gets me.

In 2007, Alt+H+K. That’s nice.

Posted in Uncategorized

6 thoughts on “Applying the Comma Style

  1. @ Sam: Yes. Ctrl+Shft+1 is actually the “Number” style, not comma. They’re pretty close, but for me comma shows negative numbers in brackets where in number it shows a – sign.

  2. I think comma style might be my _least_ favorite style. It won’t center or right align, which I need to do for certain reports. And it leaves a space at the end of the cell. I always remove that format from the formatting toolbar so that I don’t mistakenly use it, and replace it with my own “,” macro in my PMW and toolbar on my personal toolbar.

    Related, but it looks like you can change the attributes on the built-in cell format styles. But they don’t seem to stick globally, only within the spreadsheet where you make the change. I haven’t experimented with doing it on my PMW workbook since (a) it’s hidden and I’m too lazy and (b) I had a workaround anyway before I had that idea.

  3. No number format centers well, which is why I use custom format with a lot of __ (doubled underscores) at the end as opposed to changing from General to Right with indent horizontal alignment. I’ve never checked which took up more space in memory or on disk.

    As for right alignment, #,###_);(#,###);… does right align, though with right indentation equivalent to the width of a right parenthesis. Does Excel not work this way for you, chip? If it does, then Excel is working correctly since the intention of number formatting is that decimal places of the same order align. Sign characters are separate and need not align, though in the case if showing parenthesized negative numbers the spacing after the lowest order digit in positive numbers could be considered a sign character, so right sign characters would align.

    If you mean you’d expect the 0 in (positive) 210 to align with the ) in -21 so displayed as (21), then your preference is decidedly not shared by a majority of Excel users.

  4. fzz, probably true that my pref is not shared (or needed) by many, but it’s not exactly what you described (which would indeed be heinous). I’m always center aligning numbers under charts, and the default Comma style (which is linked to the “,” button on the formatting toolbar) is a format that will not center. I haven’t looked closely but I think it has the _ at the end that forces it to hold position. I also tend to prefer the -21 style rather than (21) since a lot of people I’ve worked with, surprisingly, graduate from college and don’t understand what parens mean around a number. (Probably they never looked at their bank statement.) I got tired of explaining it and just use the negative sign and center align “extra” data points under graphs.

  5. I agree with fzz regarding a “-” indicator vs. parentheses for negative values. Mildly annoying to some. Also if you omit the “Shift” from the keyboard shortcut, I believe it will only override the excel default keyboard shortcut in that workbook – containing the VBA.

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.