XY scatter, select item in list to flag point

[Christine]

I have created an XY scatter graph and included code to show a data label for
each point in a text box. I have included a drop down box to list all
possible data labels. I want to be able to select one item from the list,
and for the relevant point to then change colour.
I know I could do this using multiple series and using the drop down to
specify the second series, but this interferes with the first macro.
Any ideas?

 

[Tushar Mehta]

Two questions.

First, why do you use textboxes to add data labels? It can, and
should, be done with Excel data labels.

Second, even if you must stay with code to add data labels, there's no
reason it should not play nice with a 2nd series. Just write the code
so that it works with the first, i.e., main, series.

On Wed, 6 Aug 2008 02:20:00 -0700, Christine

I have created an XY scatter graph and included code to show a data
label for
each point in a text box. I have included a drop down box to list all
possible data labels. I want to be able to select one item from the
list,
and for the relevant point to then change colour.
I know I could do this using multiple series and using the drop down
to
specify the second series, but this interferes with the first macro.
Any ideas?

Regards,

Tushar Mehta
Microsoft MVP Excel 2000-2008
www.tushar-mehta.com
Tutorials and add-ins for Excel, PowerPoint, and other products

 

[Christine]

Textbox: there are over a hundred points, it's easier to see the label if
it's placed in a text box in one place out of the graph, and includes the
coordinates.
Code: it shows false readings for the second series which is unsatisfactory
for sharing with others so either need to start again or find a more
interesting solution.
Christine

 

[PBezucha]

Christine,
Consider once more your idea with textboxes and list dropdowns. After all,
if you list texts to the labels in the original sheet, you would be able to
control easily not only their contents but even the color, font etc. If you
have already worked hard to create a macro, you can also make another one,
which links the texts and properties (including color) of the items of the
“labels†range to those of the labels themselves, especially if they count to
hundred. The overall subroutine, you can certainly put together, that creates
regular labels can then include following snip

..... Ser was defined as arbitrary of your series
..... RngLabels is the (adjacent) range of the label texts

Ser.HasDataLabels = True
For I = 1 To Ser.Points.Count
If Not IsEmpty(RngLabels(I)) Then
Ser.Points(I).DataLabel.Text = RngLabels(I).Text
Ser.Points(I).DataLabel.Characters.Font.ColorIndex = _
RngLabels(I).Characters.Font.ColorIndex
Else
Ser.Points(I).DataLabel.Text = ""
End If
Next I

The subroutine may be promptly repeated at every change you made in a labels
range item.
Each label is movable just like a textbox, so you could and must solve the
throng. This is really difficult to automatize.
Regards