Color tab, reset button

Question

The reset button and the "copy colors from" feature on the color tab do not work.  I can't restore my default color palette that has been corrupted by the color palette from another file.  Is there some sort of lock that prevents me from changing this?  Now, when I have open the file on which I want to restore the default, and I open another file, the newly opened file takes the color palette of the first file.  Don't know what is going on here.

Answer 1

First, are you aware that colour formats used in 2007/2010 files will typically appear different when converted to xls. That's because most used "scheme" colours will map to the nearest colour that exists in the limited 56 colour palette.  IOW, don't expect colour formats to look the same when loaded into say 2003.

The fact that you say ResetColors "yields no response", ie neither changes any colours nor errors, suggests the palette is already "default". Let's see.

Ensure the workbook concerned is active

From Excel press Alt-F11 to open the VBE

Press Ctrl-g to open the Intermediate window.

In that window (bottom pane) paste the following (incl the ?)

?Join(ActiveWorkbook.Colors, ", ")

With the cursor at the end hit Enter, post back the string of 56 Long colour values.

Regards,

Peter Thornton

PS, whilst it's possible code could be copying workbook palettes if these are your own files it would seem unlikely.

Answer 2

My file was created in Excel2010 and converted from an xlsx file to an xls file.  The file that corrupted my file was created with an unknown version of excel, but probably not any older than 2003 or maybe it did originate from some older version.  I didn't even have to copy anything from the that file to my model; the legacy worksheets in my model took on the weird colors in both the background and the characters.

Activeworkbook.ResetColors in a sub yields no response.

Where would I find (and delete) if there is code that automatically runs to copy customized palette to a newly opened workbook?

Regards,

Paul

Answer 3

I can see your palette started life as a normal 2003 palette (not pre '97 style I mentioned). The bottom two rows of the 56 colour palette have all been customized (rows (6 & 7) as has the top right colour (from almost black to light grey).

The bottom two rows are the chart colours, these are not visible in the normal drop-down palette, though they are shown in various other dialogs. (It's quite normal for people to want to customize the chart colours.)

I'm afraid I can't explain what's going on with your files. I can only repeat, normally a customized palette 'belongs' to the workbook and without some separate interventioncannot change either the default palette or the palette of some other workbook.

Can you confirm that the colours that are changing are the ones I listed above. To see the entire palette do Ctrl-1 and select the Patterns tab. Compare respective palettes in your files as well as a new workbook.

For anyone else curious to see Paul's palette - in 2003 or earlier - start with the following code

ActiveWorkbook.Colors = Array()

Copy Paul's 56 comma separated long colour values in one go,

Paste between the brackets after Array() and run the code (in a Sub or even from the Immediate window)

Look for customized colours as I described above.

Regards,

Peter Thornton

Answer 4

The following came back:

?Join(ActiveWorkbook.Colors, ", ")

0, 16777215, 255, 65280, 16711680, 65535, 16711935, 16776960, 128, 32768, 8388608, 32896, 8388736, 8421376, 12632256, 8421504, 9400389, 52479, 8421504, 14540253, 5784618, 11570268, 4440443, 2002935, 12955136, 5103829, 3672501, 9251438, 15381779, 3355443, 16777215, 0, 16763904, 16777164, 13434828, 10092543, 16764057, 13408767, 16751052, 10079487, 16737843, 13421619, 52377, 52479, 39423, 26367, 10053222, 9868950, 6697728, 6723891, 13056, 13107, 13209, 6697881, 10040115, 15395562

The file that originally caused the problem was not my file, but was given to me to use by a reliable source.

Regards,

Paul

Answer 5

In theory what you describe cannot happen! The 56 colour palette can be customized but it resides with the workbook. That's to say a customized palette that belongs to one workbook cannot affect any other workbook.

I don't know why Reset Colors doesn't work (I assume you mean from the Color tab of the Options dialog). I haven't double checked but I don't think it's subject to any of the Protection locks. Try this line of code (in a Sub or from the Intermediate window)

Activeworkbook.ResetColors

(ensure the workbook whose palette you want to reset is active first)

Does it work or if not do you get an error message?

Are you sure there's no code that automatically runs to copy the customized palette to a newly opened workbook.

Now I come to think of it, in previous versions of Excel (97 and 2000), I recall odd problems with palettes when very old workbooks originally made in pre '97 versions were opened, these had a slightly different 56 colour palette by default. I don't recall any problems in Excel 2003 but are you by chance working with any very old files?

Regards,

Peter Thornton