PDA

View Full Version : Changing Colour Palette



Adamski
11-12-2009, 04:21 AM
Hello,

I have encountered a strange problem (bug?) with the colour palette (Excel 2003, Win XP Pro SP2 x32). Basically, it gets reset(ish) when opening a workbook which was saved with 2+ windows open. All windows but the last instance have the colours reset when reopening the workbook.

To reproduce, try this:

Open a new workbook.
Set some cells backcolour eg. Red (col1, row3 of default palette)
Go to Tool-Options-Color
Click the colour you used
Click Modify
Click a new colour eg.to Blue
Click OK
Click OK
[Your coloured cells should change colour as expected]
Click Window-New Window (Repeat this if you want)
[New windows show the picked colour]
Save the workbook
close the workbook (all windows)
optional: close excel
open the saved workbook
[Colours reset to default on all but last instance!]

Any solutions? Or is it just me?!

my current workarround is to ensure only one window is open when I save:


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

' Close all but one window of the workbook because otherwise
' the colours get reset when opening again! Stupid Excel
Dim WindowIndex As Integer
For WindowIndex = 1 To ThisWorkbook.Windows.Count - 1
ThisWorkbook.Windows(1).Close
Next WindowIndex

ThisWorkbook.Windows(1).WindowState = xlMaximized

End Sub


but of course this will probably upset the users when theit windows start disappearing!

Bob Phillips
11-12-2009, 04:36 AM
I can reproduce that - it is totally wierd.

Bob Phillips
11-12-2009, 10:57 AM
Adamski,

I mentioned this on an MS private forum, and a colleague of mine sent me this example. It seems to be a known problem.

It works in Excel 2003 or earlier. Follow the on sheet instructions.

Adamski
11-13-2009, 07:36 AM
Altered save workaround to keep the original window (I have freeze pans in it):
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

' Close all but the original window of the workbook because otherwise
' the colours get reset when opening again! Stupid Excel
Dim aWindow As Window
For Each aWindow In ThisWorkbook.Windows
If aWindow.WindowNumber <> 1 Then
aWindow.Close
End If
Next aWindow

End Sub

This is my other workaround:
Private Sub Workbook_Open()

Dim NumberOfWindows As Integer
NumberOfWindows = ThisWorkbook.Windows.Count

If NumberOfWindows > 1 Then

' Array to store current Window dimensions
Dim WindowDimensions() As Double
ReDim WindowDimensions(2 To NumberOfWindows, 4)

Dim WindowIndex As Integer

' Close all but one window of the workbook because only
' the colour palette of the first window is correct
For WindowIndex = NumberOfWindows To 2 Step -1

WindowDimensions(WindowIndex, 1) = ThisWorkbook.Windows(WindowIndex).Top
WindowDimensions(WindowIndex, 2) = ThisWorkbook.Windows(WindowIndex).Left
WindowDimensions(WindowIndex, 3) = ThisWorkbook.Windows(WindowIndex).Width
WindowDimensions(WindowIndex, 4) = ThisWorkbook.Windows(WindowIndex).Height

ThisWorkbook.Windows(WindowIndex).Close

Next WindowIndex

' Ensure the Workbook window is not Maximised so dimensions can be set
' and the user realises they are there
ThisWorkbook.Windows(1).WindowState = xlNormal

' Recreate the closed windows from the remaining window
' with the correct colour palette
For WindowIndex = 2 To NumberOfWindows

Dim aNewWindow As Window
Set aNewWindow = ThisWorkbook.Windows(1).NewWindow

aNewWindow.Top = WindowDimensions(WindowIndex, 1)
aNewWindow.Left = WindowDimensions(WindowIndex, 2)
aNewWindow.Width = WindowDimensions(WindowIndex, 3)
aNewWindow.Height = WindowDimensions(WindowIndex, 4)

Next WindowIndex

Else
' Maximise the Workbook window
ThisWorkbook.Windows(1).WindowState = xlMaximized
End If

End Sub
I don't like it much though since you lose the freeze panes of the original window because the good palette is alway in the last window instance, and the freeze panes in the first.

Is there a way to reopen the closed windows after the save in Workaround 1?