In my opinion the only way to achieve this is using Shapes through VBA, but I think it's a hell of a job to position it correctly.... I'll give it a quick look.
Could you show the sample worksheet?
Sub Chart_In_Worksheet(X As Double, Y As Double, Z As Double)
Dim mb As Workbook
Dim ms As Worksheet
Dim mCh As ChartObject
Dim mCA As ChartArea
Dim mPA As PlotArea
Dim diameter As Double
Dim mShape As Shape
Dim myX As Double, myY As Double, myZ As Double
Dim xScalar As Double, yScalar As Double, DiaScalar As Double
Dim DeltaX As Double, DeltaY As Double, xAxis0 As Double, xAxis1 As Double, yAxis0 As Double, yAxis1 As Double
Set mb = ThisWorkbook
Set ms = Worksheets("Sheet1")
Set mCh = ms.ChartObjects(1)
Set mCA = mCh.Chart.ChartArea
Set mPA = mCh.Chart.PlotArea
xAxis0 = mCh.Chart.Axes(xlCategory).MinimumScale
xAxis1 = mCh.Chart.Axes(xlCategory).MaximumScale
yAxis0 = mCh.Chart.Axes(xlValue).MinimumScale
yAxis1 = mCh.Chart.Axes(xlValue).MaximumScale
DeltaX = xAxis1 - xAxis0
DeltaY = yAxis1 - yAxis0
xScalar = DeltaX / mPA.InsideWidth
yScalar = DeltaY / mPA.InsideHeight
'Trim Chart to be perfectly square
DiaScalar = xScalar * 0.5 + yScalar * 0.5
myX = X / xScalar
myY = mPA.InsideHeight - Y / yScalar
myD = Z / DiaScalar
Set mShape = mCh.Chart.Shapes.AddShape(msoShapeOval, mPA.InsideLeft - myD / 2 + myX, mPA.InsideTop - myD / 2 + myY, myD, myD)
mShape.Fill.ForeColor.SchemeColor = 10
mShape.Fill.Transparency = 0.75
End Sub
Sub Chart_on_Own_Sheet(X As Double, Y As Double, Z As Double)
Dim mb As Workbook
Dim mCh As Chart
Dim mCA As ChartArea
Dim mPA As PlotArea
Dim diameter As Double
Dim mShape As Shape
Dim myX As Double, myY As Double, myZ As Double
Dim xScalar As Double, yScalar As Double, DiaScalar As Double
Dim DeltaX As Double, DeltaY As Double, xAxis0 As Double, xAxis1 As Double, yAxis0 As Double, yAxis1 As Double
Set mb = ThisWorkbook
Set mCh = Sheets("Circles")
Set mCA = mCh.ChartArea
Set mPA = mCh.PlotArea
xAxis0 = mCh.Axes(xlCategory).MinimumScale
xAxis1 = mCh.Axes(xlCategory).MaximumScale
yAxis0 = mCh.Axes(xlValue).MinimumScale
yAxis1 = mCh.Axes(xlValue).MaximumScale
DeltaX = xAxis1 - xAxis0
DeltaY = yAxis1 - yAxis0
xScalar = DeltaX / mPA.InsideWidth
yScalar = DeltaY / mPA.InsideHeight
'Trim Chart to be perfectly square
DiaScalar = xScalar * 0.5 + yScalar * 0.5
myX = X / xScalar
myY = mPA.InsideHeight - Y / yScalar
myD = Z / DiaScalar
Set mShape = mCh.Shapes.AddShape(msoShapeOval, mPA.InsideLeft - myD / 2 + myX, mPA.InsideTop - myD / 2 + myY, myD, myD)
mShape.Fill.ForeColor.SchemeColor = 10
mShape.Fill.Transparency = 0.75
End Sub
Title | # Comments | Views | Activity |
---|---|---|---|
MS Excel "--" in Formula What is it doing? | 3 | 12 | |
Excel | 6 | 18 | |
Relative link_location when pasting a =HYPERLINK() formula to a different cell in Excel | 7 | 17 | |
Excel Array formula issues | 4 | 9 |
Join the community of 500,000 technology professionals and ask your questions.
Connect with top rated Experts
16 Experts available now in Live!