Changing decimal and thousands separators

The Problem

Last week one of Allen's readers asked how to display numbers differently depending on the workbook.  Specifically, the reader had one workbook that should be displayed with a comma between the thousands and a comman where the decimal point goes.  In the US we usually see "12,345,67".  This workbook should show this as "12.345.67".
After a bit of research ("Hoew do I change this setting manually?") and recording a macro, I came up with the following.  It is what I sent to Allen, with a bit of editing.

The Answers

Part 1: Workbook-specific Default changes

The answer is fairly simple, if Allen's reader is comfortable with a small amount of VBA code in his workbook.

The key to his requirement is that he wants the non-U.S. formatting only when the special workbook is active, and back to the default when it is inactive.

Therefore the answer is in the WorkBook event modules, specifically Workbook_Activate and Workbook_Deactivate.

The Workbook_Activate event module sets the decimal and thousands to their special values, and turns the UseSystemSeparators property to False.

Private Sub Workbook_Activate()

    With Application

        .DecimalSeparator = ","

        .ThousandsSeparator = "."

        .UseSystemSeparators = False

    End With

End Sub

The Workbook_Deactivate event module does the opposite, setting the decimal and thousands to the normal values, and turns the UseSystemSeparators property to True. 

Private Sub Workbook_Deactivate()

    With Application

        .DecimalSeparator = "."

        .ThousandsSeparator = ","

        .UseSystemSeparators = True

    End With

End Sub

Part 2: Worksheet Specific Formatting

The above can also be used when working with a workbook that has a worksheet where the preferences is spaces for the ThousandsSeparator, or for that matter, any other character for the ThousandsSeparator or DecimalSeparator.

For example, if there is one sheet in a workbook that needs to have spaces as the thousands separator, use the following in the Sheet's module

Private Sub Worksheet_Activate()

    With Application

        .DecimalSeparator = "."

        .ThousandsSeparator = " " ' This was a comma

        .UseSystemSeparators = False

    End With

End Sub

Private Sub Worksheet_Deactivate()

    With Application

        .DecimalSeparator = "."

        .ThousandsSeparator = ","

        .UseSystemSeparators = True

    End With

End Sub