macro runtime error

Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Macro - Runtime Error

Macro - Runtime Error

resolvedResolved · 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


 

Excel tip:

Multiple Lines of Text in a Cell

As 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.

The line break is not affected by changing the column width, as text wrapping. To remove this you must edit the cell and remove the invisible character and replace with a normal space.

View all Excel hints and tips


Server loaded in 0.08 secs.