There are other approaches to creating a dial/speedometer chart. Here are
links to a couple of step-by-step tutorials on creating them:

http://www.brainbell.com/tutorials/m...eter_Chart.htm

http://www.andypope.info/charts.htm

Also, here is a link to charting guru Jon Peltier's site, where he explains
why he thinks this kind of chart is ineffective:
http://peltiertech.com/Excel/Charts/SpeedometerXP.html

I hope these give you a workable alternative solution.

Hutch

"Pat Russell" wrote:

Hi Tom,

Thanks for the reply. I did try using IncrementLeft but the code listing I
used was just an example - the actual code allows the user to add any number
of arcs into the dial, so I don't know in advance how much adjustment to make
to the arc, and with say, 10 arcs in the dial, it might be the 4th and 8th
that are out. Because of where the handles are on arcs, I can't reliably use
"pin the Left of the next arc on the Left+Width of the previous arc" either.
I've also tried making sure it's not some kind of number-rounding problem too.

Because all the arcs have the same control points and radius etc. they
should all centre at the same point, and the fact that adjusting the start
and end angles of the arcs causes movement in the x,y location makes me think
it has to be a bug of some kind, because the two things should be unrelated
(I think). Especially since just repeating the same line continually moves it
to the right.

It's frustrating because it's almost not noticeable.... only it is ;-)


"Tom Hutchins" wrote:

I see the problem but don't know what causes it. If it happens consistently,
you could correct for it using the IncrementLeft method. You can add code
like this after the End With line for any of the segments:

myArc.Select
Selection.ShapeRange.IncrementLeft -0.5

-0.5 means to move half a point to the left. Positive numbers will move it
to the right. I got the best results with your example by moving the first
segment to the right 0.5, the second segment to the left 0.5, and the third
segment to the right 0.2 points. Hopefully one of the MVPs can give us both a
real explanation and solution, not just my clumsy workaround.

Hope this helps,

Hutch

"Pat Russell" wrote:

Hi,

I'm trying to create a dial-type control for an Excel-based report. The
shape I'm after is basically the top half of a pie chart, and I'll add
numbers, needles etc.

I'm using msoShapeArc to create specific zones for the dial e.g.
green-amber-red, which will be a 0-100% scale. It's almost working, but using
the .Adjustment(1) and (2) settings to set the angles is shifting the arcs
out of line, and I can't figure out why! The code is below - any help greatly
appreciated.

To see the problem exaggerated, repeat the line ".Adjustments(1) = 144" in
the second segment and watch the arc shift right each time. For some reason
it's not all of the arcs, just the some (the 2nd in this axample).

Thanks,

Pat.

Sub DrawDial()

Dim myArc As Shape

For Each myArc In ActiveSheet.Shapes
myArc.Delete
Next myArc

'draw first segment
Set myArc = ActiveSheet.Shapes.AddShape(msoShapeArc, 200, 100, 100, 100)
With myArc
.Adjustments(1) = 180
.Adjustments(2) = 144
.Fill.Visible = msoTrue
.Fill.Transparency = 0.3
.Fill.ForeColor.SchemeColor = 11
End With

'draw second segment
Set myArc = ActiveSheet.Shapes.AddShape(msoShapeArc, 200, 100, 100, 100)
With myArc
'THIS NEXT LNE SHIFTS THE ARC RIGHT BY 1 POINT!
.Adjustments(1) = 144
.Adjustments(2) = 70
.Fill.Visible = msoTrue
.Fill.Transparency = 0.3
.Fill.ForeColor.SchemeColor = 53
End With

'draw third segment
Set myArc = ActiveSheet.Shapes.AddShape(msoShapeArc, 200, 100, 100, 100)
With myArc
.Adjustments(1) = 70
.Adjustments(2) = 0
.Fill.Visible = msoTrue
.Fill.Transparency = 0.3
.Fill.ForeColor.SchemeColor = 10
End With

End Sub