Keep y-axis aligned

I have two area charts. One is above the other on the page.

I want to keep their respective y-axis lined up with one another but I
cannot figure out how to do this.

The data that the charts show is unknown to me and can change so I
can't just set the fonts on the axis because at times the labels on the
y axis may be a single digit and at other times it may be three or
more.

Is there any ways to set the size allowed for axis labels in pixels or
some other measurement?

Any help would be appreciated.

James

There's no native way to achieve this. Excel forces you to set the
overall plot area width, including all the axis decorations, and you
have to let the inner dimensions go wherever they go. This is the
opposite of what any user wants, but that's Excel.

I've done this with code, and it's not particularly reliable, though
it's usually within a pixel or so. I've also done this by hiding the
Excel axes and inserting my own using a dummy series to provide the
tickmarks and labels, but this is an awful lot of work to set up.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______


James Martin wrote:

> I have two area charts. One is above the other on the page.
>
> I want to keep their respective y-axis lined up with one another but I
> cannot figure out how to do this.
>
> The data that the charts show is unknown to me and can change so I
> can't just set the fonts on the axis because at times the labels on the
> y axis may be a single digit and at other times it may be three or
> more.
>
> Is there any ways to set the size allowed for axis labels in pixels or
> some other measurement?
>
> Any help would be appreciated.
>
> James
>

Hi,

the following example is not perfect, but might help you.
(run this macro from the Excel window, not from the visual basic editor)

Declare Function GetDeviceCaps Lib "gdi32" ( _
ByVal hdc As Long, ByVal nIndex As Long) As Long
Declare Function GetDC Lib "user32" (ByVal hwnd As Long) As Long
Declare Function ReleaseDC Lib "user32" ( _
ByVal hwnd As Long, ByVal hdc As Long) As Long

Private Const LOGPIXELSX = 88
Private Const LOGPIXELSY = 90


Sub Test_SetPlotAreaPosX()
Dim left_pt As Single, width_pt As Single
Dim a As Variant

'the left position and width of the plot area (in points)
left_pt = 50
width_pt = 200

'set the position in the 1st chart object.
ActiveSheet.ChartObjects(1).Activate
a = SetPlotAreaPosX(left_pt, width_pt)

'output the result to the immediate window in the visual basic editor.
Debug.Print "1: Left=" & a(0) & ", Width=" & a(1)

'set the position in the 2nd chart object.
ActiveSheet.ChartObjects(2).Activate
a = SetPlotAreaPosX(left_pt, width_pt)
Debug.Print "2: Left=" & a(0) & ", Width=" & a(1)

End Sub


Function SetPlotAreaPosX(ByVal left_pt As Single, _
ByVal width_pt As Single) As Variant
Dim hdc As Long, px As Long
Dim xleft As Single, xright As Single
Dim cur_left As Single, cur_right As Single

hdc = GetDC(0)
px = GetDeviceCaps(hdc, LOGPIXELSX)
ReleaseDC 0, hdc

xleft = Int((left_pt - 1) * px / 72 + 0.5) * 72 / px + 1
xright = Int((left_pt + width_pt - 1) * px / 72 + 0.5) * 72 / px + 1

If ActiveWindow.Type = xlChartInPlace Then ActiveChart.ShowWindow = True
ActiveChart.ChartArea.Select
ActiveChart.PlotArea.Select

cur_left = ExecuteExcel4Macro("GET.CHART.ITEM(1,1)")
cur_right = ExecuteExcel4Macro("GET.CHART.ITEM(1,5)")

If xleft > cur_left Then ExecuteExcel4Macro _
"FORMAT.SIZE(" & (cur_right - xleft) & ")"
ExecuteExcel4Macro "FORMAT.MOVE(" & xleft & ")"
ExecuteExcel4Macro "FORMAT.MOVE(" & xleft & ")"
cur_left = ExecuteExcel4Macro("GET.CHART.ITEM(1,1)")
cur_right = ExecuteExcel4Macro("GET.CHART.ITEM(1,5)")

ExecuteExcel4Macro "FORMAT.SIZE(" & (xright - xleft) & ")"
ExecuteExcel4Macro "FORMAT.SIZE(" & (xright - xleft) & ")"
cur_left = ExecuteExcel4Macro("GET.CHART.ITEM(1,1)")
cur_right = ExecuteExcel4Macro("GET.CHART.ITEM(1,5)")

If Abs(xleft - cur_left) > 0.01 Then
ExecuteExcel4Macro "FORMAT.MOVE(" & xleft & ")"
cur_left = ExecuteExcel4Macro("GET.CHART.ITEM(1,1)")
cur_right = ExecuteExcel4Macro("GET.CHART.ITEM(1,5)")
If Abs(xleft - cur_left) > 72 / px - 0.01 Then
ExecuteExcel4Macro "FORMAT.MOVE(" & _
(xleft + (xleft - cur_left) / 2) & ")"
cur_left = ExecuteExcel4Macro("GET.CHART.ITEM(1,1)")
cur_right = ExecuteExcel4Macro("GET.CHART.ITEM(1,5)")
End If
End If

If ActiveWindow.Type = xlChartAsWindow Then ActiveWindow.Visible = False
SetPlotAreaPosX = Array(cur_left, cur_right - cur_left)
End Function

--
HTH,

okaizawa


James Martin wrote:
> I have two area charts. One is above the other on the page.
>
> I want to keep their respective y-axis lined up with one another but I
> cannot figure out how to do this.
>
> The data that the charts show is unknown to me and can change so I
> can't just set the fonts on the axis because at times the labels on the
> y axis may be a single digit and at other times it may be three or
> more.
>
> Is there any ways to set the size allowed for axis labels in pixels or
> some other measurement?
>
> Any help would be appreciated.
>
> James
>