Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Secondary Axis Match Primary Axis Gridlines | Charts and Charting in Excel | |||
Pivot Table, Line Chart with Secondary Y axis, Page Field selection destroys Y2 axis | Charts and Charting in Excel | |||
can't change how data on 2nd axis is being displayed | Charts and Charting in Excel | |||
How do I make the Y axis in an excel chart the category axis? | Charts and Charting in Excel | |||
Second X axis at top of chart? | Charts and Charting in Excel |