Insert object at graph's point of intersection in VBA

[dmurphy1991]

Ok, so i haven't been able to find anything like this anywhere. I'm going to have a pareto curve, and basically i want to highlight the tail of this curve (last 20%) using just an insertect rectagle shape with a shading. however my main question is can i have this object automatically inserted at the curve's intersection and the 80% line on the vertical axis?

Thanks

 

[Worf]

Hi
See if this example is useful:

Sub Pareto()
Dim rect As Shape, h%, w%, L%, hl As Shape, p As PlotArea, c As Chart
Set c = ActiveSheet.ChartObjects("Chart9").Chart
Set p = c.PlotArea
h = p.InsideHeight
w = p.InsideWidth
L = p.InsideLeft
Set rect = c.Shapes.AddTextbox(1, L, 0.8 * h, w, 0.2 * h)   ' bottom part of chart
rect.Fill.ForeColor.RGB = RGB(44, 123, 56)
rect.Fill.Transparency = 0.7
Set hl = c.Shapes.AddLine(L, 0.2 * h, w, 0.2 * h)           ' horizontal line
hl.Line.DashStyle = msoLineLongDash
hl.Line.ForeColor.RGB = RGB(203, 145, 54)
End Sub

 

[dmurphy1991]

Unfortunately this appears to have done nothing, but doesn't present any errors, i ensured that the active sheets if the on with the chart on it and named the 'chart 9' object as Chart 1 as per my sheet and rest of my code but it hasn't seemingly changed anything?

 

[Worf]

What Excel version are you using? That code worked with Excel 2007, but when testing with 2003 it generates a visible line but an invisible box...

 

[dmurphy1991]

Hi, sorry after fiddling with graphs and objects for the majority of the day in vba i was able to implement your code and got it to work, however the highlighted box isn't quite where i want it to be. I'm ideally after the top right red box in the following diagram, to illustrate the 'pareto tail' but with it only highlighting the 80-100% range of the Y axis (below is close)

 

[Worf]

This version lets you do the box positioning:

Sub UserBox()
Dim b As Shape, bx!, ex!, by!, ey!, c As Chart, iw%, ih%
Set c = ActiveSheet.ChartObjects("Chart8").Chart


bx = 0.6    ' begin at 60% of x axis
ex = 0.8    ' end at 80% of x axis
by = 0.7    ' begin at 70% of y axis
ey = 0.9    ' end at 90% of y axis


iw = c.PlotArea.InsideWidth
ih = c.PlotArea.InsideHeight
Set b = c.Shapes.AddTextbox(1, c.PlotArea.InsideLeft + bx * iw, c.PlotArea.InsideTop + (1 - ey) * ih, _
(ex - bx) * iw, (ey - by) * ih)
b.Fill.ForeColor.RGB = RGB(213, 34, 12)
b.Fill.Transparency = 0.7
End Sub

 

[dmurphy1991]

Wow, excellent thanks, can i ask, are the ! and % signs within the DIM statement, are the to specify something in particular? haven't come across them before

 

[dmurphy1991]

Wow, absolutely excellent thank you, works absolutely fine, going to determine my own start and end points based on my data now. I understand the principle of the code, but cani just ask...

The ! and % do they signify something significant? Haven't come across them before

Thanks again

 

[Worf]

Dim i% is equivalent to Dim i as integer

Dim s! is equivalent to Dim s as single

I personally prefer the shorter versions...