98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View ReviewsForum home » Delegate support and help forum » Microsoft Excel Training and help » Macro - Runtime Error
Macro - Runtime Error
Resolved · Urgent Priority · Version 2002/XP
Ashraful has attended:
Excel Advanced course
Macro - Runtime Error
I have a line chart which I created which shows data on one axis. I created a macro which when clicked by the user creates a secondary axis and shows this data on the chart as well. I also have another macro which resets the chart back to one axis and only displays data along that single axis.
It all works fine except if you click the macro button twice (either for it to show the secondary axis and data or if you click the reset button twice). I get the following error message:
Runtime Error '1004':
Unable to set the AxisGroup property of the Series class
Here's the VBA code:
ActiveSheet.ChartObjects("Chart 160").Activate
ActiveChart.Axes(xlValue).MajorGridlines.Select
ActiveChart.PlotArea.Select
ActiveChart.SetSourceData Source:=Sheets("USD Pivot").Range("A52:B58,D52:D58" _
), PlotBy:=xlColumns
ActiveChart.SeriesCollection(2).Select
ActiveChart.SeriesCollection(2).AxisGroup = 2
ActiveChart.Axes(xlValue, xlSecondary).Select
Selection.TickLabels.AutoScaleFont = True
With Selection.TickLabels.Font
.Name = "Futura Medium"
.FontStyle = "Regular"
.Size = 11
.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 = "Futura Medium"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
ActiveChart.Axes(xlValue).MajorGridlines.Select
ActiveChart.ChartArea.Select
ActiveChart.PlotArea.Select
With ActiveChart
.Axes(xlValue, xlSecondary).HasTitle = True
.Axes(xlValue, xlSecondary).AxisTitle.Characters.Text = "AGO Vol (Litres)"
End With
ActiveChart.ChartArea.Select
ActiveChart.Axes(xlValue, xlSecondary).AxisTitle.Select
Selection.AutoScaleFont = True
With Selection.Font
.Name = "Futura Medium"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
ActiveWindow.Visible = False
Windows("CRT Rebate Savings Tracker September 2008.xls").Activate
Range("J1").Select
ActiveWindow.SmallScroll Down:=-6
End Sub
RE: Macro - Runtime Error
I either want to correct the code or just get excel to ignore it so it doesn't display an error message to the user if they click the button twice.
For the reset button I get the following issue:
Runtime Error '1004':
Method 'SeriesCollection' of object' _Chart' failed
VBA Code:
ActiveSheet.ChartObjects("Chart 160").Activate
ActiveChart.SeriesCollection(2).Select
Selection.Delete
ActiveWindow.Visible = False
Windows("CRT Rebate Savings Tracker September 2008.xls").Activate
Range("J1").Select
End Sub
Training information:
See also:
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Multiple Lines of Text in a CellAs an alternative to the Text Wrapping facility, type a word or two, press Alt+Enter to get a new line, type more text, and continue the process for as many lines as you need. Enter as normal when you have finished. |