macsailer


Hello everyone,

My knowledge of Visual Basic is quite limited, and this code was generated through use of the Macro Recorder in Microsoft Excel. I am trying to add another column to the Chart which I want to create, however, I have been having minimal luck, recieving the error message 'Run Time Error '1004' - Select Method of Axis Class Failed' everytime. I have included the generated code, with some minor changes below, with a noticable banner at the line where I enounter my problem, which is almost at the bottom. Thanks in advance for any help you may be able to offer.

Sub ChangeSize()
'
' ChangeSize Macro
' Macro recorded 6/25/2007 by macs
'

'
ActiveChart.ChartArea.Select
ActiveSheet.Shapes("Chart 13").IncrementLeft -183.75
ActiveSheet.Shapes("Chart 13").IncrementTop -87.75
ActiveSheet.Shapes("Chart 13").ScaleWidth 1.63, msoFalse, msoScaleFromTopLeft
ActiveSheet.Shapes("Chart 13").ScaleHeight 2.24, msoFalse, msoScaleFromTopLeft
ActiveWindow.Visible = False
Windows("Copy of South Well Test Tracker2.xls").Activate
Sheets("Imperial Pivot Chart").Select
ActiveSheet.ChartObjects("Chart 13").Activate
Windows("Copy of South Well Test Tracker2.xls").SmallScroll Down:=9
ActiveWindow.Visible = False
Windows("Copy of South Well Test Tracker2.xls").Activate
Sheets("Imperial Pivot Chart Macro").Select
ActiveSheet.ChartObjects("Chart 13").Activate
Windows("Copy of South Well Test Tracker2.xls").SmallScroll Down:=-21
ActiveWindow.Visible = False
Windows("Copy of South Well Test Tracker2.xls").Activate
Sheets("Imperial Pivot Chart").Select
ActiveSheet.ChartObjects("Chart 13").Activate
Windows("Copy of South Well Test Tracker2.xls").SmallScroll Down:=-18
ActiveChart.ChartTitle.Select
ActiveWindow.Visible = False
Windows("Copy of South Well Test Tracker2.xls").Activate
Sheets("Imperial Pivot Chart Macro").Select
ActiveSheet.ChartObjects("Chart 13").Activate
ActiveChart.PlotArea.Select
Selection.Left = 80
Selection.Top = 120
Selection.Width = 371
Selection.Height = 245
Selection.Left = 65
Selection.Top = 113
Selection.Width = 386
Selection.Height = 253
Selection.Left = 58
Selection.Top = 112
Selection.Width = 393
Selection.Height = 254
Selection.Width = 413
ActiveWindow.Visible = False
Windows("Copy of South Well Test Tracker2.xls").Activate
Sheets("Imperial Pivot Chart").Select
ActiveSheet.ChartObjects("Chart 13").Activate
ActiveWindow.Visible = False
Windows("Copy of South Well Test Tracker2.xls").Activate
Sheets("Imperial Pivot Chart Macro").Select
ActiveSheet.ChartObjects("Chart 13").Activate
ActiveChart.ChartArea.Select
ActiveWindow.Visible = False
Windows("Copy of South Well Test Tracker2.xls").Activate
Sheets("Imperial Pivot Chart").Select
ActiveSheet.ChartObjects("Chart 13").Activate
ActiveWindow.Visible = False
Windows("Copy of South Well Test Tracker2.xls").Activate
Sheets("Imperial Pivot Chart Macro").Select
ActiveSheet.ChartObjects("Chart 13").Activate
ActiveWindow.Visible = False
Windows("Copy of South Well Test Tracker2.xls").Activate
Sheets("Imperial Pivot Chart").Select
ActiveSheet.ChartObjects("Chart 13").Activate
ActiveWindow.Visible = False
Windows("Copy of South Well Test Tracker2.xls").Activate
Sheets("Imperial Pivot Chart Macro").Select
ActiveSheet.ChartObjects("Chart 13").Activate
ActiveWindow.Visible = False
Windows("Copy of South Well Test Tracker2.xls").Activate
Sheets("Imperial Pivot Chart").Select
ActiveSheet.ChartObjects("Chart 13").Activate
ActiveWindow.Visible = False
Windows("Copy of South Well Test Tracker2.xls").Activate
Sheets("Imperial Pivot Chart Macro").Select
ActiveSheet.ChartObjects("Chart 13").Activate
ActiveWindow.Visible = False
Windows("Copy of South Well Test Tracker2.xls").Activate
Sheets("Imperial Pivot Chart").Select
ActiveSheet.ChartObjects("Chart 13").Activate
ActiveWindow.Visible = False
Windows("Copy of South Well Test Tracker2.xls").Activate
Sheets("Imperial Pivot Chart Macro").Select
ActiveSheet.ChartObjects("Chart 13").Activate
ActiveChart.PlotArea.Select
Selection.Top = 111
Selection.Height = 255
Selection.Height = 261
ActiveWindow.Visible = False
Windows("Copy of South Well Test Tracker2.xls").Activate
Sheets("Imperial Pivot Chart").Select
ActiveSheet.ChartObjects("Chart 13").Activate
ActiveWindow.Visible = False
Windows("Copy of South Well Test Tracker2.xls").Activate
Sheets("Imperial Pivot Chart Macro").Select
ActiveSheet.ChartObjects("Chart 13").Activate
ActiveWindow.Visible = False
Windows("Copy of South Well Test Tracker2.xls").Activate
Sheets("Imperial Pivot Chart").Select
ActiveSheet.ChartObjects("Chart 13").Activate
ActiveWindow.Visible = False
Windows("Copy of South Well Test Tracker2.xls").Activate
Sheets("Imperial Pivot Chart Macro").Select
ActiveSheet.ChartObjects("Chart 13").Activate
ActiveWindow.Visible = False
Windows("Copy of South Well Test Tracker2.xls").Activate
Sheets("Imperial Pivot Chart").Select
ActiveSheet.ChartObjects("Chart 13").Activate
ActiveWindow.Visible = False
Windows("Copy of South Well Test Tracker2.xls").Activate
Sheets("Imperial Pivot Chart Macro").Select
ActiveSheet.ChartObjects("Chart 13").Activate
ActiveWindow.Visible = False
Windows("Copy of South Well Test Tracker2.xls").Activate
Sheets("Imperial Pivot Chart").Select
ActiveSheet.ChartObjects("Chart 13").Activate
ActiveWindow.Visible = False
Windows("Copy of South Well Test Tracker2.xls").Activate
Sheets("Imperial Pivot Chart Macro").Select
ActiveSheet.ChartObjects("Chart 13").Activate
Windows("Copy of South Well Test Tracker2.xls").SmallScroll Down:=-3
ActiveChart.Legend.Select
Selection.Left = 28
Selection.Top = 40
ActiveChart.ChartTitle.Select
ActiveWindow.Visible = False
Windows("Copy of South Well Test Tracker2.xls").Activate
Sheets("Imperial Pivot Chart").Select
ActiveSheet.ChartObjects("Chart 13").Activate
ActiveWindow.Visible = False
Windows("Copy of South Well Test Tracker2.xls").Activate
Sheets("Imperial Pivot Chart Macro").Select
ActiveSheet.ChartObjects("Chart 13").Activate
Selection.AutoScaleFont = True
With Selection.Font
.Name = "Arial"
.Size = 19.5
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
ActiveChart.Legend.Select
ActiveWindow.Visible = False
Windows("Copy of South Well Test Tracker2.xls").Activate
Sheets("Imperial Pivot Chart").Select
ActiveSheet.ChartObjects("Chart 13").Activate
ActiveWindow.Visible = False
Windows("Copy of South Well Test Tracker2.xls").Activate
Sheets("Imperial Pivot Chart Macro").Select
ActiveSheet.ChartObjects("Chart 13").Activate
ActiveWindow.Visible = False
Windows("Copy of South Well Test Tracker2.xls").Activate
Sheets("Imperial Pivot Chart").Select
ActiveSheet.ChartObjects("Chart 13").Activate
ActiveWindow.Visible = False
Windows("Copy of South Well Test Tracker2.xls").Activate
Sheets("Imperial Pivot Chart Macro").Select
ActiveSheet.ChartObjects("Chart 13").Activate
ActiveWindow.Visible = False
Windows("Copy of South Well Test Tracker2.xls").Activate
Sheets("Imperial Pivot Chart").Select
ActiveSheet.ChartObjects("Chart 13").Activate
ActiveChart.Axes(xlValue).Select
ActiveWindow.Visible = False
Windows("Copy of South Well Test Tracker2.xls").Activate
Sheets("Imperial Pivot Chart Macro").Select
ActiveSheet.ChartObjects("Chart 13").Activate
ActiveChart.Axes(xlValue).Select
Selection.TickLabels.AutoScaleFont = True
With Selection.TickLabels.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 19.75
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
ActiveChart.Axes(xlCategory).Select
Selection.TickLabels.AutoScaleFont = True
With Selection.TickLabels.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 19.75
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
With ActiveChart.Axes(xlCategory)
.MinimumScaleIsAuto = True
.MaximumScaleIsAuto = True
.BaseUnitIsAuto = True
.MajorUnit = 2
.MajorUnitScale = xlMonths
.MinorUnitIsAuto = True
.Crosses = xlAutomatic
.AxisBetweenCategories = True
.ReversePlotOrder = False
End With
With Selection.TickLabels
.Alignment = xlCenter
.Offset = 100
.ReadingOrder = xlContext
.Orientation = xlUpward
End With
With Selection.TickLabels
.Alignment = xlCenter
.Offset = 100
.ReadingOrder = xlContext
.Orientation = xlDownward
End With
ActiveWindow.Visible = False
Windows("Copy of South Well Test Tracker2.xls").Activate
Sheets("Imperial Pivot Chart").Select
ActiveSheet.ChartObjects("Chart 13").Activate
ActiveWindow.Visible = False
Windows("Copy of South Well Test Tracker2.xls").Activate
Sheets("Imperial Pivot Chart Macro").Select
ActiveSheet.ChartObjects("Chart 13").Activate
With Selection.TickLabels
.Alignment = xlCenter
.Offset = 100
.ReadingOrder = xlContext
.Orientation = xlUpward
End With
ActiveWindow.Visible = False
Windows("Copy of South Well Test Tracker2.xls").Activate
Sheets("Imperial Pivot Chart").Select
ActiveSheet.ChartObjects("Chart 13").Activate
ActiveWindow.Visible = False
Windows("Copy of South Well Test Tracker2.xls").Activate
Sheets("Imperial Pivot Chart Macro").Select
ActiveSheet.ChartObjects("Chart 13").Activate
ActiveChart.Axes(xlValue).AxisTitle.Select
Selection.AutoScaleFont = True
With Selection.Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 20
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
Windows("Copy of South Well Test Tracker2.xls").SmallScroll Down:=6
ActiveWindow.Visible = False
Windows("Copy of South Well Test Tracker2.xls").Activate
Sheets("Imperial Pivot Chart").Select
ActiveSheet.ChartObjects("Chart 13").Activate
Windows("Copy of South Well Test Tracker2.xls").SmallScroll Down:=21
ActiveChart.Axes(xlCategory).AxisTitle.Select
ActiveWindow.Visible = False
Windows("Copy of South Well Test Tracker2.xls").Activate
Sheets("Imperial Pivot Chart Macro").Select
ActiveSheet.ChartObjects("Chart 13").Activate
ActiveChart.Axes(xlCategory).AxisTitle.Select
Selection.AutoScaleFont = True
With Selection.Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 19.75
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
Windows("Copy of South Well Test Tracker2.xls").SmallScroll Down:=-12
ActiveWindow.Visible = False
Windows("Copy of South Well Test Tracker2.xls").Activate
Sheets("Imperial Pivot Chart").Select
ActiveSheet.ChartObjects("Chart 13").Activate
Windows("Copy of South Well Test Tracker2.xls").SmallScroll Down:=-27
ActiveWindow.Visible = False
Windows("Copy of South Well Test Tracker2.xls").Activate
Sheets("Imperial Pivot Chart Macro").Select
ActiveSheet.ChartObjects("Chart 13").Activate
Windows("Copy of South Well Test Tracker2.xls").SmallScroll Down:=-15
ActiveChart.Legend.Select
Selection.Top = 54
ActiveChart.PlotArea.Select
Selection.Top = 105
Selection.Height = 268
Selection.Top = 98
Selection.Height = 275
Selection.Width = 422
ActiveChart.Axes(xlValue).Select
Selection.TickLabels.AutoScaleFont = True
With Selection.TickLabels.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 19
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
Selection.TickLabels.AutoScaleFont = True
With Selection.TickLabels.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 19.75
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
ActiveChart.Axes(xlValue).AxisTitle.Select
Selection.AutoScaleFont = True
With Selection.Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 20
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
ActiveChart.Axes(xlCategory).Select
Selection.TickLabels.AutoScaleFont = True
With Selection.TickLabels.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 19.75
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
Windows("Copy of South Well Test Tracker2.xls").SmallScroll Down:=12
ActiveChart.Axes(xlCategory).AxisTitle.Select
Selection.AutoScaleFont = True
With Selection.Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 19.75
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
Windows("Copy of South Well Test Tracker2.xls").SmallScroll Down:=-15
End Sub
Sub ImperialPivotChartMacro()
'
' ImperialPivotChartMacro Macro
' Macro recorded 6/25/07 by macs
'
' Keyboard Shortcut: Ctrl+q
'
ActiveCell.FormulaR1C1 = "='Imperial Pivot Chart Data'!A6"
Range("A1").Select
ActiveCell.FormulaR1C1 = "='Imperial Pivot Chart Data'!R[5]C"
Range("A2").Select
Charts.Add
ActiveChart.ApplyCustomType ChartType:=xlUserDefined, TypeName:="coke"
ActiveChart.SetSourceData Source:=Sheets("Imperial Pivot Chart Macro").Range( _
"A1"), PlotBy:=xlRows

ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).XValues = _
"='Imperial Pivot Chart Data'!R2C2:R2C25"
ActiveChart.SeriesCollection(1).Values = _
"='Imperial Pivot Chart Data'!R6C2:R6C25"
ActiveChart.SeriesCollection(1).Name = "='Imperial Pivot Chart Macro'!R1C1"
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(2).AxisGroup = 2
ActiveChart.SeriesCollection(2).Values = _
"='Imperial Pivot Chart Data'!R6C26:R6C49"
ActiveChart.SeriesCollection(2).Name = "=""Tubing Pressure"""
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(3).AxisGroup = 2
ActiveChart.SeriesCollection(3).Values = _
"='Imperial Pivot Chart Data'!R6C50:R6C73"
ActiveChart.SeriesCollection(3).Name = "=""Casing Pressure"""
ActiveChart.Location xlLocationAsNewSheet, "Test"
ActiveChart.HasDataTable = False
ActiveChart.Axes(xlValue).Select
ActiveChart.Axes(xlCategory).AxisTitle.Select
ActiveChart.Axes(xlValue).AxisTitle.Select

