Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
MarkersColor: wrong error behavior
Hi all,
As a requital I submit a macro that some of technical Excel user may find useful. A snag, however, appers on the place marked by the remarks. Error handling requests in this case obviously some other trick I know nothing of. Many thanks Petr Sub MarkerColor() 'Sub changes the colors of individual markers in the selected series 'of x-y chart into the colors of the fonts of parent cell v a l u e s. 'It keeps the marker interior the way as defined for the whole series: 'either empty, or–this time-of uniform color. If, however, the background 'of any value cell is light gray, the interior of corresponding 'marker changes into its opposite, i.e. if the series has been declared 'as marker full then such a marker turns to empty and vice versa. 'If the background is medium gray, the marker disappears. 'The aim is to identify individual markers or their groups within 'one complete series on a x-y chart, without having to decompose 'the parent range into subranges. Dim SP As Points, W As Range Dim ErrMsg As String, SPF As String, Rng As String Dim I As Long, N As Long, PosComma As Long, ICI As Long, FCI As Long Dim MarkerIsEmpty As Boolean Const LightGray = 15, MediumGray = 48 ErrMsg = "No series has been selected" On Error GoTo ErrExit Set SP = Selection.Points MarkerIsEmpty = Selection.MarkerBackgroundColorIndex = xlNone N = SP.Count SPF = SP.Parent.Formula I = 3 Do I = I + 1 Rng$ = Right(SPF, I) Loop Until Left(Rng, 1) = "!" Rng = Right(Rng, Len(Rng) - 1) PosComma = Application.WorksheetFunction.Search(",", Rng) Rng$ = Left$(Rng, PosComma - 1) Set W = Range(Rng) For I = 1 To N FCI = W.Cells(I).Font.ColorIndex On Error GoTo Skip 'Here, if the I-th marker is missing (one of co-ordinate 'cells is empty or it is outside x or y chart scales) an error should occure and such a marker should be skipped SP(I).MarkerForegroundColorIndex = FCI 'It works fine, but only once. If there are more such cases, the error 'appers invariably at the second of them as the collapse #1004 WHY? '("Can't set the property...) ICI = W.Cells(I).Interior.ColorIndex If ICI = LightGray Xor MarkerIsEmpty Then SP(I).MarkerBackgroundColorIndex = xlNone ElseIf ICI = MediumGray Then SP(I).MarkerForegroundColorIndex = xlNone SP(I).MarkerBackgroundColorIndex = xlNone Else SP(I).MarkerBackgroundColorIndex = FCI End If Skip: On Error GoTo 0 Next I Exit Sub ErrExit: MsgBox ErrMsg$ On Error GoTo 0 End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
MarkersColor: wrong error behavior
Something I don't quite understand going on here -
If your series or individual point(s) has/have no markers, somehow applying marker colours makes markers visible. Although theoretically they still don't exist!! Start with a line without markers and try this both at the beginning and again at the end of your code: Msgbox Selection.MarkerStyle I get -4142 (xlMarkerStyleNone) before and after applying colours. Perhaps ensure markers exist: With selection If .Markerstyle = -4142 then .markerstyle = xlAutomatic End with Or similar in the loop With SP(I) Re your medium grey problem - what's the the colour of the plot background? By default normally colorindex 15, namely medium grey. BTW your colour constants rely on the palette colours 15 & 48 being uncustomized. Regards, Sandy -----Original Message----- Hi all, As a requital I submit a macro that some of technical Excel user may find useful. A snag, however, appers on the place marked by the remarks. Error handling requests in this case obviously some other trick I know nothing of. Many thanks Petr Sub MarkerColor() 'Sub changes the colors of individual markers in the selected series 'of x-y chart into the colors of the fonts of parent cell v a l u e s. 'It keeps the marker interior the way as defined for the whole series: 'either empty, or–this time-of uniform color. If, however, the background 'of any value cell is light gray, the interior of corresponding 'marker changes into its opposite, i.e. if the series has been declared 'as marker full then such a marker turns to empty and vice versa. 'If the background is medium gray, the marker disappears. 'The aim is to identify individual markers or their groups within 'one complete series on a x-y chart, without having to decompose 'the parent range into subranges. Dim SP As Points, W As Range Dim ErrMsg As String, SPF As String, Rng As String Dim I As Long, N As Long, PosComma As Long, ICI As Long, FCI As Long Dim MarkerIsEmpty As Boolean Const LightGray = 15, MediumGray = 48 ErrMsg = "No series has been selected" On Error GoTo ErrExit Set SP = Selection.Points MarkerIsEmpty = Selection.MarkerBackgroundColorIndex = xlNone N = SP.Count SPF = SP.Parent.Formula I = 3 Do I = I + 1 Rng$ = Right(SPF, I) Loop Until Left(Rng, 1) = "!" Rng = Right(Rng, Len(Rng) - 1) PosComma = Application.WorksheetFunction.Search(",", Rng) Rng$ = Left$(Rng, PosComma - 1) Set W = Range(Rng) For I = 1 To N FCI = W.Cells(I).Font.ColorIndex On Error GoTo Skip 'Here, if the I-th marker is missing (one of co-ordinate 'cells is empty or it is outside x or y chart scales) an error should occure and such a marker should be skipped SP(I).MarkerForegroundColorIndex = FCI 'It works fine, but only once. If there are more such cases, the error 'appers invariably at the second of them as the collapse #1004 WHY? '("Can't set the property...) ICI = W.Cells(I).Interior.ColorIndex If ICI = LightGray Xor MarkerIsEmpty Then SP(I).MarkerBackgroundColorIndex = xlNone ElseIf ICI = MediumGray Then SP(I).MarkerForegroundColorIndex = xlNone SP(I).MarkerBackgroundColorIndex = xlNone Else SP(I).MarkerBackgroundColorIndex = FCI End If Skip: On Error GoTo 0 Next I Exit Sub ErrExit: MsgBox ErrMsg$ On Error GoTo 0 End Sub . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
MarkersColor: wrong error behavior
Dear Sandy,
you turned out the only one who showed the interest. You must be deservedly punished, so allow me please to exploit you for a "beta-test". I hope the Sub worked for you if the number of markers in the series was complete. Substantially, there are two ways to distinct points: color and empty/full difference. Both ways are included, only the description may be difficult to understand without presenting an example. Both shades of gray were taken from the default palette. In fact, shading the background of the cells with these two lightest hues is quite natural. The background of the default x-y chart is nasty, just as many of its other features. If you would find any other objection as for the normal function, tell me please. But, what is decisive, I cannot do without quitting the absenting markers. SP(I) in such a case hasn't any property which could be judged previously, the only way is handling the error. That is the only cue, because it refuses to act twice. If even you would find the Sub useful, then it would be worth trying further. Sincerely Petr -----Original Message----- Something I don't quite understand going on here - If your series or individual point(s) has/have no markers, somehow applying marker colours makes markers visible. Although theoretically they still don't exist!! Start with a line without markers and try this both at the beginning and again at the end of your code: Msgbox Selection.MarkerStyle I get -4142 (xlMarkerStyleNone) before and after applying colours. Perhaps ensure markers exist: With selection If .Markerstyle = -4142 then .markerstyle = xlAutomatic End with -----Original Message----- Hi all, As a requital I submit a macro that some of technical Excel user may find useful. A snag, however, appers on the place marked by the remarks. Error handling requests in this case obviously some other trick I know nothing of. Many thanks Petr Sub MarkerColor() 'Sub changes the colors of individual markers in the selected series 'of x-y chart into the colors of the fonts of parent cell v a l u e s. 'It keeps the marker interior the way as defined for the whole series: 'either empty, or–this time-of uniform color. If, however, the background 'of any value cell is light gray, the interior of corresponding 'marker changes into its opposite, i.e. if the series has been declared 'as marker full then such a marker turns to empty and vice versa. 'If the background is medium gray, the marker disappears. 'The aim is to identify individual markers or their groups within 'one complete series on a x-y chart, without having to decompose 'the parent range into subranges. Dim SP As Points, W As Range Dim ErrMsg As String, SPF As String, Rng As String Dim I As Long, N As Long, PosComma As Long, ICI As Long, FCI As Long Dim MarkerIsEmpty As Boolean Const LightGray = 15, MediumGray = 48 ErrMsg = "No series has been selected" On Error GoTo ErrExit Set SP = Selection.Points MarkerIsEmpty = Selection.MarkerBackgroundColorIndex = xlNone N = SP.Count SPF = SP.Parent.Formula I = 3 Do I = I + 1 Rng$ = Right(SPF, I) Loop Until Left(Rng, 1) = "!" Rng = Right(Rng, Len(Rng) - 1) PosComma = Application.WorksheetFunction.Search(",", Rng) Rng$ = Left$(Rng, PosComma - 1) Set W = Range(Rng) For I = 1 To N FCI = W.Cells(I).Font.ColorIndex On Error GoTo Skip 'Here, if the I-th marker is missing (one of co- ordinate 'cells is empty or it is outside x or y chart scales) an error should occure and such a marker should be skipped SP(I).MarkerForegroundColorIndex = FCI 'It works fine, but only once. If there are more such cases, the error 'appers invariably at the second of them as the collapse #1004 WHY? '("Can't set the property...) ICI = W.Cells(I).Interior.ColorIndex If ICI = LightGray Xor MarkerIsEmpty Then SP(I).MarkerBackgroundColorIndex = xlNone ElseIf ICI = MediumGray Then SP(I).MarkerForegroundColorIndex = xlNone SP(I).MarkerBackgroundColorIndex = xlNone Else SP(I).MarkerBackgroundColorIndex = FCI End If Skip: On Error GoTo 0 Next I Exit Sub ErrExit: MsgBox ErrMsg$ On Error GoTo 0 End Sub . . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
MarkersColor: wrong error behavior
Hi Petr,
I didn't pick up on your 2nd unhandled error yesterday. (Maybe not possible to reset the same error handler in a loop?) However this seems to work for me: Instead of error handling within the loop, before the loop use - On Error Resume Next and move "On Error Goto 0" after the loop. Might also need to include something like: If err.number < 0 then err.clear goto XX 'a line just before Next end if (but this wasn't necessary in my limited checking) A few thoughts by way of return punishment: 1. You could reference cell fill colour matching the plot with - LightGray = ActiveChart.PlotArea.Interior.ColorIndex 2. I think it's worth checking the series markers are not xlNone, if not set xlautomatic. Also set any individual not-existent point marker same as the series markers. 3. I understand the code but not the purpose of "MarkerIsEmpty" which relates to your comments concerning "marker full" etc. (probably me missing something obvious). 4. Not all marker styles show Background colours (maybe not relevant). Regards, Sandy -----Original Message----- Dear Sandy, you turned out the only one who showed the interest. You must be deservedly punished, so allow me please to exploit you for a "beta-test". I hope the Sub worked for you if the number of markers in the series was complete. Substantially, there are two ways to distinct points: color and empty/full difference. Both ways are included, only the description may be difficult to understand without presenting an example. Both shades of gray were taken from the default palette. In fact, shading the background of the cells with these two lightest hues is quite natural. The background of the default x-y chart is nasty, just as many of its other features. If you would find any other objection as for the normal function, tell me please. But, what is decisive, I cannot do without quitting the absenting markers. SP(I) in such a case hasn't any property which could be judged previously, the only way is handling the error. That is the only cue, because it refuses to act twice. If even you would find the Sub useful, then it would be worth trying further. Sincerely Petr -----Original Message----- Something I don't quite understand going on here - If your series or individual point(s) has/have no markers, somehow applying marker colours makes markers visible. Although theoretically they still don't exist!! Start with a line without markers and try this both at the beginning and again at the end of your code: Msgbox Selection.MarkerStyle I get -4142 (xlMarkerStyleNone) before and after applying colours. Perhaps ensure markers exist: With selection If .Markerstyle = -4142 then .markerstyle = xlAutomatic End with -----Original Message----- Hi all, As a requital I submit a macro that some of technical Excel user may find useful. A snag, however, appers on the place marked by the remarks. Error handling requests in this case obviously some other trick I know nothing of. Many thanks Petr Sub MarkerColor() 'Sub changes the colors of individual markers in the selected series 'of x-y chart into the colors of the fonts of parent cell v a l u e s. 'It keeps the marker interior the way as defined for the whole series: 'either empty, or–this time-of uniform color. If, however, the background 'of any value cell is light gray, the interior of corresponding 'marker changes into its opposite, i.e. if the series has been declared 'as marker full then such a marker turns to empty and vice versa. 'If the background is medium gray, the marker disappears. 'The aim is to identify individual markers or their groups within 'one complete series on a x-y chart, without having to decompose 'the parent range into subranges. Dim SP As Points, W As Range Dim ErrMsg As String, SPF As String, Rng As String Dim I As Long, N As Long, PosComma As Long, ICI As Long, FCI As Long Dim MarkerIsEmpty As Boolean Const LightGray = 15, MediumGray = 48 ErrMsg = "No series has been selected" On Error GoTo ErrExit Set SP = Selection.Points MarkerIsEmpty = Selection.MarkerBackgroundColorIndex = xlNone N = SP.Count SPF = SP.Parent.Formula I = 3 Do I = I + 1 Rng$ = Right(SPF, I) Loop Until Left(Rng, 1) = "!" Rng = Right(Rng, Len(Rng) - 1) PosComma = Application.WorksheetFunction.Search(",", Rng) Rng$ = Left$(Rng, PosComma - 1) Set W = Range(Rng) For I = 1 To N FCI = W.Cells(I).Font.ColorIndex On Error GoTo Skip 'Here, if the I-th marker is missing (one of co- ordinate 'cells is empty or it is outside x or y chart scales) an error should occure and such a marker should be skipped SP(I).MarkerForegroundColorIndex = FCI 'It works fine, but only once. If there are more such cases, the error 'appers invariably at the second of them as the collapse #1004 WHY? '("Can't set the property...) ICI = W.Cells(I).Interior.ColorIndex If ICI = LightGray Xor MarkerIsEmpty Then SP(I).MarkerBackgroundColorIndex = xlNone ElseIf ICI = MediumGray Then SP(I).MarkerForegroundColorIndex = xlNone SP(I).MarkerBackgroundColorIndex = xlNone Else SP(I).MarkerBackgroundColorIndex = FCI End If Skip: On Error GoTo 0 Next I Exit Sub ErrExit: MsgBox ErrMsg$ On Error GoTo 0 End Sub . . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do i add an error message if someone enters wrong data | Excel Discussion (Misc queries) | |||
Error Message :A value used in the formula is of the wrong data ty | Excel Worksheet Functions | |||
Wrong amount or calculation error? | Excel Discussion (Misc queries) | |||
Run time error 1004 - what have I done wrong? | Excel Discussion (Misc queries) | |||
How to get a cell to error if the wrong figue is entered | Excel Discussion (Misc queries) |