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


  1. snb says:

    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. Jim Rech says:

    >>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. Seb says:

    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. snb says:

    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. Dick Kusleika says:

    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.

Posting code or formulas in your comment? Use [cc] tags!

  • [cc_vb]Block of code goes here[/cc_vb]
  • [cci_vb]Inline code goes here[/cci_vb]
  • [cc]Formula goes here[/cc]

Leave a Reply