!!!!ERROR ENOUNTERED BELOW!!!!!
ActiveChart.Axes(xlValue, xlSecondary).AxisTitle.Select
ActiveChart.Axes(xlValue, xlSecondary).Select
ActiveChart.Axes(xlCategory).Select
With Selection.TickLabels
.Alignment = xlCenter
.Offset = 100
.ReadingOrder = xlContext
.Orientation = xlUpward
End With
ActiveSheet.ChartObjects("Test").Activate
Windows("Copy of South Well Test Tracker.xls").Activate
ActiveWindow.Visible = False
ActiveSheet.ChartObjects("Test").Activate
Windows("Copy of South Well Test Tracker.xls").Activate
ActiveWindow.SelectedSheets.PrintPreview
ActiveWindow.SelectedSheets.PrintPreview

End Sub

Thanks again,

Mac





Re: Run Time Error '1004' - Select Method of Axis Class Failed

Jon Peltier


That's a lot of code, much of redundant. The following are the lines which you indicated cause problems. The first two do nothing but select objects which are then ignored.


ActiveChart.Axes(xlValue, xlSecondary).AxisTitle.Select
ActiveChart.Axes(xlValue, xlSecondary).Select
ActiveChart.Axes(xlCategory).Select

Any time you have Object.Select followed by Selection.Keyword, you should replace it by a single step Object.Keyword.

If the axis in question has no axis title, then any reference to its .AxisTitle property will fail. If a chart does not have a particular axis (such as the secondary value axis in your example), any code referring to this axis will fail. I don't know what your chart looks like, so I can't tell what the missing element might be.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______