Adding shapes to Chart Plot Area via VBA...

M

Matty

Well-known Member
Joined
Feb 17, 2007
Messages
3,717
Hi All,

I would like to add a square shape to a Chart Plot Area starting at where the vertical and horizontal axes meet. If my data was static then this would be a simple job, but the data is dynamic (and so are the axes) and therefore I need the square to grow and shrink depending on what is shown.

Is it possible to use some code to do this?

Thanks,

Matty
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Sort by date Sort by votes
Grow and shrink in what way? Do you get any VBA code if you record a macro while adding the shape manually?
 
Upvote 0
Andrew Poulsom said:
Grow and shrink in what way? Do you get any VBA code if you record a macro while adding the shape manually?
Click to expand...

Thanks for your interest.

The shape needs to be locked to values on the vertical and horizontal axes, so that if the value on these axes moves, the shape grows and shrinks accordingly.

I would normally combine charts to do this (an area one to give me the square I need), but you can't combine charts wihen using a Bubble chart.

Hope this helps explain what I'm after. I'll also have a play with the recorder to see if this yields anything.

Cheers,


Matty
 
Upvote 0
Hi Matty

You can also use worksheet or chart events to detect the change in the source data and adjust the shape.
 
Upvote 0
Upvote 0
Matty said:
That sounds promising, but how would we get Excel to know how big the shape needs to be?
Click to expand...

Check the properties of the Axis object.

You have the usual Left, Top, Width, Height that allow you to to know exactly the size and position of the axes in the chart.
 
Upvote 0
pgc01 said:
Check the properties of the Axis object.

You have the usual Left, Top, Width, Height that allow you to to know exactly the size and position of the axes in the chart.
Click to expand...

Hi pgc01,

I've had a look and recorded a few examples, but I'm struggling to get the shape to do the following:

  • Postion the bottom left of the rectangle at the intersection of the x and y axes.
  • Link the rectangle's height to wherever the value 20 exists on the y axis.
  • Link the rectangle's width to wherever 0 exists on the x axis (my Chart has negative and positive values on the x axis).

Could you nudge me in the right direction, please?


Thanks,

Matty
 
Last edited:
Upvote 0
Hi again

What's the chart type?
For many chart types, another way that will draw the rectangle is simply to add a series with 5 points (the corners + the first corner)

For ex., add a series with

(0,0)
(0,1)
(1,1)
(1,0)
(0,0)

This line will draw a rectangle that starts where the the axes meet, like you said you want.
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question
Adding Multiple Vertical Lines on Horizontal Bar Chart
Replies
1
Views
174
NdNoviceHlp
NdNoviceHlp
T
  • Question
Adding Shaded Area in Pivot Chart
Replies
3
Views
400
Jon Peltier
J
N
  • Question
2-step Dynamic Ranges and Charts
Replies
0
Views
123
Nick Loughton
N
B
  • Question
Prevent Line from Continuing Point-to-Point (Non-continuous) On Scatter Plot in VBA
Replies
1
Views
448
Jon Peltier
J
D
  • Solved
Text Boxes - How to populate them without overlapping, bumping in to each other
Replies
3
Views
777
dcoker
D

Forum statistics

Threads
1,214,516
Messages
6,119,976
Members
448,934
Latest member
audette89

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top