PDA

View Full Version : ScaleAxes with VBA problem



bopha99
10-30-2014, 04:17 PM
Hello,

I am running:

Sub ScaleAxes()
With ActiveChart.Axes(xlCategory, xlPrimary)
.MaximumScale = ActiveSheet.Range("h10").Value
.MinimumScale = ActiveSheet.Range("h11").Value
.MajorUnit = ActiveSheet.Range("h12").Value
End With
With ActiveChart.Axes(xlValue, xlPrimary)
.MaximumScale = ActiveSheet.Range("i10").Value
.MinimumScale = ActiveSheet.Range("i11").Value
.MajorUnit = ActiveSheet.Range("i12").Value
End With
End Sub
to scale my axes in a chart. My axes are here:


Axis
X
Y


Max
10
900000


Min
0
500000


Tick
1
100000






where h10 is 10 and i10 is 900000. The axes shown above is on tab "Calculations." The chart that I want is on tab "Fee Analysis". I am getting the error Run-time error '-2147467259 (80004005)': Method 'MaximumScale' of object 'Axis' failed....What am I doing wrong? What can I do to get this to work?

Also, if you know how to add into the vba code so that in the chart the last value on both lines comes up as a data label in dollars with nothing after the decimal point that would be awesome. Thanks in advance.

bopha99
10-30-2014, 05:03 PM
Scratch that...post I figured out how to get the vba...next is..how do I add a button to perform the task and then...how do I add in in vba how to add a data label at the last point in the chart for the highest value on both lines?

Dave
11-02-2014, 06:21 AM
I don't understand the button request... just goto the toolbox and add a button to the sheet from the activeX controls, right click on it, select view code, and then insert your code.... is this what you want? Anyways, it's not clear what data label U want... the last point, the highest value or what. To only add 1 label, I think U have to add all the labels and then remove the ones U don't want. Here's some data label code to get U started. HTH. Dave

'add data labels to series 1
ActiveChart.SeriesCollection(1).ApplyDataLabels Type:=xlDataLabelsShowValue, _
AutoText:=True, LegendKey:=False
'format data labels
With Sheets("Sheet1").ChartObjects(1).Chart.SeriesCollection(1).DataLabels
.Position = xlLabelPositionAbove
.Orientation = xlHorizontal
.AutoScaleFont = False
.Font.Size = 13
.Font.Bold = True
.Font.Italic = True
.NumberFormat = "$#,##0."
End With
'remove data labels
ActiveChart.SeriesCollection(7).Points(1).DataLabel.Delete



edit: Forgot to mention that data labels are attached to the points collection.