Dynamic Axis Titles

[Eli]

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

 

[Jarek Kujawa]

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