Color Pallete Problem

  • Thread starter Thread starter BLB
  • Start date Start date
B

BLB

I loaded Excel 2002 on Windows XP a few weeks ago. I did not change the
default color pallet. However, all of a sudden the pallet on some
workbooks changed and when I open 2 workbooks (1 with the correct
default pallet, and 1 that somehow got modified), the 1st workbook
takes on the pallet of the 2nd modified workbook. Very annoying to know
that I highlited a cell in orange, but because I have a 2nd workbook
open, that cell is now some brown color.

I have tried reinstalling MS Office. I can't copy the pallet from one
workbook to another because the problem is that the default pallet is
the one I want, but the modified pallet is the one that takes over.

This happened on several workbooks, but then others didn't get changed
from the default... HELP!
 
Goto Tools>Options>Colour and click Reset.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
I had tried that already, it doesn't work either. I noticed also tha
it doesn't save that way. I can later open a workbook, that didn'
have the modified colors, all by itself and the colors will revert bac
to the default
 
Don't know how one WB can adopt the palette of another,
without some form of explicit instruction.

You mention Orange vs some brown. Colorindex for Orange
in a modern un-customised palette is 45 (just right of
red). Colorindex 45 in an old palette (xl95) just so
happens to be "some brown", coincidence!

Try this macro

Sub Test1()
MsgBox ActiveWorkbook.Colors(45)
'modern palette returns 39423 ( rgb 255,153,000)
'old palette returns 3368601 ( rgb 153,102,051)
End Sub

Do you get 3368601, by chance?

I've had some strange experiences working with "new"
and "old" default palettes open at the same time, but
nothing I can directly relate to your experience.

Normally you should be able to Reset the palette in Tools
Options > Color, and copy palettes between wb's from
Click to expand...
the same tab. If that's not possible try this:

Sub Test2()
Dim pal As Variant
pal = Workbooks("MyBook1.xls").Colors
Workbooks("MyBook2xls").Colors = pal
End Sub

This might give a customised palette that's the same as a
default palette, but frankly I'm puzzled by what you
describe.

Regards,
Peter
 
Hi Peter,

Yes, I agree it is very weird.

I do get 3368601 when I run the Test1 macro. I was running Excel 200
on Windows XP previously. The really weird thing is that if the ol
palettes somehow didn't get converted to the new, you would think i
would have happened all WB's, not just some of them. I am running of
of a server, and unfortunately do not know how many WB were affected
otherwise if worse came to worse I could just go in manually and chang
all the palette's. Ugh!

I have tried several times, and definitely cannot copy (because the W
that the palette is getting copied from has to be open) and canno
reset.

When I run the Test2 macro, I am getting a "Subscript out of Range
runtime error. I am new to VBA and am just learning, so what am
doing wrong? I'm reading the code as: pal gets the colors of the W
that I want to use. Then I'm assigning pal to the workbook that I wan
to change. Possibly I am typing in the name of the WB wrong? I a
running the macro in the WB that I am trying to change. Is thi
correct
 
Sounds like some of your wb's date back to pre XL97. I
used to assume that the definition of a default palette
was only within the Excel application. I now think there
may also a definition within the wb (perhaps only in
specific cases), as well as any customised definition of a
palette.

One possibility is that when your old wb's were
transferred to xl2000, the old default palette was reset
to the new default palette. But the old definition was
still within the wb, then on recent upgrade the old
definition got reset. This is pure speculation, but ties
up with some of my experiences. Another oddity is in most
cases if I reset an old default palette, it get's changed
to the new palette, but not in all cases. Also cannot
always reset an old default palette in vba.

I have a few more thoughts about how default palettes are
defined, but would be interested if anyone has something
more "definitive" on the subject (all is not as it seems!)

Re the macros:
Test1 can be in any wb, it displays info about the
currently active wb.

Test2 can be in any wb. Both named wb's must be open and
the one you are copying to unprotected. If a wb has not
been saved do not include the extension ".xls". I'm not
sure about network issues, maybe copy to a local drive.
If it still fails, open the VBE (Alt F11), put the cursor
within the sub and keep pressing F8, report which line it
fails on etc.

If you create a new wb it should start with the "new"
default palette from which you should be able to copy to
other wb's, either using Tools/options etc or the Test2
macro. If a new wb does not open with a "new" palette the
only thing I can think of is you have "Book.xlt" in your
startup folder with a definition of the "old" default
palette.

Regards,
Peter
 
Thanks for all your help, Peter. Your thoughts on the subject make som
sense in this case. It is entirely possible that these WB date back t
xl97. I will give Test2 another try.

The good thing is that "Book.xlt" DOES have the new palette so at th
least the problem won't spread. :
 
It is entirely possible that these WB date back to xl97.

Would be pre xl97, xl97 has the "new" palette"
The good thing is that "Book.xlt" DOES have the new
palette so at the least the problem won't spread. :)
Click to expand...

Make doubly sure the default palette of "Book.xlt" is the
new palette, not merely a customised copy of a new default
palette, not quite the same thing.
If in any doubt create a new Book.xlt, that's not based on
the old one (remove it from xlstart & restart Excel first)

Regards,
Peter
 
Hi Peter,

