Microsoft Office Tutorials and References
In Depth Information
Sizing and Aligning Charts
The charts plot the data in the last row. Because the number of rows varies, the
last row must be determined dynamically. The last row of the worksheet is
determined by using the End method. This is similar to activating the last cell in a column
and then pressing End, followed by Up Arrow.
Next, a ChartObject is inserted and a new series is created by using the
NewSeries method. Subsequent statements assign the data range (the Values
property) and the category labels range (the Xvalues property). Note that the category
labels range is in the same range on each sheet, so this range does not have to be
determined by the code. The next statement changes the color of the columns.
The next series of statements applies various types of adjustments on the chart.
Finally, the ChtTop variable is updated, ready to be used for the next chart’s
positioning. Notice that screen updating is turned off at the beginning of the macro. On
slower systems, having this off may speed up the macro a bit.
Sizing and Aligning Charts
When you work with several embedded charts, you may prefer that the charts are
all the same size and aligned. The following macro does the job.
Sub SizeAndAlignCharts()
Dim ChtWidth As Long, ChtHeight As Long
Dim TopPosition As Long, LeftPosition As Long
Dim ChtObj As ChartObject
If ActiveChart Is Nothing Then Exit Sub
‘Get size of active chart
ChtWidth = ActiveChart.Parent.Width
ChtHeight = ActiveChart.Parent.Height
TopPosition = ActiveChart.Parent.Top
LeftPosition = ActiveChart.Parent.Left
‘Loop through all Chart Objects
For Each ChtObj In ActiveSheet.ChartObjects
ChtObj.Width = ChtWidth
ChtObj.Height = ChtHeight
ChtObj.Top = TopPosition
ChtObj.Left = LeftPosition
TopPosition = TopPosition + ChtObj.Height
Next ChtObj
End Sub
The SizeAndAlignCharts macro requires that a chart is active, and uses this chart
as a “base.” The procedure resizes all other charts on the sheet such that they are the
same size as the active chart. In addition, the charts are repositioned so they are all
aligned vertically.
Search JabSto ::




Custom Search