Applying NumberFormat Version 4

I’ve been using a macro to apply the comma style for about five years. I’ve even enhanced it. It’s such a simple bit of code that it really never broke – until now. I got a workbook that caused the code to fail because it didn’t have a style named ‘Comma’. I just assumed that style was built in to all workbooks.

I received a workbook in 2003 format (.xls). I Saved As to 2010 format (.xlsx) because that’s what I always do. I even closed and re-opened it because I don’t like it when it says ‘Compatibility Mode’ in the title. Here’s what the Styles gallery looks like compared to the Styles gallery on a new workbook below.

OK, somebody deleted the number format styles. Seems strange, but I’ve seen stranger. Then more strangeness occurred. None of the cells on this unprotected worksheet were locked. And when I add a new worksheet to the workbook, every cell on the new worksheet was unlocked. I don’t recall ever seeing that before. Generally, every cell on a new worksheet is locked and the sheet is unprotected. That way when you protect a worksheet, you get expected behavior.

I still don’t understand why this workbook behaves the way it does, but I do need to change my code to account for it. I toyed with the idea of adding a Comma style if it didn’t exist, but the Comma style is just a number format, so I didn’t see any downside to just applying a number format instead of a style. Also, I’ve thoroughly enjoyed my new toggle feature for PivotItems that switches between zero and two decimal places and applied that same logic to cells.

Sub MakeComma()

Dim pf As PivotField

Const sONEDECIMAL As String = "#,##0"
Const sTWODECIMALS As String = "#,##0.00"
Const sCOMMAONE As String = "_(* #,##0_);_(* (#,##0);_(* ""-""_);_(@_)"
Const sCOMMATWO As String = "_(* #,##0.00_);_(* (#,##0.00);_(* ""-""??_);_(@_)"

gclsAppEvents.AddLog "^m", "MakeComma"

If TypeName(Selection) = "Range" Then
On Error Resume Next
Set pf = ActiveCell.PivotField
On Error GoTo 0

If pf Is Nothing Then
If Selection.NumberFormat = sCOMMATWO Then
Selection.NumberFormat = sCOMMAONE
Selection.NumberFormat = sCOMMATWO
End If
If pf.NumberFormat = sTWODECIMALS Then
pf.NumberFormat = sONEDECIMAL
pf.NumberFormat = sTWODECIMALS
End If
End If
End If

End Sub

5 thoughts on “Applying NumberFormat Version 4

  1. It look srather esy to remove buitlin styles:

    Sub M_snb()
      For Each st In ThisWorkbook.Styles
        If Left(st.Name, 5) = "Comma" Or Left(st.Name, 8) = "Currency" Or st.Name = "Percent" Then st.Delete
      For Each st In ThisWorkbook.Styles
        c00 = c00 & vbLf & st.Name & "_" & st.BuiltIn
      MsgBox c00
    End Sub
  2. >>Generally, every cell on a new worksheet is locked and the sheet is unprotected.

    Sounds like the Normal style was redefined. I advise people to leave the built-in styles as they are and make your own. Fewer problem with other workbooks, and for other users.


  3. I think the “normal” style must be set to have cells unlocked.

    I modified the settings of the “normal” style to uncheck the “Locked” and created a new worksheet and all the cells on the new worksheet were unprotected.


  4. You can always retrieve the builtin styles from the personal.xlsb file:

    Sub M_snb()
    Application.DisplayAlerts = False
    ActiveWorkbook.Styles.Merge Workbooks("Personal.xlsb")
    Application.DisplayAlerts = True
    End Sub

    Maybe it's practival in your case to add the onedecimal & twodecimal numberformats as styles to the personal.xlsb workbook.
    So you can then always refer to their stylenames and your constants will become redundant.

  5. Normal style is locked. I create a new sheet and ?activecell.Style returns Normal, but the cell is still unlocked. If I click on the Normal style, which is already highlighted, the cell becomes locked. Strange.

Leave a Reply

Your email address will not be published. Required fields are marked *

To create code blocks or other preformatted text, indent by four spaces:

    This will be displayed in a monospaced font. The first four 
    spaces will be stripped off, but all other whitespace
    will be preserved.
    Markdown is turned off in code blocks:
     [This is not a link](

To create not a block, but an inline code span, use backticks:

Here is some inline `code`.

For more help see