how to add a text box to a chart position it ?

**77anders** · 03-17-2009, 06:47 AM

Hi,
How can I with a macro create text boxes and place them in a chart?
TO each textbox there should be an arrow that points towards a certain Y value on the y-axis. I've tried versions of the code below, But I don't know how to link the y-axis vaule with the position of the arrow and text box.
Any suggestions?

Please Login or Register  to view this content.

**Andy Pope** · 03-17-2009, 08:27 AM

Can you post example chart and describe what you are trying to do.

Can you not use data labels to display text?

**77anders** · 03-17-2009, 08:59 AM

I would like to create something like this (See attached file). The lines in the schart will chage after a macro is finished. THe text boxes has to be created and filled in with values from variables in the overall macro (min max values etc).

**Andy Pope** · 03-17-2009, 09:37 AM

This uses an additional xy series to display data labels.
The data label text is linked to cells which contain all the information to be displayed.

If you need leader lines you can add another series to create those.

If you still need to add and position the textboxes via code then this may help.
http://www.andypope.info/vba/Axislabel_textboxes.htm

You need to workout the plotareas dimensions in relation to the axis scale and then calculate the new position of the textbox given a X and or Y value at which to place it.