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
Else
Selection.NumberFormat = sCOMMATWO
End If
Else
If pf.NumberFormat = sTWODECIMALS Then
pf.NumberFormat = sONEDECIMAL
Else
pf.NumberFormat = sTWODECIMALS
End If
End If
End If
End Sub
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 Next For Each st In ThisWorkbook.Styles c00 = c00 & vbLf & st.Name & "_" & st.BuiltIn Next MsgBox c00 End Sub>>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.
Jim
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.
Seb
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.
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.