Is there a way to find out the default number formats for Excel? For
example, if you open the "Format Cells" dialog box (Menu Bar: Format /
Cells, or Ctrl+1), click the "Number" page, and select "Date", the
"Type:" box populates with a number of options. I'm asking if there
is a way to programmatically discover what these defaults are.

The reason I want to know this is because I received an Excel file
this past week that had so many Custom formats that the file wouldn't
let me make formatting changes. I've never seen this error before,
but I've also never seen so many custom formats before. Ultimately, I
would like to delete the non-default formats, and maintain the default
custom formats without accidentially deleting them (if that's even
possible). (Hopefully this makes sense).

My thought process is below. I can do the comparision part, but I
don't know how to find out what the default formats are. Any help is
greatly appreciated. Thanks in advance.

Matt

Sub test_Format()

Dim n As Variant

'place default formats into an array, e.g. formatAry()

For Each n In Selection.Cells
' Debug.Print n.Address; ": "; n.NumberFormat
'compare formatAry(i) to n.NumberFormat
'If n.NumberForat < formatAry(i) Then
'n.DeleteNumberFormat x
Next

End Sub

If you select "Custom" at the bottom of the list then the custom formats
appear at the bottom of the "Type" list.
The custom formats are the only ones that can be deleted - by
clicking the "Delete" button. That button is grayed out if a
built-in format is selected.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)




wrote in message
Is there a way to find out the default number formats for Excel? For
example, if you open the "Format Cells" dialog box (Menu Bar: Format /
Cells, or Ctrl+1), click the "Number" page, and select "Date", the
"Type:" box populates with a number of options. I'm asking if there
is a way to programmatically discover what these defaults are.

The reason I want to know this is because I received an Excel file
this past week that had so many Custom formats that the file wouldn't
let me make formatting changes. I've never seen this error before,
but I've also never seen so many custom formats before. Ultimately, I
would like to delete the non-default formats, and maintain the default
custom formats without accidentially deleting them (if that's even
possible). (Hopefully this makes sense).

My thought process is below. I can do the comparision part, but I
don't know how to find out what the default formats are. Any help is
greatly appreciated. Thanks in advance.

Matt

Sub test_Format()

Dim n As Variant

'place default formats into an array, e.g. formatAry()

For Each n In Selection.Cells
' Debug.Print n.Address; ": "; n.NumberFormat
'compare formatAry(i) to n.NumberFormat
'If n.NumberForat < formatAry(i) Then
'n.DeleteNumberFormat x
Next

End Sub