Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
selection.font.color returns wrong color; the first execution
This code was executed twice. Both times in the immediate pane to ensure
that nothing in my code was generating this error. Here are the results. (There should be 3 rows. The first row is the executed code and the next two rows are the resulting output.) ?"idx:" & Range("B5").font.colorindex & " (ThisWorkbook.colors(32) = &H" & Hex(ThisWorkbook.colors(32))& " clr:&H" & Hex(Range("B5").font.color) idx:32 (ThisWorkbook.colors(32) = &HFF clr:&HFF idx:32 (ThisWorkbook.colors(32) = &HFF clr:&HFF0000 ColorIndex 32 was set to red and that is what is displayed in Patterns and Font. Then the font color in cell B5 was set using index 32. (This is the bottom right of the palette displaying all 56 colors.) The first time the code is executed the font color of cell "B5" is reported as if it were Blue (&HFF0000). Second, and subsequent, executions correctly report the font color as Red (&H0000FF). This error can be repeated by selecting the worksheet (using Alt-Tab or any other method) and then reselecting the VB code window. Executing this statement reports the same color both times. Thus, the workaround is not simply referencing the value twice. ...........?Range("B5").font.color & Range("B5").font.color Is there a workaround or am I "missing" the logic for this problem? ==== Why ColorIndex 32 is being used ============================================ Here is the situation. A worksheet allows the selection of three font colors and the font color will be used to interpret what the entry means. So, if the user decides to use Black for two or three of the fonts I need to modify the font slightly so they see their chosen color. If the font colors did not need to be unique there would be no issue. Luckily, no one is likely to detect the difference between 3 cells where the three cells contain text with a font color of &H0, &H1, and &H2, yet the program can detect the difference. To impact the workbook colors ColorIndex 30, 31, and 32 were chosen to hold the three font colors. These indexes are the last three cells on the last row of the full palette. They are not normally seen when using the drop down font selection. When they change the font color on the color selection worksheet the code checks all three font colors and ensures they are unique. If not, then one or two are changed in such a way as to change the font color by 1 unless all three were chosen to be either black or white. Then one of the colors must be changed by a value of 2. Once the colors are verified to be unique, their respective ColorIndex is set to the chosen color, then the cells where the user chooses the font color are set to match it's respective ColorIndex. The problem is that the program was changing the colors! I assumed it was an error until the problem could be replicated using the code shown. Any suggestions? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
selection.font.color returns wrong color; the first execution
I found a workaround.
Adding a line of code to select the active sheet works. Yep, it works fine if "ActiveSheet.Select" is executed. No responses and nothing found at Microsoft so I presume this is an unknown issue with Excel 2002 SP3 under Windows XP 2002 SP 1. Test code Debug.Print "idx:" & Range("B4").Font.ColorIndex & " (ThisWorkbook.colors(30) = &H" & Hex(ThisWorkbook.Colors(30)) & " clr:&H" & Hex(Range("B6").Font.Color) Debug.Print "idx:" & Range("B5").Font.ColorIndex & " (ThisWorkbook.colors(31) = &H" & Hex(ThisWorkbook.Colors(31)) & " clr:&H" & Hex(Range("B4").Font.Color) Debug.Print "idx:" & Range("B6").Font.ColorIndex & " (ThisWorkbook.colors(32) = &H" & Hex(ThisWorkbook.Colors(32)) & " clr:&H" & Hex(Range("B5").Font.Color) First execution idx:31 (ThisWorkbook.colors(30) = &H1 clr:&H80 idx:32 (ThisWorkbook.colors(31) = &H0 clr:&H808000 idx:30 (ThisWorkbook.colors(32) = &H2 clr:&HFF0000 Second execution idx:31 (ThisWorkbook.colors(30) = &H1 clr:&H1 idx:32 (ThisWorkbook.colors(31) = &H0 clr:&H0 idx:30 (ThisWorkbook.colors(32) = &H2 clr:&H2 -- My handle should tell you enough about me. I am not an MVP, expert, guru, etc. but I do like to help. "AnExpertNovice" wrote in message ... This code was executed twice. Both times in the immediate pane to ensure that nothing in my code was generating this error. Here are the results. (There should be 3 rows. The first row is the executed code and the next two rows are the resulting output.) ?"idx:" & Range("B5").font.colorindex & " (ThisWorkbook.colors(32) = &H" & Hex(ThisWorkbook.colors(32))& " clr:&H" & Hex(Range("B5").font.color) idx:32 (ThisWorkbook.colors(32) = &HFF clr:&HFF idx:32 (ThisWorkbook.colors(32) = &HFF clr:&HFF0000 ColorIndex 32 was set to red and that is what is displayed in Patterns and Font. Then the font color in cell B5 was set using index 32. (This is the bottom right of the palette displaying all 56 colors.) The first time the code is executed the font color of cell "B5" is reported as if it were Blue (&HFF0000). Second, and subsequent, executions correctly report the font color as Red (&H0000FF). This error can be repeated by selecting the worksheet (using Alt-Tab or any other method) and then reselecting the VB code window. Executing this statement reports the same color both times. Thus, the workaround is not simply referencing the value twice. ..........?Range("B5").font.color & Range("B5").font.color Is there a workaround or am I "missing" the logic for this problem? ==== Why ColorIndex 32 is being used ============================================ Here is the situation. A worksheet allows the selection of three font colors and the font color will be used to interpret what the entry means. So, if the user decides to use Black for two or three of the fonts I need to modify the font slightly so they see their chosen color. If the font colors did not need to be unique there would be no issue. Luckily, no one is likely to detect the difference between 3 cells where the three cells contain text with a font color of &H0, &H1, and &H2, yet the program can detect the difference. To impact the workbook colors ColorIndex 30, 31, and 32 were chosen to hold the three font colors. These indexes are the last three cells on the last row of the full palette. They are not normally seen when using the drop down font selection. When they change the font color on the color selection worksheet the code checks all three font colors and ensures they are unique. If not, then one or two are changed in such a way as to change the font color by 1 unless all three were chosen to be either black or white. Then one of the colors must be changed by a value of 2. Once the colors are verified to be unique, their respective ColorIndex is set to the chosen color, then the cells where the user chooses the font color are set to match it's respective ColorIndex. The problem is that the program was changing the colors! I assumed it was an error until the problem could be replicated using the code shown. Any suggestions? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
selection.font.color returns wrong color; the first execution
A guess - are you confusing the Activeworkbook and ThisWorkbook's palette's
(based on the observation your debug code returns info about each). If not post the full code. Regards, Peter T "AnExpertNovice" wrote in message ... This code was executed twice. Both times in the immediate pane to ensure that nothing in my code was generating this error. Here are the results. (There should be 3 rows. The first row is the executed code and the next two rows are the resulting output.) ?"idx:" & Range("B5").font.colorindex & " (ThisWorkbook.colors(32) = &H" & Hex(ThisWorkbook.colors(32))& " clr:&H" & Hex(Range("B5").font.color) idx:32 (ThisWorkbook.colors(32) = &HFF clr:&HFF idx:32 (ThisWorkbook.colors(32) = &HFF clr:&HFF0000 ColorIndex 32 was set to red and that is what is displayed in Patterns and Font. Then the font color in cell B5 was set using index 32. (This is the bottom right of the palette displaying all 56 colors.) The first time the code is executed the font color of cell "B5" is reported as if it were Blue (&HFF0000). Second, and subsequent, executions correctly report the font color as Red (&H0000FF). This error can be repeated by selecting the worksheet (using Alt-Tab or any other method) and then reselecting the VB code window. Executing this statement reports the same color both times. Thus, the workaround is not simply referencing the value twice. ..........?Range("B5").font.color & Range("B5").font.color Is there a workaround or am I "missing" the logic for this problem? ==== Why ColorIndex 32 is being used ============================================ Here is the situation. A worksheet allows the selection of three font colors and the font color will be used to interpret what the entry means. So, if the user decides to use Black for two or three of the fonts I need to modify the font slightly so they see their chosen color. If the font colors did not need to be unique there would be no issue. Luckily, no one is likely to detect the difference between 3 cells where the three cells contain text with a font color of &H0, &H1, and &H2, yet the program can detect the difference. To impact the workbook colors ColorIndex 30, 31, and 32 were chosen to hold the three font colors. These indexes are the last three cells on the last row of the full palette. They are not normally seen when using the drop down font selection. When they change the font color on the color selection worksheet the code checks all three font colors and ensures they are unique. If not, then one or two are changed in such a way as to change the font color by 1 unless all three were chosen to be either black or white. Then one of the colors must be changed by a value of 2. Once the colors are verified to be unique, their respective ColorIndex is set to the chosen color, then the cells where the user chooses the font color are set to match it's respective ColorIndex. The problem is that the program was changing the colors! I assumed it was an error until the problem could be replicated using the code shown. Any suggestions? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
selection.font.color returns wrong color; the first execution
No, but good point.
In this case the code resides in the current workbook so ActiveWorkbook and ThisWorkbook are the same. Thanks for the thought. A co-worker said he had a similar situation with Excel workbooks created by Business Objects. Opening the workbook allowed viewing the report but trying to print or do a print preview generated an error. Clicking on the worksheet tab "fixed" the problem. Essentially they are maunally doing an Activeworksheet.Select to work around the issue. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
selection.font.color returns wrong color; the first execution
So what's the actual code you use.
#32 in a default palette is 100% blue. It is possible to create a workbook that sustains two unique palettes concurrently, one default and one customized, each viewable in different windows of the same workbook. There's a bit of a knack to doing this (I always forget!) and easy to loose the dual palette. Perhaps something along these lines is occurring for you. Regards, Peter T "AnExpertNovice" wrote in message ... No, but good point. In this case the code resides in the current workbook so ActiveWorkbook and ThisWorkbook are the same. Thanks for the thought. A co-worker said he had a similar situation with Excel workbooks created by Business Objects. Opening the workbook allowed viewing the report but trying to print or do a print preview generated an error. Clicking on the worksheet tab "fixed" the problem. Essentially they are maunally doing an Activeworksheet.Select to work around the issue. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
selection.font.color returns wrong color; the first execution
I don't see how a dual palette can be used since changing the color of an
index immediately changes the color throughout the workbook. But, there is a lot I don't know and can't imagine! Now, this has become even more confusing the code below (much of it is redundant) was executed in two ways, each with similar but different results. First it was executed in the Immediate Window; one step at a time. Both ActiveWorkbook and ThisWorkbook were used. No change since the code effectively resides in the ActiveWorkbook. The results we 16711680 32 255 255 255 (ActiveSheet.Select performed here) 16711680 16711680 Color Test Then a new workbook was manually created, a module inserted, and the code was copied to a subroutine. The Workbooks.Add code was commented out. Before executing a second subroutine was created consisting of three lines of code Debug.Print ActiveSheet.Range("A1").Font.Color ActiveSheet.Select Debug.Print ActiveSheet.Range("A1").Font.Color These lines of code exist within the first subroutine so I would think the results would be the same. The results we First Routine, notice that the Font Color was reported as 255 (red) five times in a row, unlike in the Immediate Window. 16711680 255 32 255 255 255 (ActiveSheet.Select performed here) 255 255 Color Test Second Routine, oops. 16711680 255 (I'm sending the rest of my hair to Microsoft, but if they are not careful I will send them my first born later. ;) '=================== Start of code 'Start a new workbook being sure to start with the default colors and a known worksheet. Workbooks.Add Template:="Workbook" ActiveWorkbook.ResetColors Activeworkbook.Sheets(1).select Activeworkbook.Sheets(1).activate Activeworkbook.Sheets(1).Name = "Color Test" 'place the word Color in A1 ActiveSheet.Range("A1").value = "Color" 'Make the text of a size that color can be more readily seen. Activesheet.Range("A1").Font.Size = 14 Activesheet.Range("A1").Font.Bold = True 'Change the font color of A1 to the color in index 32. The font is now blue. Activesheet.Range("A1").Font.ColorIndex = 32 'Report the Font Color in the cell (FF0000, or 16711680; ie Blue) debug.Print Activesheet.Range("A1").Font.Color 'change the color of Index 32 to Red. The font is now red. ActiveWorkbook.colors(32) = &HFF 'Report the ColorIndex value. (32) debug.Print Activesheet.Range("A1").Font.ColorIndex 'Report the Color of the ColorIndex 32 (FF, ie Red) debug.Print ActiveWorkbook.Colors(32) 'Report the Font Color in the cell (FF, or 255; ie Red.... huh?) debug.Print Activesheet.Range("A1").Font.Color 'Report the Font Color in the cell again (FF, or 255; ) debug.Print Thisworkbook.WorkSheets("Color Test").Range("A1").Font.Color 'Now, select the ActiveSheet, just for fun Activesheet.select 'Report the Font Color in the cell again (FF0000, or 16711680; ) debug.Print Activesheet.Range("A1").Font.Color 'Ok, what is the font color now? debug.Print Thisworkbook.WorkSheets("Color Test").Range("A1").Font.Color 'Do a bit of testing debug.Print Thisworkbook.ActiveSheet.name '=================== End of code -- My handle should tell you enough about me. I am not an MVP, expert, guru, etc. but I do like to help. "Peter T" <peter_t@discussions wrote in message ... So what's the actual code you use. #32 in a default palette is 100% blue. It is possible to create a workbook that sustains two unique palettes concurrently, one default and one customized, each viewable in different windows of the same workbook. There's a bit of a knack to doing this (I always forget!) and easy to loose the dual palette. Perhaps something along these lines is occurring for you. Regards, Peter T "AnExpertNovice" wrote in message ... No, but good point. In this case the code resides in the current workbook so ActiveWorkbook and ThisWorkbook are the same. Thanks for the thought. A co-worker said he had a similar situation with Excel workbooks created by Business Objects. Opening the workbook allowed viewing the report but trying to print or do a print preview generated an error. Clicking on the worksheet tab "fixed" the problem. Essentially they are maunally doing an Activeworksheet.Select to work around the issue. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
selection.font.color returns wrong color; the first execution
I get similar results but only when I start by stepping through with F8
Sub test() ' compare difference in debug results between ' step through with F8 & run with F5 Workbooks.Add For i = 1 To 16 Cells(i, 1).Font.ColorIndex = i Next For i = 1 To 16 ActiveWorkbook.Colors(i) = 255 Next For i = 1 To 16 ' should return : i 255 255 ' though stepping through returns: i default palette color(i) 255 Debug.Print i, Cells(i, 1).Font.Color, _ ActiveWorkbook.Colors(Cells(i, 1).Font.ColorIndex) Next Dim r As Range Set r = [a6] 'put a break on next line and put cursor over r.Font.Color x = r.Font.Color ' tip under cursor reads = 65536 (yellow) but in Locals r.font = 255 as expected End Sub I've spent considerable time working with the Excel palette and still don't fully understand it's deep inner workings. Appears to belong to the workbook's "windows" object, which itself is a rather odd thing. But where or how is the "default" palette stored & defined. Doing certain things with the palette can crash Excel (albeit in rare scenarios). I don't see how a dual palette can be used since changing the color of an index immediately changes the color throughout the workbook. One of those can't-be-possible-but-is things. With two windows, apply colours to cells in one window and see different colours from the "other" palette update in same cells in the other window. Switch windows and the drop down palette changes. Regards, Peter T "AnExpertNovice" wrote in message ... I don't see how a dual palette can be used since changing the color of an index immediately changes the color throughout the workbook. But, there is a lot I don't know and can't imagine! Now, this has become even more confusing the code below (much of it is redundant) was executed in two ways, each with similar but different results. First it was executed in the Immediate Window; one step at a time. Both ActiveWorkbook and ThisWorkbook were used. No change since the code effectively resides in the ActiveWorkbook. The results we 16711680 32 255 255 255 (ActiveSheet.Select performed here) 16711680 16711680 Color Test Then a new workbook was manually created, a module inserted, and the code was copied to a subroutine. The Workbooks.Add code was commented out. Before executing a second subroutine was created consisting of three lines of code Debug.Print ActiveSheet.Range("A1").Font.Color ActiveSheet.Select Debug.Print ActiveSheet.Range("A1").Font.Color These lines of code exist within the first subroutine so I would think the results would be the same. The results we First Routine, notice that the Font Color was reported as 255 (red) five times in a row, unlike in the Immediate Window. 16711680 255 32 255 255 255 (ActiveSheet.Select performed here) 255 255 Color Test Second Routine, oops. 16711680 255 (I'm sending the rest of my hair to Microsoft, but if they are not careful I will send them my first born later. ;) '=================== Start of code 'Start a new workbook being sure to start with the default colors and a known worksheet. Workbooks.Add Template:="Workbook" ActiveWorkbook.ResetColors Activeworkbook.Sheets(1).select Activeworkbook.Sheets(1).activate Activeworkbook.Sheets(1).Name = "Color Test" 'place the word Color in A1 ActiveSheet.Range("A1").value = "Color" 'Make the text of a size that color can be more readily seen. Activesheet.Range("A1").Font.Size = 14 Activesheet.Range("A1").Font.Bold = True 'Change the font color of A1 to the color in index 32. The font is now blue. Activesheet.Range("A1").Font.ColorIndex = 32 'Report the Font Color in the cell (FF0000, or 16711680; ie Blue) debug.Print Activesheet.Range("A1").Font.Color 'change the color of Index 32 to Red. The font is now red. ActiveWorkbook.colors(32) = &HFF 'Report the ColorIndex value. (32) debug.Print Activesheet.Range("A1").Font.ColorIndex 'Report the Color of the ColorIndex 32 (FF, ie Red) debug.Print ActiveWorkbook.Colors(32) 'Report the Font Color in the cell (FF, or 255; ie Red.... huh?) debug.Print Activesheet.Range("A1").Font.Color 'Report the Font Color in the cell again (FF, or 255; ) debug.Print Thisworkbook.WorkSheets("Color Test").Range("A1").Font.Color 'Now, select the ActiveSheet, just for fun Activesheet.select 'Report the Font Color in the cell again (FF0000, or 16711680; ) debug.Print Activesheet.Range("A1").Font.Color 'Ok, what is the font color now? debug.Print Thisworkbook.WorkSheets("Color Test").Range("A1").Font.Color 'Do a bit of testing debug.Print Thisworkbook.ActiveSheet.name '=================== End of code -- My handle should tell you enough about me. I am not an MVP, expert, guru, etc. but I do like to help. "Peter T" <peter_t@discussions wrote in message ... So what's the actual code you use. #32 in a default palette is 100% blue. It is possible to create a workbook that sustains two unique palettes concurrently, one default and one customized, each viewable in different windows of the same workbook. There's a bit of a knack to doing this (I always forget!) and easy to loose the dual palette. Perhaps something along these lines is occurring for you. Regards, Peter T "AnExpertNovice" wrote in message ... No, but good point. In this case the code resides in the current workbook so ActiveWorkbook and ThisWorkbook are the same. Thanks for the thought. A co-worker said he had a similar situation with Excel workbooks created by Business Objects. Opening the workbook allowed viewing the report but trying to print or do a print preview generated an error. Clicking on the worksheet tab "fixed" the problem. Essentially they are maunally doing an Activeworksheet.Select to work around the issue. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
selection.font.color returns wrong color; the first execution
I found a reference and consolidated it into:
First row from left to right: 1, 53, 52, 51, 49, 11, 55, 56 second row: 9, 46, 12, 10, 14, 5, 47, 16 third row: 3, 45, 43, 50, 42, 41, 13, 48 fourth row: 7, 44, 6, 4, 8, 33, 54, 15 fifth row: 38, 40, 36, 35, 34, 37, 39, 2 sixth row first default row for charts: 17, 18, 19, 20, 21, 22, 23, 24 seventh row. second default row for charts: 25, 26, 27, 28, 29, 30, 31, 32 [a6] was set to Index 6, which is the 4th row, 3rd column, which is yellow. Thus, you have shown the similar pattern of a change in values based on executing code and code executed after a break. Seemingly regardless of whether the break was a break point or an End statement. I modified your code slightly. ' Workbooks.Add ActiveWorkbook.ResetColors 'x = r.Font.Color Debug.Print ActiveSheet.Range("A6").Font.Color Debug.Print ActiveSheet.Range("A6").Font.Color '<== Add Breakpoint ActiveSheet.Select Debug.Print ActiveSheet.Range("A6").Font.Color The results are the same, of course. If the code is run non-stop then all three Debug.Prints display the value 255. If a breakpoint is placed on the second Debug.Print statement then the first returns 255, the second 65535, and the third 255. So, it does seem that adding an Activesheet.Select statment provides a possible workaround. Perhaps such a silly line of code should be documented to prevent a rational person from removing such a ridiculous. The comment needs to state that setting a breakpoint will give different results and explain why. Sure wish I knew why. At least we found what Mr. Bean did prior to becoming a comedian. -- My handle should tell you enough about me. I am not an MVP, expert, guru, etc. but I do like to help. "Peter T" <peter_t@discussions wrote in message ... I get similar results but only when I start by stepping through with F8 Sub test() ' compare difference in debug results between ' step through with F8 & run with F5 Workbooks.Add For i = 1 To 16 Cells(i, 1).Font.ColorIndex = i Next For i = 1 To 16 ActiveWorkbook.Colors(i) = 255 Next For i = 1 To 16 ' should return : i 255 255 ' though stepping through returns: i default palette color(i) 255 Debug.Print i, Cells(i, 1).Font.Color, _ ActiveWorkbook.Colors(Cells(i, 1).Font.ColorIndex) Next Dim r As Range Set r = [a6] 'put a break on next line and put cursor over r.Font.Color x = r.Font.Color ' tip under cursor reads = 65536 (yellow) but in Locals r.font = 255 as expected End Sub I've spent considerable time working with the Excel palette and still don't fully understand it's deep inner workings. Appears to belong to the workbook's "windows" object, which itself is a rather odd thing. But where or how is the "default" palette stored & defined. Doing certain things with the palette can crash Excel (albeit in rare scenarios). I don't see how a dual palette can be used since changing the color of an index immediately changes the color throughout the workbook. One of those can't-be-possible-but-is things. With two windows, apply colours to cells in one window and see different colours from the "other" palette update in same cells in the other window. Switch windows and the drop down palette changes. Regards, Peter T "AnExpertNovice" wrote in message ... I don't see how a dual palette can be used since changing the color of an index immediately changes the color throughout the workbook. But, there is a lot I don't know and can't imagine! Now, this has become even more confusing the code below (much of it is redundant) was executed in two ways, each with similar but different results. First it was executed in the Immediate Window; one step at a time. Both ActiveWorkbook and ThisWorkbook were used. No change since the code effectively resides in the ActiveWorkbook. The results we 16711680 32 255 255 255 (ActiveSheet.Select performed here) 16711680 16711680 Color Test Then a new workbook was manually created, a module inserted, and the code was copied to a subroutine. The Workbooks.Add code was commented out. Before executing a second subroutine was created consisting of three lines of code Debug.Print ActiveSheet.Range("A1").Font.Color ActiveSheet.Select Debug.Print ActiveSheet.Range("A1").Font.Color These lines of code exist within the first subroutine so I would think the results would be the same. The results we First Routine, notice that the Font Color was reported as 255 (red) five times in a row, unlike in the Immediate Window. 16711680 255 32 255 255 255 (ActiveSheet.Select performed here) 255 255 Color Test Second Routine, oops. 16711680 255 (I'm sending the rest of my hair to Microsoft, but if they are not careful I will send them my first born later. ;) '=================== Start of code 'Start a new workbook being sure to start with the default colors and a known worksheet. Workbooks.Add Template:="Workbook" ActiveWorkbook.ResetColors Activeworkbook.Sheets(1).select Activeworkbook.Sheets(1).activate Activeworkbook.Sheets(1).Name = "Color Test" 'place the word Color in A1 ActiveSheet.Range("A1").value = "Color" 'Make the text of a size that color can be more readily seen. Activesheet.Range("A1").Font.Size = 14 Activesheet.Range("A1").Font.Bold = True 'Change the font color of A1 to the color in index 32. The font is now blue. Activesheet.Range("A1").Font.ColorIndex = 32 'Report the Font Color in the cell (FF0000, or 16711680; ie Blue) debug.Print Activesheet.Range("A1").Font.Color 'change the color of Index 32 to Red. The font is now red. ActiveWorkbook.colors(32) = &HFF 'Report the ColorIndex value. (32) debug.Print Activesheet.Range("A1").Font.ColorIndex 'Report the Color of the ColorIndex 32 (FF, ie Red) debug.Print ActiveWorkbook.Colors(32) 'Report the Font Color in the cell (FF, or 255; ie Red.... huh?) debug.Print Activesheet.Range("A1").Font.Color 'Report the Font Color in the cell again (FF, or 255; ) debug.Print Thisworkbook.WorkSheets("Color Test").Range("A1").Font.Color 'Now, select the ActiveSheet, just for fun Activesheet.select 'Report the Font Color in the cell again (FF0000, or 16711680; ) debug.Print Activesheet.Range("A1").Font.Color 'Ok, what is the font color now? debug.Print Thisworkbook.WorkSheets("Color Test").Range("A1").Font.Color 'Do a bit of testing debug.Print Thisworkbook.ActiveSheet.name '=================== End of code -- My handle should tell you enough about me. I am not an MVP, expert, guru, etc. but I do like to help. "Peter T" <peter_t@discussions wrote in message ... So what's the actual code you use. #32 in a default palette is 100% blue. It is possible to create a workbook that sustains two unique palettes concurrently, one default and one customized, each viewable in different windows of the same workbook. There's a bit of a knack to doing this (I always forget!) and easy to loose the dual palette. Perhaps something along these lines is occurring for you. Regards, Peter T "AnExpertNovice" wrote in message ... No, but good point. In this case the code resides in the current workbook so ActiveWorkbook and ThisWorkbook are the same. Thanks for the thought. A co-worker said he had a similar situation with Excel workbooks created by Business Objects. Opening the workbook allowed viewing the report but trying to print or do a print preview generated an error. Clicking on the worksheet tab "fixed" the problem. Essentially they are maunally doing an Activeworksheet.Select to work around the issue. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
selection.font.color returns wrong color; the first execution
If I interpret what you say correctly everything returns as expected when
you run the code normally. So I don't see the need to "workaround" by selecting the activesheet which is not something to do for no good reason. In normal use you can reliably return .font.color. Or in both normal & debug mode idx = cell.font.colorindex if idx 0 then colorvalue = cell.parent.parent.colors(idx) ' ie cell's Workbook.Colors(idx) ' else ' automatic/system black, most typically colorvalue = 0 end if Regards, Peter T "AnExpertNovice" wrote in message ... I found a reference and consolidated it into: First row from left to right: 1, 53, 52, 51, 49, 11, 55, 56 second row: 9, 46, 12, 10, 14, 5, 47, 16 third row: 3, 45, 43, 50, 42, 41, 13, 48 fourth row: 7, 44, 6, 4, 8, 33, 54, 15 fifth row: 38, 40, 36, 35, 34, 37, 39, 2 sixth row first default row for charts: 17, 18, 19, 20, 21, 22, 23, 24 seventh row. second default row for charts: 25, 26, 27, 28, 29, 30, 31, 32 [a6] was set to Index 6, which is the 4th row, 3rd column, which is yellow. Thus, you have shown the similar pattern of a change in values based on executing code and code executed after a break. Seemingly regardless of whether the break was a break point or an End statement. I modified your code slightly. ' Workbooks.Add ActiveWorkbook.ResetColors 'x = r.Font.Color Debug.Print ActiveSheet.Range("A6").Font.Color Debug.Print ActiveSheet.Range("A6").Font.Color '<== Add Breakpoint ActiveSheet.Select Debug.Print ActiveSheet.Range("A6").Font.Color The results are the same, of course. If the code is run non-stop then all three Debug.Prints display the value 255. If a breakpoint is placed on the second Debug.Print statement then the first returns 255, the second 65535, and the third 255. So, it does seem that adding an Activesheet.Select statment provides a possible workaround. Perhaps such a silly line of code should be documented to prevent a rational person from removing such a ridiculous. The comment needs to state that setting a breakpoint will give different results and explain why. Sure wish I knew why. At least we found what Mr. Bean did prior to becoming a comedian. -- My handle should tell you enough about me. I am not an MVP, expert, guru, etc. but I do like to help. "Peter T" <peter_t@discussions wrote in message ... I get similar results but only when I start by stepping through with F8 Sub test() ' compare difference in debug results between ' step through with F8 & run with F5 Workbooks.Add For i = 1 To 16 Cells(i, 1).Font.ColorIndex = i Next For i = 1 To 16 ActiveWorkbook.Colors(i) = 255 Next For i = 1 To 16 ' should return : i 255 255 ' though stepping through returns: i default palette color(i) 255 Debug.Print i, Cells(i, 1).Font.Color, _ ActiveWorkbook.Colors(Cells(i, 1).Font.ColorIndex) Next Dim r As Range Set r = [a6] 'put a break on next line and put cursor over r.Font.Color x = r.Font.Color ' tip under cursor reads = 65536 (yellow) but in Locals r.font = 255 as expected End Sub I've spent considerable time working with the Excel palette and still don't fully understand it's deep inner workings. Appears to belong to the workbook's "windows" object, which itself is a rather odd thing. But where or how is the "default" palette stored & defined. Doing certain things with the palette can crash Excel (albeit in rare scenarios). I don't see how a dual palette can be used since changing the color of an index immediately changes the color throughout the workbook. One of those can't-be-possible-but-is things. With two windows, apply colours to cells in one window and see different colours from the "other" palette update in same cells in the other window. Switch windows and the drop down palette changes. Regards, Peter T "AnExpertNovice" wrote in message ... I don't see how a dual palette can be used since changing the color of an index immediately changes the color throughout the workbook. But, there is a lot I don't know and can't imagine! Now, this has become even more confusing the code below (much of it is redundant) was executed in two ways, each with similar but different results. First it was executed in the Immediate Window; one step at a time. Both ActiveWorkbook and ThisWorkbook were used. No change since the code effectively resides in the ActiveWorkbook. The results we 16711680 32 255 255 255 (ActiveSheet.Select performed here) 16711680 16711680 Color Test Then a new workbook was manually created, a module inserted, and the code was copied to a subroutine. The Workbooks.Add code was commented out. Before executing a second subroutine was created consisting of three lines of code Debug.Print ActiveSheet.Range("A1").Font.Color ActiveSheet.Select Debug.Print ActiveSheet.Range("A1").Font.Color These lines of code exist within the first subroutine so I would think the results would be the same. The results we First Routine, notice that the Font Color was reported as 255 (red) five times in a row, unlike in the Immediate Window. 16711680 255 32 255 255 255 (ActiveSheet.Select performed here) 255 255 Color Test Second Routine, oops. 16711680 255 (I'm sending the rest of my hair to Microsoft, but if they are not careful I will send them my first born later. ;) '=================== Start of code 'Start a new workbook being sure to start with the default colors and a known worksheet. Workbooks.Add Template:="Workbook" ActiveWorkbook.ResetColors Activeworkbook.Sheets(1).select Activeworkbook.Sheets(1).activate Activeworkbook.Sheets(1).Name = "Color Test" 'place the word Color in A1 ActiveSheet.Range("A1").value = "Color" 'Make the text of a size that color can be more readily seen. Activesheet.Range("A1").Font.Size = 14 Activesheet.Range("A1").Font.Bold = True 'Change the font color of A1 to the color in index 32. The font is now blue. Activesheet.Range("A1").Font.ColorIndex = 32 'Report the Font Color in the cell (FF0000, or 16711680; ie Blue) debug.Print Activesheet.Range("A1").Font.Color 'change the color of Index 32 to Red. The font is now red. ActiveWorkbook.colors(32) = &HFF 'Report the ColorIndex value. (32) debug.Print Activesheet.Range("A1").Font.ColorIndex 'Report the Color of the ColorIndex 32 (FF, ie Red) debug.Print ActiveWorkbook.Colors(32) 'Report the Font Color in the cell (FF, or 255; ie Red.... huh?) debug.Print Activesheet.Range("A1").Font.Color 'Report the Font Color in the cell again (FF, or 255; ) debug.Print Thisworkbook.WorkSheets("Color Test").Range("A1").Font.Color 'Now, select the ActiveSheet, just for fun Activesheet.select 'Report the Font Color in the cell again (FF0000, or 16711680; ) debug.Print Activesheet.Range("A1").Font.Color 'Ok, what is the font color now? debug.Print Thisworkbook.WorkSheets("Color Test").Range("A1").Font.Color 'Do a bit of testing debug.Print Thisworkbook.ActiveSheet.name '=================== End of code -- My handle should tell you enough about me. I am not an MVP, expert, guru, etc. but I do like to help. "Peter T" <peter_t@discussions wrote in message ... So what's the actual code you use. #32 in a default palette is 100% blue. It is possible to create a workbook that sustains two unique palettes concurrently, one default and one customized, each viewable in different windows of the same workbook. There's a bit of a knack to doing this (I always forget!) and easy to loose the dual palette. Perhaps something along these lines is occurring for you. Regards, Peter T "AnExpertNovice" wrote in message ... No, but good point. In this case the code resides in the current workbook so ActiveWorkbook and ThisWorkbook are the same. Thanks for the thought. A co-worker said he had a similar situation with Excel workbooks created by Business Objects. Opening the workbook allowed viewing the report but trying to print or do a print preview generated an error. Clicking on the worksheet tab "fixed" the problem. Essentially they are maunally doing an Activeworksheet.Select to work around the issue. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
selection.font.color returns wrong color; the first execution
everything returns as expected when you run the code normally
Only if the code is always sets colors whenever they are to be tested, including events. Thanks to your help we have a better, although a very slightly slower, work around that always works. The "ActiveSheet.Select" work around fails if the code is interrupted between the selection and test and it may interfere with code working with multiple worksheets. The proper work around is find the color of the color index of the color. To find the Interior color of cell "A1" use: With ActiveSheet.Range("A1").Interior If .ColorIndex < 0 Then lngcolor = .Color Else lngcolor = ActiveWorkbook.Colors(.ColorIndex) End If End With To find the Font color used in cell "A1" use: 'Warning: This code assumes all characters within the cell use the same font color! With ActiveSheet.Range("A1").Font If .ColorIndex < 0 Then lngcolor = .Color Else lngcolor = ActiveWorkbook.Colors(.ColorIndex) End If End With Some code examples use the .Color directly and others that use .ColorIndex directly. Both have problems. ColorIndex is fine as long as it is not the Color that is important but the ColorIndex. ColorIndex 5 and 32 both default to Blue (&HFF0000) is one example. Color is fine as long as the ColorIndex has not been modified prior to the current execution. Thanks for hashing this out with me. PS. I normally define and set worksheet and workbook objects then use those in my code. This was not tested and may have some impact, although I"m sure the final solution will work without problems. -- My handle should tell you enough about me. I am not an MVP, expert, guru, etc. but I do like to help. "Peter T" <peter_t@discussions wrote in message ... If I interpret what you say correctly everything returns as expected when you run the code normally. So I don't see the need to "workaround" by selecting the activesheet which is not something to do for no good reason. In normal use you can reliably return .font.color. Or in both normal & debug mode idx = cell.font.colorindex if idx 0 then colorvalue = cell.parent.parent.colors(idx) ' ie cell's Workbook.Colors(idx) ' else ' automatic/system black, most typically colorvalue = 0 end if Regards, Peter T "AnExpertNovice" wrote in message ... I found a reference and consolidated it into: First row from left to right: 1, 53, 52, 51, 49, 11, 55, 56 second row: 9, 46, 12, 10, 14, 5, 47, 16 third row: 3, 45, 43, 50, 42, 41, 13, 48 fourth row: 7, 44, 6, 4, 8, 33, 54, 15 fifth row: 38, 40, 36, 35, 34, 37, 39, 2 sixth row first default row for charts: 17, 18, 19, 20, 21, 22, 23, 24 seventh row. second default row for charts: 25, 26, 27, 28, 29, 30, 31, 32 [a6] was set to Index 6, which is the 4th row, 3rd column, which is yellow. Thus, you have shown the similar pattern of a change in values based on executing code and code executed after a break. Seemingly regardless of whether the break was a break point or an End statement. I modified your code slightly. ' Workbooks.Add ActiveWorkbook.ResetColors 'x = r.Font.Color Debug.Print ActiveSheet.Range("A6").Font.Color Debug.Print ActiveSheet.Range("A6").Font.Color '<== Add Breakpoint ActiveSheet.Select Debug.Print ActiveSheet.Range("A6").Font.Color The results are the same, of course. If the code is run non-stop then all three Debug.Prints display the value 255. If a breakpoint is placed on the second Debug.Print statement then the first returns 255, the second 65535, and the third 255. So, it does seem that adding an Activesheet.Select statment provides a possible workaround. Perhaps such a silly line of code should be documented to prevent a rational person from removing such a ridiculous. The comment needs to state that setting a breakpoint will give different results and explain why. Sure wish I knew why. At least we found what Mr. Bean did prior to becoming a comedian. -- My handle should tell you enough about me. I am not an MVP, expert, guru, etc. but I do like to help. "Peter T" <peter_t@discussions wrote in message ... I get similar results but only when I start by stepping through with F8 Sub test() ' compare difference in debug results between ' step through with F8 & run with F5 Workbooks.Add For i = 1 To 16 Cells(i, 1).Font.ColorIndex = i Next For i = 1 To 16 ActiveWorkbook.Colors(i) = 255 Next For i = 1 To 16 ' should return : i 255 255 ' though stepping through returns: i default palette color(i) 255 Debug.Print i, Cells(i, 1).Font.Color, _ ActiveWorkbook.Colors(Cells(i, 1).Font.ColorIndex) Next Dim r As Range Set r = [a6] 'put a break on next line and put cursor over r.Font.Color x = r.Font.Color ' tip under cursor reads = 65536 (yellow) but in Locals r.font = 255 as expected End Sub I've spent considerable time working with the Excel palette and still don't fully understand it's deep inner workings. Appears to belong to the workbook's "windows" object, which itself is a rather odd thing. But where or how is the "default" palette stored & defined. Doing certain things with the palette can crash Excel (albeit in rare scenarios). I don't see how a dual palette can be used since changing the color of an index immediately changes the color throughout the workbook. One of those can't-be-possible-but-is things. With two windows, apply colours to cells in one window and see different colours from the "other" palette update in same cells in the other window. Switch windows and the drop down palette changes. Regards, Peter T "AnExpertNovice" wrote in message ... I don't see how a dual palette can be used since changing the color of an index immediately changes the color throughout the workbook. But, there is a lot I don't know and can't imagine! Now, this has become even more confusing the code below (much of it is redundant) was executed in two ways, each with similar but different results. First it was executed in the Immediate Window; one step at a time. Both ActiveWorkbook and ThisWorkbook were used. No change since the code effectively resides in the ActiveWorkbook. The results we 16711680 32 255 255 255 (ActiveSheet.Select performed here) 16711680 16711680 Color Test Then a new workbook was manually created, a module inserted, and the code was copied to a subroutine. The Workbooks.Add code was commented out. Before executing a second subroutine was created consisting of three lines of code Debug.Print ActiveSheet.Range("A1").Font.Color ActiveSheet.Select Debug.Print ActiveSheet.Range("A1").Font.Color These lines of code exist within the first subroutine so I would think the results would be the same. The results we First Routine, notice that the Font Color was reported as 255 (red) five times in a row, unlike in the Immediate Window. 16711680 255 32 255 255 255 (ActiveSheet.Select performed here) 255 255 Color Test Second Routine, oops. 16711680 255 (I'm sending the rest of my hair to Microsoft, but if they are not careful I will send them my first born later. ;) '=================== Start of code 'Start a new workbook being sure to start with the default colors and a known worksheet. Workbooks.Add Template:="Workbook" ActiveWorkbook.ResetColors Activeworkbook.Sheets(1).select Activeworkbook.Sheets(1).activate Activeworkbook.Sheets(1).Name = "Color Test" 'place the word Color in A1 ActiveSheet.Range("A1").value = "Color" 'Make the text of a size that color can be more readily seen. Activesheet.Range("A1").Font.Size = 14 Activesheet.Range("A1").Font.Bold = True 'Change the font color of A1 to the color in index 32. The font is now blue. Activesheet.Range("A1").Font.ColorIndex = 32 'Report the Font Color in the cell (FF0000, or 16711680; ie Blue) debug.Print Activesheet.Range("A1").Font.Color 'change the color of Index 32 to Red. The font is now red. ActiveWorkbook.colors(32) = &HFF 'Report the ColorIndex value. (32) debug.Print Activesheet.Range("A1").Font.ColorIndex 'Report the Color of the ColorIndex 32 (FF, ie Red) debug.Print ActiveWorkbook.Colors(32) 'Report the Font Color in the cell (FF, or 255; ie Red.... huh?) debug.Print Activesheet.Range("A1").Font.Color 'Report the Font Color in the cell again (FF, or 255; ) debug.Print Thisworkbook.WorkSheets("Color Test").Range("A1").Font.Color 'Now, select the ActiveSheet, just for fun Activesheet.select 'Report the Font Color in the cell again (FF0000, or 16711680; ) debug.Print Activesheet.Range("A1").Font.Color 'Ok, what is the font color now? debug.Print Thisworkbook.WorkSheets("Color Test").Range("A1").Font.Color 'Do a bit of testing debug.Print Thisworkbook.ActiveSheet.name '=================== End of code -- My handle should tell you enough about me. I am not an MVP, expert, guru, etc. but I do like to help. "Peter T" <peter_t@discussions wrote in message ... So what's the actual code you use. #32 in a default palette is 100% blue. It is possible to create a workbook that sustains two unique palettes concurrently, one default and one customized, each viewable in different windows of the same workbook. There's a bit of a knack to doing this (I always forget!) and easy to loose the dual palette. Perhaps something along these lines is occurring for you. Regards, Peter T "AnExpertNovice" wrote in message ... No, but good point. In this case the code resides in the current workbook so ActiveWorkbook and ThisWorkbook are the same. Thanks for the thought. A co-worker said he had a similar situation with Excel workbooks created by Business Objects. Opening the workbook allowed viewing the report but trying to print or do a print preview generated an error. Clicking on the worksheet tab "fixed" the problem. Essentially they are maunally doing an Activeworksheet.Select to work around the issue. |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
selection.font.color returns wrong color; the first execution
Glad you've got it working
'Warning: This code assumes all characters within the cell use the same font color! Dim vx as variant vx = .colorindex if isnull(vx) then ' it's mixed colours elseif vdx < 0 ' automatic else a palette colour end if Some code examples use the .Color directly and others that use .ColorIndex directly. Both have problems. ColorIndex is fine as long as it is not the Color that is important but the ColorIndex. ColorIndex 5 and 32 both default to Blue (&HFF0000) is one example. In a default palette there are 10 duplicates, in a customized who knows. But I don't see why you say it's a problem to return the colour. Color is fine as long as the ColorIndex has not been modified prior to the current execution. Unlike you, the problem discussed earlier only occurs for me in debug / step mode. Regards, Peter T "AnExpertNovice" wrote in message ... everything returns as expected when you run the code normally Only if the code is always sets colors whenever they are to be tested, including events. Thanks to your help we have a better, although a very slightly slower, work around that always works. The "ActiveSheet.Select" work around fails if the code is interrupted between the selection and test and it may interfere with code working with multiple worksheets. The proper work around is find the color of the color index of the color. To find the Interior color of cell "A1" use: With ActiveSheet.Range("A1").Interior If .ColorIndex < 0 Then lngcolor = .Color Else lngcolor = ActiveWorkbook.Colors(.ColorIndex) End If End With To find the Font color used in cell "A1" use: 'Warning: This code assumes all characters within the cell use the same font color! With ActiveSheet.Range("A1").Font If .ColorIndex < 0 Then lngcolor = .Color Else lngcolor = ActiveWorkbook.Colors(.ColorIndex) End If End With Some code examples use the .Color directly and others that use .ColorIndex directly. Both have problems. ColorIndex is fine as long as it is not the Color that is important but the ColorIndex. ColorIndex 5 and 32 both default to Blue (&HFF0000) is one example. Color is fine as long as the ColorIndex has not been modified prior to the current execution. Thanks for hashing this out with me. PS. I normally define and set worksheet and workbook objects then use those in my code. This was not tested and may have some impact, although I"m sure the final solution will work without problems. -- My handle should tell you enough about me. I am not an MVP, expert, guru, etc. but I do like to help. "Peter T" <peter_t@discussions wrote in message ... If I interpret what you say correctly everything returns as expected when you run the code normally. So I don't see the need to "workaround" by selecting the activesheet which is not something to do for no good reason. In normal use you can reliably return .font.color. Or in both normal & debug mode idx = cell.font.colorindex if idx 0 then colorvalue = cell.parent.parent.colors(idx) ' ie cell's Workbook.Colors(idx) ' else ' automatic/system black, most typically colorvalue = 0 end if Regards, Peter T "AnExpertNovice" wrote in message ... I found a reference and consolidated it into: First row from left to right: 1, 53, 52, 51, 49, 11, 55, 56 second row: 9, 46, 12, 10, 14, 5, 47, 16 third row: 3, 45, 43, 50, 42, 41, 13, 48 fourth row: 7, 44, 6, 4, 8, 33, 54, 15 fifth row: 38, 40, 36, 35, 34, 37, 39, 2 sixth row first default row for charts: 17, 18, 19, 20, 21, 22, 23, 24 seventh row. second default row for charts: 25, 26, 27, 28, 29, 30, 31, 32 [a6] was set to Index 6, which is the 4th row, 3rd column, which is yellow. Thus, you have shown the similar pattern of a change in values based on executing code and code executed after a break. Seemingly regardless of whether the break was a break point or an End statement. I modified your code slightly. ' Workbooks.Add ActiveWorkbook.ResetColors 'x = r.Font.Color Debug.Print ActiveSheet.Range("A6").Font.Color Debug.Print ActiveSheet.Range("A6").Font.Color '<== Add Breakpoint ActiveSheet.Select Debug.Print ActiveSheet.Range("A6").Font.Color The results are the same, of course. If the code is run non-stop then all three Debug.Prints display the value 255. If a breakpoint is placed on the second Debug.Print statement then the first returns 255, the second 65535, and the third 255. So, it does seem that adding an Activesheet.Select statment provides a possible workaround. Perhaps such a silly line of code should be documented to prevent a rational person from removing such a ridiculous. The comment needs to state that setting a breakpoint will give different results and explain why. Sure wish I knew why. At least we found what Mr. Bean did prior to becoming a comedian. -- My handle should tell you enough about me. I am not an MVP, expert, guru, etc. but I do like to help. "Peter T" <peter_t@discussions wrote in message ... I get similar results but only when I start by stepping through with F8 Sub test() ' compare difference in debug results between ' step through with F8 & run with F5 Workbooks.Add For i = 1 To 16 Cells(i, 1).Font.ColorIndex = i Next For i = 1 To 16 ActiveWorkbook.Colors(i) = 255 Next For i = 1 To 16 ' should return : i 255 255 ' though stepping through returns: i default palette color(i) 255 Debug.Print i, Cells(i, 1).Font.Color, _ ActiveWorkbook.Colors(Cells(i, 1).Font.ColorIndex) Next Dim r As Range Set r = [a6] 'put a break on next line and put cursor over r.Font.Color x = r.Font.Color ' tip under cursor reads = 65536 (yellow) but in Locals r.font = 255 as expected End Sub I've spent considerable time working with the Excel palette and still don't fully understand it's deep inner workings. Appears to belong to the workbook's "windows" object, which itself is a rather odd thing. But where or how is the "default" palette stored & defined. Doing certain things with the palette can crash Excel (albeit in rare scenarios). I don't see how a dual palette can be used since changing the color of an index immediately changes the color throughout the workbook. One of those can't-be-possible-but-is things. With two windows, apply colours to cells in one window and see different colours from the "other" palette update in same cells in the other window. Switch windows and the drop down palette changes. Regards, Peter T "AnExpertNovice" wrote in message ... I don't see how a dual palette can be used since changing the color of an index immediately changes the color throughout the workbook. But, there is a lot I don't know and can't imagine! Now, this has become even more confusing the code below (much of it is redundant) was executed in two ways, each with similar but different results. First it was executed in the Immediate Window; one step at a time. Both ActiveWorkbook and ThisWorkbook were used. No change since the code effectively resides in the ActiveWorkbook. The results we 16711680 32 255 255 255 (ActiveSheet.Select performed here) 16711680 16711680 Color Test Then a new workbook was manually created, a module inserted, and the code was copied to a subroutine. The Workbooks.Add code was commented out. Before executing a second subroutine was created consisting of three lines of code Debug.Print ActiveSheet.Range("A1").Font.Color ActiveSheet.Select Debug.Print ActiveSheet.Range("A1").Font.Color These lines of code exist within the first subroutine so I would think the results would be the same. The results we First Routine, notice that the Font Color was reported as 255 (red) five times in a row, unlike in the Immediate Window. 16711680 255 32 255 255 255 (ActiveSheet.Select performed here) 255 255 Color Test Second Routine, oops. 16711680 255 (I'm sending the rest of my hair to Microsoft, but if they are not careful I will send them my first born later. ;) '=================== Start of code 'Start a new workbook being sure to start with the default colors and a known worksheet. Workbooks.Add Template:="Workbook" ActiveWorkbook.ResetColors Activeworkbook.Sheets(1).select Activeworkbook.Sheets(1).activate Activeworkbook.Sheets(1).Name = "Color Test" 'place the word Color in A1 ActiveSheet.Range("A1").value = "Color" 'Make the text of a size that color can be more readily seen. Activesheet.Range("A1").Font.Size = 14 Activesheet.Range("A1").Font.Bold = True 'Change the font color of A1 to the color in index 32. The font is now blue. Activesheet.Range("A1").Font.ColorIndex = 32 'Report the Font Color in the cell (FF0000, or 16711680; ie Blue) debug.Print Activesheet.Range("A1").Font.Color 'change the color of Index 32 to Red. The font is now red. ActiveWorkbook.colors(32) = &HFF 'Report the ColorIndex value. (32) debug.Print Activesheet.Range("A1").Font.ColorIndex 'Report the Color of the ColorIndex 32 (FF, ie Red) debug.Print ActiveWorkbook.Colors(32) 'Report the Font Color in the cell (FF, or 255; ie Red.... huh?) debug.Print Activesheet.Range("A1").Font.Color 'Report the Font Color in the cell again (FF, or 255; ) debug.Print Thisworkbook.WorkSheets("Color Test").Range("A1").Font.Color 'Now, select the ActiveSheet, just for fun Activesheet.select 'Report the Font Color in the cell again (FF0000, or 16711680; ) debug.Print Activesheet.Range("A1").Font.Color 'Ok, what is the font color now? debug.Print Thisworkbook.WorkSheets("Color Test").Range("A1").Font.Color 'Do a bit of testing debug.Print Thisworkbook.ActiveSheet.name '=================== End of code -- My handle should tell you enough about me. I am not an MVP, expert, guru, etc. but I do like to help. "Peter T" <peter_t@discussions wrote in message ... So what's the actual code you use. #32 in a default palette is 100% blue. It is possible to create a workbook that sustains two unique palettes concurrently, one default and one customized, each viewable in different windows of the same workbook. There's a bit of a knack to doing this (I always forget!) and easy to loose the dual palette. Perhaps something along these lines is occurring for you. Regards, Peter T "AnExpertNovice" wrote in message ... No, but good point. In this case the code resides in the current workbook so ActiveWorkbook and ThisWorkbook are the same. Thanks for the thought. A co-worker said he had a similar situation with Excel workbooks created by Business Objects. Opening the workbook allowed viewing the report but trying to print or do a print preview generated an error. Clicking on the worksheet tab "fixed" the problem. Essentially they are maunally doing an Activeworksheet.Select to work around the issue. |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
selection.font.color returns wrong color; the first execution
The isnull is nice to know, thanks!
I don't see why you say it's a problem to return the colour. because the Font.color or interior.color can return either the default or actual color.... depending. Unlike you, the problem discussed earlier only occurs for me in debug / step mode. Use this code in a new workbook. Sub SetDuringExecution() ActiveWorkbook.ResetColors ActiveWorkbook.Colors(6) = &HFF ActiveSheet.Range("A1").Font.ColorIndex = 6 Debug.Print "------" Debug.Print ActiveSheet.Range("A1").Font.ColorIndex Debug.Print ActiveSheet.Range("A1").Font.Color Debug.Print ActiveWorkbook.Colors(ActiveSheet.Range("A1").Font .ColorIndex) End Sub Sub SetInAPriorExecution() Debug.Print "------" Debug.Print ActiveSheet.Range("A1").Font.ColorIndex Debug.Print ActiveSheet.Range("A1").Font.Color Debug.Print ActiveWorkbook.Colors(ActiveSheet.Range("A1").Font .ColorIndex) End Sub Execute the first routine (set during) The result should be. ------ 6 255 255 Execute the second routine (set in a prior) The result should be. ------ 6 255 255 This is what I think you are referring to. Now, toggle to the actual worksheet. You can do what you want to the worksheet, or do nothing. Now, execute the second routine again. The result should be. ------ 6 65535 255 Let me know if your results are different or the same. PLEASE! I'm using Excel 2002 on XP 2002 at work the code works as demonstrated at work. I am using Excel 2000 on Win 2K at home and believe it worked the same at home, but will retest with this exact code to be sure. I will only mention the results if they are different. 6 is, of course, the color index. Default is yellow as you know. The second number is either 255 (actual) or 65535 (default). This reports the font color. The third number is always the actual color. It reports the color of the color index. Thus, I suggest this is the proper code, once the negative and null values are handled. In my case, the value will never be null, but good to know. -- My handle should tell you enough about me. I am not an MVP, expert, guru, etc. but I do like to help. "Peter T" <peter_t@discussions wrote in message ... Glad you've got it working 'Warning: This code assumes all characters within the cell use the same font color! Dim vx as variant vx = .colorindex if isnull(vx) then ' it's mixed colours elseif vdx < 0 ' automatic else a palette colour end if Some code examples use the .Color directly and others that use ..ColorIndex directly. Both have problems. ColorIndex is fine as long as it is not the Color that is important but the ColorIndex. ColorIndex 5 and 32 both default to Blue (&HFF0000) is one example. In a default palette there are 10 duplicates, in a customized who knows. But I don't see why you say it's a problem to return the colour. Color is fine as long as the ColorIndex has not been modified prior to the current execution. Unlike you, the problem discussed earlier only occurs for me in debug / step mode. Regards, Peter T "AnExpertNovice" wrote in message ... everything returns as expected when you run the code normally Only if the code is always sets colors whenever they are to be tested, including events. Thanks to your help we have a better, although a very slightly slower, work around that always works. The "ActiveSheet.Select" work around fails if the code is interrupted between the selection and test and it may interfere with code working with multiple worksheets. The proper work around is find the color of the color index of the color. To find the Interior color of cell "A1" use: With ActiveSheet.Range("A1").Interior If .ColorIndex < 0 Then lngcolor = .Color Else lngcolor = ActiveWorkbook.Colors(.ColorIndex) End If End With To find the Font color used in cell "A1" use: 'Warning: This code assumes all characters within the cell use the same font color! With ActiveSheet.Range("A1").Font If .ColorIndex < 0 Then lngcolor = .Color Else lngcolor = ActiveWorkbook.Colors(.ColorIndex) End If End With Some code examples use the .Color directly and others that use ..ColorIndex directly. Both have problems. ColorIndex is fine as long as it is not the Color that is important but the ColorIndex. ColorIndex 5 and 32 both default to Blue (&HFF0000) is one example. Color is fine as long as the ColorIndex has not been modified prior to the current execution. Thanks for hashing this out with me. PS. I normally define and set worksheet and workbook objects then use those in my code. This was not tested and may have some impact, although I"m sure the final solution will work without problems. -- My handle should tell you enough about me. I am not an MVP, expert, guru, etc. but I do like to help. "Peter T" <peter_t@discussions wrote in message ... If I interpret what you say correctly everything returns as expected when you run the code normally. So I don't see the need to "workaround" by selecting the activesheet which is not something to do for no good reason. In normal use you can reliably return .font.color. Or in both normal & debug mode idx = cell.font.colorindex if idx 0 then colorvalue = cell.parent.parent.colors(idx) ' ie cell's Workbook.Colors(idx) ' else ' automatic/system black, most typically colorvalue = 0 end if Regards, Peter T "AnExpertNovice" wrote in message ... I found a reference and consolidated it into: First row from left to right: 1, 53, 52, 51, 49, 11, 55, 56 second row: 9, 46, 12, 10, 14, 5, 47, 16 third row: 3, 45, 43, 50, 42, 41, 13, 48 fourth row: 7, 44, 6, 4, 8, 33, 54, 15 fifth row: 38, 40, 36, 35, 34, 37, 39, 2 sixth row first default row for charts: 17, 18, 19, 20, 21, 22, 23, 24 seventh row. second default row for charts: 25, 26, 27, 28, 29, 30, 31, 32 [a6] was set to Index 6, which is the 4th row, 3rd column, which is yellow. Thus, you have shown the similar pattern of a change in values based on executing code and code executed after a break. Seemingly regardless of whether the break was a break point or an End statement. I modified your code slightly. ' Workbooks.Add ActiveWorkbook.ResetColors 'x = r.Font.Color Debug.Print ActiveSheet.Range("A6").Font.Color Debug.Print ActiveSheet.Range("A6").Font.Color '<== Add Breakpoint ActiveSheet.Select Debug.Print ActiveSheet.Range("A6").Font.Color The results are the same, of course. If the code is run non-stop then all three Debug.Prints display the value 255. If a breakpoint is placed on the second Debug.Print statement then the first returns 255, the second 65535, and the third 255. So, it does seem that adding an Activesheet.Select statment provides a possible workaround. Perhaps such a silly line of code should be documented to prevent a rational person from removing such a ridiculous. The comment needs to state that setting a breakpoint will give different results and explain why. Sure wish I knew why. At least we found what Mr. Bean did prior to becoming a comedian. -- My handle should tell you enough about me. I am not an MVP, expert, guru, etc. but I do like to help. "Peter T" <peter_t@discussions wrote in message ... I get similar results but only when I start by stepping through with F8 Sub test() ' compare difference in debug results between ' step through with F8 & run with F5 Workbooks.Add For i = 1 To 16 Cells(i, 1).Font.ColorIndex = i Next For i = 1 To 16 ActiveWorkbook.Colors(i) = 255 Next For i = 1 To 16 ' should return : i 255 255 ' though stepping through returns: i default palette color(i) 255 Debug.Print i, Cells(i, 1).Font.Color, _ ActiveWorkbook.Colors(Cells(i, 1).Font.ColorIndex) Next Dim r As Range Set r = [a6] 'put a break on next line and put cursor over r.Font.Color x = r.Font.Color ' tip under cursor reads = 65536 (yellow) but in Locals r.font = 255 as expected End Sub I've spent considerable time working with the Excel palette and still don't fully understand it's deep inner workings. Appears to belong to the workbook's "windows" object, which itself is a rather odd thing. But where or how is the "default" palette stored & defined. Doing certain things with the palette can crash Excel (albeit in rare scenarios). I don't see how a dual palette can be used since changing the color of an index immediately changes the color throughout the workbook. One of those can't-be-possible-but-is things. With two windows, apply colours to cells in one window and see different colours from the "other" palette update in same cells in the other window. Switch windows and the drop down palette changes. Regards, Peter T "AnExpertNovice" wrote in message ... I don't see how a dual palette can be used since changing the color of an index immediately changes the color throughout the workbook. But, there is a lot I don't know and can't imagine! Now, this has become even more confusing the code below (much of it is redundant) was executed in two ways, each with similar but different results. First it was executed in the Immediate Window; one step at a time. Both ActiveWorkbook and ThisWorkbook were used. No change since the code effectively resides in the ActiveWorkbook. The results we 16711680 32 255 255 255 (ActiveSheet.Select performed here) 16711680 16711680 Color Test Then a new workbook was manually created, a module inserted, and the code was copied to a subroutine. The Workbooks.Add code was commented out. Before executing a second subroutine was created consisting of three lines of code Debug.Print ActiveSheet.Range("A1").Font.Color ActiveSheet.Select Debug.Print ActiveSheet.Range("A1").Font.Color These lines of code exist within the first subroutine so I would think the results would be the same. The results we First Routine, notice that the Font Color was reported as 255 (red) five times in a row, unlike in the Immediate Window. 16711680 255 32 255 255 255 (ActiveSheet.Select performed here) 255 255 Color Test Second Routine, oops. 16711680 255 (I'm sending the rest of my hair to Microsoft, but if they are not careful I will send them my first born later. ;) '=================== Start of code 'Start a new workbook being sure to start with the default colors and a known worksheet. Workbooks.Add Template:="Workbook" ActiveWorkbook.ResetColors Activeworkbook.Sheets(1).select Activeworkbook.Sheets(1).activate Activeworkbook.Sheets(1).Name = "Color Test" 'place the word Color in A1 ActiveSheet.Range("A1").value = "Color" 'Make the text of a size that color can be more readily seen. Activesheet.Range("A1").Font.Size = 14 Activesheet.Range("A1").Font.Bold = True 'Change the font color of A1 to the color in index 32. The font is now blue. Activesheet.Range("A1").Font.ColorIndex = 32 'Report the Font Color in the cell (FF0000, or 16711680; ie Blue) debug.Print Activesheet.Range("A1").Font.Color 'change the color of Index 32 to Red. The font is now red. ActiveWorkbook.colors(32) = &HFF 'Report the ColorIndex value. (32) debug.Print Activesheet.Range("A1").Font.ColorIndex 'Report the Color of the ColorIndex 32 (FF, ie Red) debug.Print ActiveWorkbook.Colors(32) 'Report the Font Color in the cell (FF, or 255; ie Red.... huh?) debug.Print Activesheet.Range("A1").Font.Color 'Report the Font Color in the cell again (FF, or 255; ) debug.Print Thisworkbook.WorkSheets("Color Test").Range("A1").Font.Color 'Now, select the ActiveSheet, just for fun Activesheet.select 'Report the Font Color in the cell again (FF0000, or 16711680; ) debug.Print Activesheet.Range("A1").Font.Color 'Ok, what is the font color now? debug.Print Thisworkbook.WorkSheets("Color Test").Range("A1").Font.Color 'Do a bit of testing debug.Print Thisworkbook.ActiveSheet.name '=================== End of code -- My handle should tell you enough about me. I am not an MVP, expert, guru, etc. but I do like to help. "Peter T" <peter_t@discussions wrote in message ... So what's the actual code you use. #32 in a default palette is 100% blue. It is possible to create a workbook that sustains two unique palettes concurrently, one default and one customized, each viewable in different windows of the same workbook. There's a bit of a knack to doing this (I always forget!) and easy to loose the dual palette. Perhaps something along these lines is occurring for you. Regards, Peter T "AnExpertNovice" wrote in message ... No, but good point. In this case the code resides in the current workbook so ActiveWorkbook and ThisWorkbook are the same. Thanks for the thought. A co-worker said he had a similar situation with Excel workbooks created by Business Objects. Opening the workbook allowed viewing the report but trying to print or do a print preview generated an error. Clicking on the worksheet tab "fixed" the problem. Essentially they are maunally doing an Activeworksheet.Select to work around the issue. |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
selection.font.color returns wrong color; the first execution
Hmm - I see what you mean
To avoid any confusion I ran this in Excel with Alt-F8. No need to add a new wb and code can be in any wb. First customize #6 and apply #6 to A1 then comment those lines Sub Test3() Dim n(1 To 3, 1 To 3) As Long 'customize #6 one time only ActiveWorkbook.Colors(6) = 255 ' apply Font.ColorIndex first time only ActiveSheet.Range("A1").Font.ColorIndex = 6 With ActiveSheet.Range("A1").Font ' or .Interior ' apply Font.ColorIndex first time only, then comment ' .ColorIndex = 6 n(1, 1) = .ColorIndex n(2, 1) = .Color ' 65535 - wrong (unless format applied above) n(3, 1) = ActiveWorkbook.Colors(.ColorIndex) ' do something to "any" cell format - not necessarily A1 .ColorIndex = .ColorIndex ' ' .Bold = .Bold ' only if "With .Font" n(1, 2) = .ColorIndex n(2, 2) = .Color ' 255 - right n(3, 2) = ActiveWorkbook.Colors(.ColorIndex) [a1] = [a1] + 1 ' change a value n(1, 3) = .ColorIndex n(2, 3) = .Color ' 65535 - wrong again !! n(3, 3) = ActiveWorkbook.Colors(.ColorIndex) [b1:d3].Value = n End With End Sub Conclusion - return the colour of the colorindex. Or apply some format (even same format), but as Font can have mixed formats best to apply to .Interior. Regards, Peter T "AnExpertNovice" wrote in message ... The isnull is nice to know, thanks! I don't see why you say it's a problem to return the colour. because the Font.color or interior.color can return either the default or actual color.... depending. Unlike you, the problem discussed earlier only occurs for me in debug / step mode. Use this code in a new workbook. Sub SetDuringExecution() ActiveWorkbook.ResetColors ActiveWorkbook.Colors(6) = &HFF ActiveSheet.Range("A1").Font.ColorIndex = 6 Debug.Print "------" Debug.Print ActiveSheet.Range("A1").Font.ColorIndex Debug.Print ActiveSheet.Range("A1").Font.Color Debug.Print ActiveWorkbook.Colors(ActiveSheet.Range("A1").Font .ColorIndex) End Sub Sub SetInAPriorExecution() Debug.Print "------" Debug.Print ActiveSheet.Range("A1").Font.ColorIndex Debug.Print ActiveSheet.Range("A1").Font.Color Debug.Print ActiveWorkbook.Colors(ActiveSheet.Range("A1").Font .ColorIndex) End Sub Execute the first routine (set during) The result should be. ------ 6 255 255 Execute the second routine (set in a prior) The result should be. ------ 6 255 255 This is what I think you are referring to. Now, toggle to the actual worksheet. You can do what you want to the worksheet, or do nothing. Now, execute the second routine again. The result should be. ------ 6 65535 255 Let me know if your results are different or the same. PLEASE! I'm using Excel 2002 on XP 2002 at work the code works as demonstrated at work. I am using Excel 2000 on Win 2K at home and believe it worked the same at home, but will retest with this exact code to be sure. I will only mention the results if they are different. 6 is, of course, the color index. Default is yellow as you know. The second number is either 255 (actual) or 65535 (default). This reports the font color. The third number is always the actual color. It reports the color of the color index. Thus, I suggest this is the proper code, once the negative and null values are handled. In my case, the value will never be null, but good to know. -- My handle should tell you enough about me. I am not an MVP, expert, guru, etc. but I do like to help. "Peter T" <peter_t@discussions wrote in message ... Glad you've got it working 'Warning: This code assumes all characters within the cell use the same font color! Dim vx as variant vx = .colorindex if isnull(vx) then ' it's mixed colours elseif vdx < 0 ' automatic else a palette colour end if Some code examples use the .Color directly and others that use .ColorIndex directly. Both have problems. ColorIndex is fine as long as it is not the Color that is important but the ColorIndex. ColorIndex 5 and 32 both default to Blue (&HFF0000) is one example. In a default palette there are 10 duplicates, in a customized who knows. But I don't see why you say it's a problem to return the colour. Color is fine as long as the ColorIndex has not been modified prior to the current execution. Unlike you, the problem discussed earlier only occurs for me in debug / step mode. Regards, Peter T "AnExpertNovice" wrote in message ... everything returns as expected when you run the code normally Only if the code is always sets colors whenever they are to be tested, including events. Thanks to your help we have a better, although a very slightly slower, work around that always works. The "ActiveSheet.Select" work around fails if the code is interrupted between the selection and test and it may interfere with code working with multiple worksheets. The proper work around is find the color of the color index of the color. To find the Interior color of cell "A1" use: With ActiveSheet.Range("A1").Interior If .ColorIndex < 0 Then lngcolor = .Color Else lngcolor = ActiveWorkbook.Colors(.ColorIndex) End If End With To find the Font color used in cell "A1" use: 'Warning: This code assumes all characters within the cell use the same font color! With ActiveSheet.Range("A1").Font If .ColorIndex < 0 Then lngcolor = .Color Else lngcolor = ActiveWorkbook.Colors(.ColorIndex) End If End With Some code examples use the .Color directly and others that use .ColorIndex directly. Both have problems. ColorIndex is fine as long as it is not the Color that is important but the ColorIndex. ColorIndex 5 and 32 both default to Blue (&HFF0000) is one example. Color is fine as long as the ColorIndex has not been modified prior to the current execution. Thanks for hashing this out with me. PS. I normally define and set worksheet and workbook objects then use those in my code. This was not tested and may have some impact, although I"m sure the final solution will work without problems. -- My handle should tell you enough about me. I am not an MVP, expert, guru, etc. but I do like to help. "Peter T" <peter_t@discussions wrote in message ... If I interpret what you say correctly everything returns as expected when you run the code normally. So I don't see the need to "workaround" by selecting the activesheet which is not something to do for no good reason. In normal use you can reliably return .font.color. Or in both normal & debug mode idx = cell.font.colorindex if idx 0 then colorvalue = cell.parent.parent.colors(idx) ' ie cell's Workbook.Colors(idx) ' else ' automatic/system black, most typically colorvalue = 0 end if Regards, Peter T "AnExpertNovice" wrote in message ... I found a reference and consolidated it into: First row from left to right: 1, 53, 52, 51, 49, 11, 55, 56 second row: 9, 46, 12, 10, 14, 5, 47, 16 third row: 3, 45, 43, 50, 42, 41, 13, 48 fourth row: 7, 44, 6, 4, 8, 33, 54, 15 fifth row: 38, 40, 36, 35, 34, 37, 39, 2 sixth row first default row for charts: 17, 18, 19, 20, 21, 22, 23, 24 seventh row. second default row for charts: 25, 26, 27, 28, 29, 30, 31, 32 [a6] was set to Index 6, which is the 4th row, 3rd column, which is yellow. Thus, you have shown the similar pattern of a change in values based on executing code and code executed after a break. Seemingly regardless of whether the break was a break point or an End statement. I modified your code slightly. ' Workbooks.Add ActiveWorkbook.ResetColors 'x = r.Font.Color Debug.Print ActiveSheet.Range("A6").Font.Color Debug.Print ActiveSheet.Range("A6").Font.Color '<== Add Breakpoint ActiveSheet.Select Debug.Print ActiveSheet.Range("A6").Font.Color The results are the same, of course. If the code is run non-stop then all three Debug.Prints display the value 255. If a breakpoint is placed on the second Debug.Print statement then the first returns 255, the second 65535, and the third 255. So, it does seem that adding an Activesheet.Select statment provides a possible workaround. Perhaps such a silly line of code should be documented to prevent a rational person from removing such a ridiculous. The comment needs to state that setting a breakpoint will give different results and explain why. Sure wish I knew why. At least we found what Mr. Bean did prior to becoming a comedian. -- My handle should tell you enough about me. I am not an MVP, expert, guru, etc. but I do like to help. "Peter T" <peter_t@discussions wrote in message ... I get similar results but only when I start by stepping through with F8 Sub test() ' compare difference in debug results between ' step through with F8 & run with F5 Workbooks.Add For i = 1 To 16 Cells(i, 1).Font.ColorIndex = i Next For i = 1 To 16 ActiveWorkbook.Colors(i) = 255 Next For i = 1 To 16 ' should return : i 255 255 ' though stepping through returns: i default palette color(i) 255 Debug.Print i, Cells(i, 1).Font.Color, _ ActiveWorkbook.Colors(Cells(i, 1).Font.ColorIndex) Next Dim r As Range Set r = [a6] 'put a break on next line and put cursor over r.Font.Color x = r.Font.Color ' tip under cursor reads = 65536 (yellow) but in Locals r.font = 255 as expected End Sub I've spent considerable time working with the Excel palette and still don't fully understand it's deep inner workings. Appears to belong to the workbook's "windows" object, which itself is a rather odd thing. But where or how is the "default" palette stored & defined. Doing certain things with the palette can crash Excel (albeit in rare scenarios). I don't see how a dual palette can be used since changing the color of an index immediately changes the color throughout the workbook. One of those can't-be-possible-but-is things. With two windows, apply colours to cells in one window and see different colours from the "other" palette update in same cells in the other window. Switch windows and the drop down palette changes. Regards, Peter T "AnExpertNovice" wrote in message ... I don't see how a dual palette can be used since changing the color of an index immediately changes the color throughout the workbook. But, there is a lot I don't know and can't imagine! Now, this has become even more confusing the code below (much of it is redundant) was executed in two ways, each with similar but different results. First it was executed in the Immediate Window; one step at a time. Both ActiveWorkbook and ThisWorkbook were used. No change since the code effectively resides in the ActiveWorkbook. The results we 16711680 32 255 255 255 (ActiveSheet.Select performed here) 16711680 16711680 Color Test Then a new workbook was manually created, a module inserted, and the code was copied to a subroutine. The Workbooks.Add code was commented out. Before executing a second subroutine was created consisting of three lines of code Debug.Print ActiveSheet.Range("A1").Font.Color ActiveSheet.Select Debug.Print ActiveSheet.Range("A1").Font.Color These lines of code exist within the first subroutine so I would think the results would be the same. The results we First Routine, notice that the Font Color was reported as 255 (red) five times in a row, unlike in the Immediate Window. 16711680 255 32 255 255 255 (ActiveSheet.Select performed here) 255 255 Color Test Second Routine, oops. 16711680 255 (I'm sending the rest of my hair to Microsoft, but if they are not careful I will send them my first born later. ;) '=================== Start of code 'Start a new workbook being sure to start with the default colors and a known worksheet. Workbooks.Add Template:="Workbook" ActiveWorkbook.ResetColors Activeworkbook.Sheets(1).select Activeworkbook.Sheets(1).activate Activeworkbook.Sheets(1).Name = "Color Test" 'place the word Color in A1 ActiveSheet.Range("A1").value = "Color" 'Make the text of a size that color can be more readily seen. Activesheet.Range("A1").Font.Size = 14 Activesheet.Range("A1").Font.Bold = True 'Change the font color of A1 to the color in index 32. The font is now blue. Activesheet.Range("A1").Font.ColorIndex = 32 'Report the Font Color in the cell (FF0000, or 16711680; ie Blue) debug.Print Activesheet.Range("A1").Font.Color 'change the color of Index 32 to Red. The font is now red. ActiveWorkbook.colors(32) = &HFF 'Report the ColorIndex value. (32) debug.Print Activesheet.Range("A1").Font.ColorIndex 'Report the Color of the ColorIndex 32 (FF, ie Red) debug.Print ActiveWorkbook.Colors(32) 'Report the Font Color in the cell (FF, or 255; ie Red.... huh?) debug.Print Activesheet.Range("A1").Font.Color 'Report the Font Color in the cell again (FF, or 255; ) debug.Print Thisworkbook.WorkSheets("Color Test").Range("A1").Font.Color 'Now, select the ActiveSheet, just for fun Activesheet.select 'Report the Font Color in the cell again (FF0000, or 16711680; ) debug.Print Activesheet.Range("A1").Font.Color 'Ok, what is the font color now? debug.Print Thisworkbook.WorkSheets("Color Test").Range("A1").Font.Color 'Do a bit of testing debug.Print Thisworkbook.ActiveSheet.name '=================== End of code -- My handle should tell you enough about me. I am not an MVP, expert, guru, etc. but I do like to help. "Peter T" <peter_t@discussions wrote in message ... So what's the actual code you use. #32 in a default palette is 100% blue. It is possible to create a workbook that sustains two unique palettes concurrently, one default and one customized, each viewable in different windows of the same workbook. There's a bit of a knack to doing this (I always forget!) and easy to loose the dual palette. Perhaps something along these lines is occurring for you. Regards, Peter T "AnExpertNovice" wrote in message ... No, but good point. In this case the code resides in the current workbook so ActiveWorkbook and ThisWorkbook are the same. Thanks for the thought. A co-worker said he had a similar situation with Excel workbooks created by Business Objects. Opening the workbook allowed viewing the report but trying to print or do a print preview generated an error. Clicking on the worksheet tab "fixed" the problem. Essentially they are maunally doing an Activeworksheet.Select to work around the issue. |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
selection.font.color returns wrong color; the first execution
Well, I'm "glad" you could confirm this feature on your system. The problem
exists, as one would assume, in both Excel 2000 under Win 2K and 2002 under Win XP. Your tests were interesting because you were able to replicate with code what I was producing with manual actions. As for the conclusion Or apply some format (even same format), but as Font can have mixed formats best to apply to .Interior. This still breaks when code execution is stopped (debug.assert, stop, break point, and other errors) so determining the color of the color index appears to be the only decent solution. Take care. Thanks for the conversation. -- My handle should tell you enough about me. I am not an MVP, expert, guru, etc. but I do like to help. "Peter T" <peter_t@discussions wrote in message ... Hmm - I see what you mean To avoid any confusion I ran this in Excel with Alt-F8. No need to add a new wb and code can be in any wb. First customize #6 and apply #6 to A1 then comment those lines Sub Test3() Dim n(1 To 3, 1 To 3) As Long 'customize #6 one time only ActiveWorkbook.Colors(6) = 255 ' apply Font.ColorIndex first time only ActiveSheet.Range("A1").Font.ColorIndex = 6 With ActiveSheet.Range("A1").Font ' or .Interior ' apply Font.ColorIndex first time only, then comment ' .ColorIndex = 6 n(1, 1) = .ColorIndex n(2, 1) = .Color ' 65535 - wrong (unless format applied above) n(3, 1) = ActiveWorkbook.Colors(.ColorIndex) ' do something to "any" cell format - not necessarily A1 .ColorIndex = .ColorIndex ' ' .Bold = .Bold ' only if "With .Font" n(1, 2) = .ColorIndex n(2, 2) = .Color ' 255 - right n(3, 2) = ActiveWorkbook.Colors(.ColorIndex) [a1] = [a1] + 1 ' change a value n(1, 3) = .ColorIndex n(2, 3) = .Color ' 65535 - wrong again !! n(3, 3) = ActiveWorkbook.Colors(.ColorIndex) [b1:d3].Value = n End With End Sub Conclusion - return the colour of the colorindex. Or apply some format (even same format), but as Font can have mixed formats best to apply to .Interior. Regards, Peter T "AnExpertNovice" wrote in message ... The isnull is nice to know, thanks! I don't see why you say it's a problem to return the colour. because the Font.color or interior.color can return either the default or actual color.... depending. Unlike you, the problem discussed earlier only occurs for me in debug / step mode. Use this code in a new workbook. Sub SetDuringExecution() ActiveWorkbook.ResetColors ActiveWorkbook.Colors(6) = &HFF ActiveSheet.Range("A1").Font.ColorIndex = 6 Debug.Print "------" Debug.Print ActiveSheet.Range("A1").Font.ColorIndex Debug.Print ActiveSheet.Range("A1").Font.Color Debug.Print ActiveWorkbook.Colors(ActiveSheet.Range("A1").Font .ColorIndex) End Sub Sub SetInAPriorExecution() Debug.Print "------" Debug.Print ActiveSheet.Range("A1").Font.ColorIndex Debug.Print ActiveSheet.Range("A1").Font.Color Debug.Print ActiveWorkbook.Colors(ActiveSheet.Range("A1").Font .ColorIndex) End Sub Execute the first routine (set during) The result should be. ------ 6 255 255 Execute the second routine (set in a prior) The result should be. ------ 6 255 255 This is what I think you are referring to. Now, toggle to the actual worksheet. You can do what you want to the worksheet, or do nothing. Now, execute the second routine again. The result should be. ------ 6 65535 255 Let me know if your results are different or the same. PLEASE! I'm using Excel 2002 on XP 2002 at work the code works as demonstrated at work. I am using Excel 2000 on Win 2K at home and believe it worked the same at home, but will retest with this exact code to be sure. I will only mention the results if they are different. 6 is, of course, the color index. Default is yellow as you know. The second number is either 255 (actual) or 65535 (default). This reports the font color. The third number is always the actual color. It reports the color of the color index. Thus, I suggest this is the proper code, once the negative and null values are handled. In my case, the value will never be null, but good to know. -- My handle should tell you enough about me. I am not an MVP, expert, guru, etc. but I do like to help. "Peter T" <peter_t@discussions wrote in message ... Glad you've got it working 'Warning: This code assumes all characters within the cell use the same font color! Dim vx as variant vx = .colorindex if isnull(vx) then ' it's mixed colours elseif vdx < 0 ' automatic else a palette colour end if Some code examples use the .Color directly and others that use .ColorIndex directly. Both have problems. ColorIndex is fine as long as it is not the Color that is important but the ColorIndex. ColorIndex 5 and 32 both default to Blue (&HFF0000) is one example. In a default palette there are 10 duplicates, in a customized who knows. But I don't see why you say it's a problem to return the colour. Color is fine as long as the ColorIndex has not been modified prior to the current execution. Unlike you, the problem discussed earlier only occurs for me in debug / step mode. Regards, Peter T "AnExpertNovice" wrote in message ... everything returns as expected when you run the code normally Only if the code is always sets colors whenever they are to be tested, including events. Thanks to your help we have a better, although a very slightly slower, work around that always works. The "ActiveSheet.Select" work around fails if the code is interrupted between the selection and test and it may interfere with code working with multiple worksheets. The proper work around is find the color of the color index of the color. To find the Interior color of cell "A1" use: With ActiveSheet.Range("A1").Interior If .ColorIndex < 0 Then lngcolor = .Color Else lngcolor = ActiveWorkbook.Colors(.ColorIndex) End If End With To find the Font color used in cell "A1" use: 'Warning: This code assumes all characters within the cell use the same font color! With ActiveSheet.Range("A1").Font If .ColorIndex < 0 Then lngcolor = .Color Else lngcolor = ActiveWorkbook.Colors(.ColorIndex) End If End With Some code examples use the .Color directly and others that use .ColorIndex directly. Both have problems. ColorIndex is fine as long as it is not the Color that is important but the ColorIndex. ColorIndex 5 and 32 both default to Blue (&HFF0000) is one example. Color is fine as long as the ColorIndex has not been modified prior to the current execution. Thanks for hashing this out with me. PS. I normally define and set worksheet and workbook objects then use those in my code. This was not tested and may have some impact, although I"m sure the final solution will work without problems. -- My handle should tell you enough about me. I am not an MVP, expert, guru, etc. but I do like to help. "Peter T" <peter_t@discussions wrote in message ... If I interpret what you say correctly everything returns as expected when you run the code normally. So I don't see the need to "workaround" by selecting the activesheet which is not something to do for no good reason. In normal use you can reliably return .font.color. Or in both normal & debug mode idx = cell.font.colorindex if idx 0 then colorvalue = cell.parent.parent.colors(idx) ' ie cell's Workbook.Colors(idx) ' else ' automatic/system black, most typically colorvalue = 0 end if Regards, Peter T "AnExpertNovice" wrote in message ... I found a reference and consolidated it into: First row from left to right: 1, 53, 52, 51, 49, 11, 55, 56 second row: 9, 46, 12, 10, 14, 5, 47, 16 third row: 3, 45, 43, 50, 42, 41, 13, 48 fourth row: 7, 44, 6, 4, 8, 33, 54, 15 fifth row: 38, 40, 36, 35, 34, 37, 39, 2 sixth row first default row for charts: 17, 18, 19, 20, 21, 22, 23, 24 seventh row. second default row for charts: 25, 26, 27, 28, 29, 30, 31, 32 [a6] was set to Index 6, which is the 4th row, 3rd column, which is yellow. Thus, you have shown the similar pattern of a change in values based on executing code and code executed after a break. Seemingly regardless of whether the break was a break point or an End statement. I modified your code slightly. ' Workbooks.Add ActiveWorkbook.ResetColors 'x = r.Font.Color Debug.Print ActiveSheet.Range("A6").Font.Color Debug.Print ActiveSheet.Range("A6").Font.Color '<== Add Breakpoint ActiveSheet.Select Debug.Print ActiveSheet.Range("A6").Font.Color The results are the same, of course. If the code is run non-stop then all three Debug.Prints display the value 255. If a breakpoint is placed on the second Debug.Print statement then the first returns 255, the second 65535, and the third 255. So, it does seem that adding an Activesheet.Select statment provides a possible workaround. Perhaps such a silly line of code should be documented to prevent a rational person from removing such a ridiculous. The comment needs to state that setting a breakpoint will give different results and explain why. Sure wish I knew why. At least we found what Mr. Bean did prior to becoming a comedian. -- My handle should tell you enough about me. I am not an MVP, expert, guru, etc. but I do like to help. "Peter T" <peter_t@discussions wrote in message ... I get similar results but only when I start by stepping through with F8 Sub test() ' compare difference in debug results between ' step through with F8 & run with F5 Workbooks.Add For i = 1 To 16 Cells(i, 1).Font.ColorIndex = i Next For i = 1 To 16 ActiveWorkbook.Colors(i) = 255 Next For i = 1 To 16 ' should return : i 255 255 ' though stepping through returns: i default palette color(i) 255 Debug.Print i, Cells(i, 1).Font.Color, _ ActiveWorkbook.Colors(Cells(i, 1).Font.ColorIndex) Next Dim r As Range Set r = [a6] 'put a break on next line and put cursor over r.Font.Color x = r.Font.Color ' tip under cursor reads = 65536 (yellow) but in Locals r.font = 255 as expected End Sub I've spent considerable time working with the Excel palette and still don't fully understand it's deep inner workings. Appears to belong to the workbook's "windows" object, which itself is a rather odd thing. But where or how is the "default" palette stored & defined. Doing certain things with the palette can crash Excel (albeit in rare scenarios). I don't see how a dual palette can be used since changing the color of an index immediately changes the color throughout the workbook. One of those can't-be-possible-but-is things. With two windows, apply colours to cells in one window and see different colours from the "other" palette update in same cells in the other window. Switch windows and the drop down palette changes. Regards, Peter T "AnExpertNovice" wrote in message ... I don't see how a dual palette can be used since changing the color of an index immediately changes the color throughout the workbook. But, there is a lot I don't know and can't imagine! Now, this has become even more confusing the code below (much of it is redundant) was executed in two ways, each with similar but different results. First it was executed in the Immediate Window; one step at a time. Both ActiveWorkbook and ThisWorkbook were used. No change since the code effectively resides in the ActiveWorkbook. The results we 16711680 32 255 255 255 (ActiveSheet.Select performed here) 16711680 16711680 Color Test Then a new workbook was manually created, a module inserted, and the code was copied to a subroutine. The Workbooks.Add code was commented out. Before executing a second subroutine was created consisting of three lines of code Debug.Print ActiveSheet.Range("A1").Font.Color ActiveSheet.Select Debug.Print ActiveSheet.Range("A1").Font.Color These lines of code exist within the first subroutine so I would think the results would be the same. The results we First Routine, notice that the Font Color was reported as 255 (red) five times in a row, unlike in the Immediate Window. 16711680 255 32 255 255 255 (ActiveSheet.Select performed here) 255 255 Color Test Second Routine, oops. 16711680 255 (I'm sending the rest of my hair to Microsoft, but if they are not careful I will send them my first born later. ;) '=================== Start of code 'Start a new workbook being sure to start with the default colors and a known worksheet. Workbooks.Add Template:="Workbook" ActiveWorkbook.ResetColors Activeworkbook.Sheets(1).select Activeworkbook.Sheets(1).activate Activeworkbook.Sheets(1).Name = "Color Test" 'place the word Color in A1 ActiveSheet.Range("A1").value = "Color" 'Make the text of a size that color can be more readily seen. Activesheet.Range("A1").Font.Size = 14 Activesheet.Range("A1").Font.Bold = True 'Change the font color of A1 to the color in index 32. The font is now blue. Activesheet.Range("A1").Font.ColorIndex = 32 'Report the Font Color in the cell (FF0000, or 16711680; ie Blue) debug.Print Activesheet.Range("A1").Font.Color 'change the color of Index 32 to Red. The font is now red. ActiveWorkbook.colors(32) = &HFF 'Report the ColorIndex value. (32) debug.Print Activesheet.Range("A1").Font.ColorIndex 'Report the Color of the ColorIndex 32 (FF, ie Red) debug.Print ActiveWorkbook.Colors(32) 'Report the Font Color in the cell (FF, or 255; ie Red.... huh?) debug.Print Activesheet.Range("A1").Font.Color 'Report the Font Color in the cell again (FF, or 255; ) debug.Print Thisworkbook.WorkSheets("Color Test").Range("A1").Font.Color 'Now, select the ActiveSheet, just for fun Activesheet.select 'Report the Font Color in the cell again (FF0000, or 16711680; ) debug.Print Activesheet.Range("A1").Font.Color 'Ok, what is the font color now? debug.Print Thisworkbook.WorkSheets("Color Test").Range("A1").Font.Color 'Do a bit of testing debug.Print Thisworkbook.ActiveSheet.name '=================== End of code -- My handle should tell you enough about me. I am not an MVP, expert, guru, etc. but I do like to help. "Peter T" <peter_t@discussions wrote in message ... So what's the actual code you use. #32 in a default palette is 100% blue. It is possible to create a workbook that sustains two unique palettes concurrently, one default and one customized, each viewable in different windows of the same workbook. There's a bit of a knack to doing this (I always forget!) and easy to loose the dual palette. Perhaps something along these lines is occurring for you. Regards, Peter T "AnExpertNovice" wrote in message ... No, but good point. In this case the code resides in the current workbook so ActiveWorkbook and ThisWorkbook are the same. Thanks for the thought. A co-worker said he had a similar situation with Excel workbooks created by Business Objects. Opening the workbook allowed viewing the report but trying to print or do a print preview generated an error. Clicking on the worksheet tab "fixed" the problem. Essentially they are maunally doing an Activeworksheet.Select to work around the issue. |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
selection.font.color returns wrong color; the first execution
Actually I'm "glad" you persevered with the belief of your perceptions,
despite any hint I doubted (not intended) ! I think this deserves the status of "bug", albeit a mild one with an easy workaround. I hadn't noticed it before because I only return palette colours in objects that cannot accept an RGB format. I'm also surprised never to have come across mention of it in this ng or elsewhere, which is not to say it isn't documented. I don't know of any "effective" means to report bugs. I've tried in the past with much more serious issues regarding the palette & VBA. Regards, Peter T "AnExpertNovice" wrote in message ... Well, I'm "glad" you could confirm this feature on your system. The problem exists, as one would assume, in both Excel 2000 under Win 2K and 2002 under Win XP. Your tests were interesting because you were able to replicate with code what I was producing with manual actions. As for the conclusion Or apply some format (even same format), but as Font can have mixed formats best to apply to .Interior. This still breaks when code execution is stopped (debug.assert, stop, break point, and other errors) so determining the color of the color index appears to be the only decent solution. Take care. Thanks for the conversation. -- My handle should tell you enough about me. I am not an MVP, expert, guru, etc. but I do like to help. <snip |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Checkbox to change background color, font color and remove/ add bo | Excel Discussion (Misc queries) | |||
fill color and font color icons do not work in excel | Excel Discussion (Misc queries) | |||
How to change the default Border, Font Color, and Cell Color | Excel Discussion (Misc queries) | |||
Default Border, Font Color, and Cell Background Color | Excel Discussion (Misc queries) | |||
My fill color and font color do not work in Excel Std Edition 2003 | Excel Discussion (Misc queries) |