Results 1 to 2 of 2
2012-04-30, 04:00 #1
- Join Date
- Feb 2002
- Canberra, Australian Capital Territory, Australia
- Thanked 0 Times in 0 Posts
Excel 2007 - Bizarre uncommanded format change from number to date
I am working on an macro enabled excel spreadsheet that has numerous sheets with both number, date formatted cells. A number of the sheets also include pull down selections linking to another sheet (data vlidation). For some strange reason (I am pretty sure this happens overnight???) the majority of the numbers get converted to date fomat, ie 3 => 3 Jan 00. This is painful because I have to then go back, select the column-format-general or number...on numerous spreadsheets. Also, all the 'white space' (unoccupied cells) have also been converted to date format.
If I make the changes, save the document (different filename), and then open it on the same day the corrected number format is maintained. However the next working day (?) I open the document and 'hey-presto' the majority of numbers are all changed to date format (this includes pull down lists and calculated numbers - typically between 1 and 200)!
2012-04-30, 06:56 #2
- Join Date
- Mar 2002
- Newcazzle, UK
- Thanked 542 Times in 517 Posts
This 'issue' has been raised previously with mysterious Swedish formats appearing etc - see this reply:
Nasty problem. Seems that this can occur with large spreasheets, particularly those with pivot tables, affects all versions of Excel2003, 2007, 2010.
Check the entries in your custom formats list: it probably has multiple repeated entries, which you can delete.
When you copy cells from another workbook into your excel file, it seems that custom formats are possibly 'imported' too. Might be a memory issue when too many custom formats get 'imported'.
You can 'fix' a corrupted Normal format as follows:
Click on cell styles in the Home ribbon.
Right click on "Normal"
Change format to General
Another way would be to remove ALL custom formats from the file.
You could use the following:
Sub removeStyles() 'Remove all styles other than Excel's own.
Dim st As Style
For Each st In ThisWorkbook.Styles
If Not st.BuiltIn Then st.Delete
let us know how you get on..