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

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

.

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

.

.

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

.

.

.