Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dynamic Axis Titles
I have a dynamic chart, thus every month when I add new data, the chart
automatically updates. The problem I have is the Axis Range that Excel automatically sets is way too big. I know I can fix the range, however as my data changes the data may wander out of the range. Thus I would like to know if I can set a dynamic range, like 100 below and above the min and max of the data? Any ideas? Thank you, Eli |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dynamic Axis Titles
look for .MaximumScale = max + 100 and .MinimumScale = min - 100 to
change your range (+-100) I used that macro in Excel 2003 but it works well in 2007 too Sub Y_adjust_axis() Dim k As String Dim max As Double Dim min As Double Dim pointsss As Integer Dim crosss As Double Dim i As Integer Dim j As Integer Dim unitsss As Double With ActiveSheet jednostki = ActiveChart.Axes(xlValue).DisplayUnit ActiveChart.Axes(xlValue).DisplayUnit = xlNone pointsss = ActiveChart.SeriesCollection(1).Points.Count On Error Resume Next For j = 1 To ActiveChart.SeriesCollection.Count ActiveChart.SeriesCollection(j).ApplyDataLabels AutoText:=True, ShowValue:=True For i = 1 To pointsss ActiveChart.SeriesCollection(j).Points (i).DataLabel.Select k = Selection.Characters.Text '/ 100 If Right(k, 1) = "%" Then k = Left(k, Len(k) - 1) / 100 End If If k max Then max = k End If If k < min Then min = k End If Next i Next j max = max + 100 If min <= 0 Then crosss = 0 ElseIf min 0 Then crosss = min * 0.9 End If With ActiveChart.Axes(xlValue) .MaximumScale = max + 100 .MinimumScale = min - 100 ActiveChart.Axes(xlValue).Select .TickLabels.NumberFormat = ";;;" .MinorTickMark = xlNone .MajorTickMark = xlNone '.MinorUnit = 2 '.MajorUnit = Abs(max / 2) '.Crosses = xlCustom .CrossesAt = crosss '.ReversePlotOrder = False '.ScaleType = xlLinear .DisplayUnit = unitsss End With ActiveChart.Deselect End With End Sub HIH On 12 Maj, 00:52, Eli wrote: I have a dynamic chart, thus every month when I add new data, the chart automatically updates. The problem I have is the Axis Range that Excel automatically sets is way too big. I know I can fix the range, however as my data changes the data may wander out of the range. Thus I would like to know if I can set a dynamic range, like 100 below and above the min and max of the data? Any ideas? Thank you, Eli |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Y Axis Titles (Excel is clipping titles) | Excel Discussion (Misc queries) | |||
Dynamic Titles in a Pivot Charts | Charts and Charting in Excel | |||
Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel | |||
Dynamic Chart Titles - formating | Charts and Charting in Excel | |||
Dynamic Chart Titles | Charts and Charting in Excel |