Aha! Guess what else I noticed? The "Reset" button in the colo
palette, that is supposed to restore the default color palette, doesn'
work! What it does is restore the change I just made to the palett
before closing the option window. If I change a color and select "Ok
to close the window and then go back in and hit "Reset", nothing
happens.

So then, I went in search of "book.xlt" and can't find it in th
XLSTART directory. I have a "Personal.xls" where I have my macros, bu
that is it. Does "book.xlt" only get created if I create it t
customize my Excel? The only other file I see is "Excel10.xlb" outsid
of the XLSTART folder. Not sure what that is.

So, how exactly do I find out what the default palette is?

Thanks so much,
Beck
 
Hi Becky

This sounds more than weird!

Just to be sure I'm following correctly, are you saying
this:
1. Options Color, Modify, customise a default colour to
new colour
2. Close Options dialog
3. New colour can be formatted
4. Options Color, Reset palette, colour does not change in
the options dialog
5. Close Options
6. Customised colour is still there, and same can be
applied as a colour format from a drop down colour icon.

Right? Also do same with default Red and the Orange right
of red (red is common to "old" and "new" palettes, orange
isn't).

Another macro for you, it's equivalent to resetting the
palette manually, although theoretically the manual method
is more likely to work than this macro:

Sub Test3()
ActiveWorkbook.ResetColors
End Sub

(ActiveWorkbook means the currently selected workbook)

Book.xlt: if you don't have it don't worry about it, most
people don't. From your previous post I understood you
did. For reference, you can create a template on which
all new workbooks will be based on, (incl any customised
palette, hence my reason for mentioning it as a
possibility). Save a template named Book.xlt and put it
in the XlStart folder (but don't do that now).

Excel10.xlb is where your customized toolbars are stored
and very unlikely related to this issue (if you customise
toolbars it's worth backing up this file periodically,
occasionally it gets corrupted).

I'm running out of ideas but will be interested to know
what's been going on.

Regards,
Peter
 
Hi Peter,

That is correct. Tried changing the red and the orange(old and new
and same thing happens. Can not reset.

Test2 does not work. Test3 does not work.

This is what else I've tried:
1) I decided to reinstall office AGAIN and noticed that the last time
reinstalled it, I didn't completely remove Office XP befor
reinstalling. In fact it looked like I had just upgraded when
installed it the first time. I noticed that part of the old Office wa
still installed. I got excited at this point because that would explai
the problem. So I completely removed all of MS Office, old and new
and reinstalled it. No change. :(

2) I went on someone elses computer to verify that the reset button i
Excel 2002 does work the way its supposed to. It does.

book.xlt: I thought that I had it as well. I was confused...thinkin
that when you start a new workbook that is the file the windows uses b
default not knowing that that is the case only if you create a custo
template.

I'm about ready to give up...

Beck
 
Hi Becky,

Well there's got to be a reason, I assume giving up is not
an option!

I take it your inability to reset a customized palette
applies to all including new files, not only your old
files. It seems established that lurking somewhere is an
old file / old palette. Even so I don't know why that
should cause these problems. Try some elimination, testing
as you go:

Remove everything in your XlStart folder to a temporary
adjacent folder. Same if you have an "Alternate startup
file location" (Options/General). Did you say you are on
a network, if so do likewise on your server. Under
the "Window" menu, Unhide should be greyed out. No shared
files.

Make a list of checked Add-ins (Tools) and uncheck them.
If you cannot do same on your server ask if there are any
old bespoke add-ins (don't know if these would load to
you).

On another PC run following over a new wb to save the
definition of a default palette (put the macro in yet
another wb, make the new wb active)

Sub SavePalette()
Dim i As Byte
For i = 1 To 56
Cells(i, 1).Value = ActiveWorkbook.Colors(i)
Cells(i,2).interior.colorindex = i
Next
End Sub

Save the new file as MyBook.xls and also as "Book.xlt"
(not Book1.xlt, an easy mistake). Mail or floppy to your
PC. Do some customise/reset tests with MyBook.xls

Insert a new Column A and run Savepalette again, but
comment the line that applies the colorindex format (no
need to colour cells again, they will change to display
the current palette). While customizing / resetting use
this formula to look for differences:
=A1=B1 copy down and look for False.

Put Book.xlt in your XlStart folder. Restart XL and new
wb's should show numbers in A1:56. Try some more
customize / reset tests on new & old files as for
MyBook.xls

Still cannot reset, run following with the palette colour
value numbers in A1:A56

Sub LoadPalette()
Dim i As Byte
For i = 1 To 56
ActiveWorkbook.Colors(i) = Cells(i, 1).Value
Next
End Sub

This should customise the palette with the that of the
default palette (looks same but a workaround to resetting
the default palette). However as Test2 does not work this
may not either, it works in a similar way. But if it does
try Test2 and Test3 again.

When done remove Book.xlt from xlstart.

I'm not over optimistic any of the above will solve the
problem but give it a shot. Some of what I suggest is
based on my speculation that the default palette is
defined by an algorithm / loop method (rather than
constants), possibly contained with the WB in addition to
constants of any customized palette. And that the "old"
definition can somehow corrupt the "new" definition. I
could be totally wrong about this idea, but it ties up
with some other observations I've made.

Good luck,
Peter
 
This is an old post, but I wanted to post the solution for others t
benefit from. I did a complete re-install of Windows XP and the whol
problem was fixed!

Thanks, Peter, for all your help
 
Back
Top