Sleeper: Any way to fix the scale ( inches / mm / cm ) of chart or excel sheet ?

[senthilr]

I use VBA to create XY-scatter graph on a seperate chart sheet and plot data points. I also create a set of text boxes on both left and right side of the graph. But, on the right side of the graph, the "left" position of the text boxes are not uniform ( even though I pass same constant value as left point og each text box ). The text boxes are misaligned automatically.

Can any of you give me a solution for this ???

[Killian]

Maybe you could post the code you're using?
I've done a similar thing before with:


ActiveChart.Shapes("TextBoxLeft").Left = _
ActiveChart.PlotArea.Left - (ActiveChart.Shapes("TextBoxLeft").Width \ 2)

This will align the centre of the label with the y-axis.

Note that plotarea.left starts at the left edge of the axis tick mark labels so you may have to adjust for this (I think when I did this I switched them off, positioned the label and then switched them on again)

[senthilr]

I have placed the code below ( probably a verly large portion to give the full picture of the application :
In this function, based on some values, I create multiple chart sheets, and in each sheet I create an XY scatter graph, plot values, and then create a set of text boxes to the left and right of the chart.



While CrashLst2(nCntSecond) <> ""
'Create a text box for Crash code of current second crash
If nCntPos = 0 Then
nTop1 = 9
nLeft = 70
Else
nTop1 = nTop1 + ((nCntMain + 1) * 20)
nLeft = 85
End If
With cs.TextBoxes.Add(0, nTop1, 45, 18)
.Select
.AutoSize = True
.Font.FontStyle = "Bold"
.Font.Size = 10
.text = Trim(CrashLst2(nCntSecond))
.ShapeRange.Fill.ForeColor.SchemeColor = 9
.ShapeRange.Fill.Solid
End With
'Create a text box for information of current second crash
With cs.TextBoxes.Add(nLeft, nTop1, 65, 18)
.Select
.AutoSize = True
.Font.FontStyle = "Bold"
.Font.Size = 10
.text = Trim(sCrashInfo2(nCntSecond))
.ShapeRange.Fill.ForeColor.SchemeColor = 9
.ShapeRange.Fill.Solid
End With
nCntPos = nCntPos + 1
nCntMain = 0
' Loop through the main crahs list
While CrashLst1(nCntMain) <> ""
' Create a text box for crash code of current main crash
If nCntPos = 1 Then
nTop2 = nTop1 + 19
If nChartCnt > 0 Then
nLeft = 560
Else
nLeft = 600
End If
If nSimCnt > 0 Then
nLeft = 560
End If
Else
If nCntPos = 2 Then
nTop2 = nTop2 + 20
Else
nTop2 = nTop2 + 15
End If
nLeft = 560
End If
With cs.TextBoxes.Add(0, nTop2, 65, 9)
.Select
.AutoSize = True
.text = CrashLst1(nCntMain)
.Font.Size = 10
.ShapeRange.Fill.ForeColor.SchemeColor = 9
.ShapeRange.Fill.Solid
End With
' Create a text box for information of current main crash
With cs.TextBoxes.Add(nLeft, nTop2, 85, 9)
.Select
.AutoSize = True
.text = sCrashInfo1(nCntMain)
.Font.Size = 10
.ShapeRange.Fill.ForeColor.SchemeColor = 9
.ShapeRange.Fill.Solid
End With
' After this, I add data through series collection for the chart.