Excel Vba Help Required
Hello,
Wondered if you could help me with a little issue I'm having. I'm exporting some data from Access to Excel and converting into some fancy graphs. The number of series' ranges from 2 to 5. Below isa copy of the code I am using to format the graph in terms of series names, layout, colours of lines, etc. When the full 5 series are there to be charted there is no problem with the code, however, when I try to convert less the 5 series I get an error message saying error code 400. I have tried to trap the error and it seems to be when I try to name the values on the X-axis. I've tried to take that line out but I can't seem to get it to work. Any help would be greatly appreciated.
Sub AutoExec()
Dim budgetusage As Boolean, actualcost As Boolean, budgetcost As Boolean
Dim row As Integer, series As Integer
Dim sheet_name As String, i As Integer
'MsgBox "auto"
On Error GoTo last_sheet
While 1
ActiveSheet.Nex t.Select
Wend
last_sheet:
sheet_name = ActiveSheet.Nam e
On Error GoTo 0
Sheets(sheet_na me).Select
Range("A1").Sel ect
Selection.End(x lDown).Select
row = ActiveCell.row
Cells(row + 1, 4).Formula = "=SUM(D2:D" & row & ")"
budgetusage = (Cells(row + 1, 4).Value <> 0)
Cells(row + 1, 5).Formula = "=SUM(E2:E" & row & ")"
actualcost = (Cells(row + 1, 5).Value <> 0)
Cells(row + 1, 6).Formula = "=SUM(F2:F" & row & ")"
budgetcost = (Cells(row + 1, 6).Value <> 0)
Sheets("Graphic alAnalysisWeek" ).Select
ActiveChart.Cha rtArea.Select
Sheets("Graphic alAnalysisWeek" ).Copy after:=Sheets(s heet_name)
Sheets(ActiveSh eet.Name).Name = sheet_name & " chart"
series = 1
ActiveChart.Ser iesCollection.N ewSeries
ActiveChart.Ser iesCollection(s eries).Values = "='" & sheet_name & "'!R2C2:R" & row & "C2"
ActiveChart.Ser iesCollection(s eries).Name = "=""Product ion Volume"""
series = 2
ActiveChart.Ser iesCollection.N ewSeries
ActiveChart.Ser iesCollection(s eries).Values = "='" & sheet_name & "'!R2C3:R" & row & "C3"
ActiveChart.Ser iesCollection(s eries).Name = "=""Usage Volume"""
series = 3
If budgetusage Then
ActiveChart.Ser iesCollection.N ewSeries
ActiveChart.Ser iesCollection(s eries).Values = "='" & sheet_name & "'!R2C4:R" & row & "C4"
ActiveChart.Ser iesCollection(s eries).Name = "=""Budget Usage"""
series = series + 1
End If
If actualcost Then
ActiveChart.Ser iesCollection.N ewSeries
ActiveChart.Ser iesCollection(s eries).Values = "='" & sheet_name & "'!R2C5:R" & row & "C5"
ActiveChart.Ser iesCollection(s eries).Name = "=""Actual Cost"""
series = series + 1
End If
If budgetcost Then
ActiveChart.Ser iesCollection.N ewSeries
ActiveChart.Ser iesCollection(s eries).Values = "='" & sheet_name & "'!R2C6:R" & row & "C6"
ActiveChart.Ser iesCollection(s eries).Name = "=""Budget Cost"""
End If
MsgBox ""
For i = 1 To series
ActiveChart.Ser iesCollection(i ).XValues = "='" & sheet_name & "'!R2C1:R" & row & "C1"
Next i
MsgBox ""
ActiveChart.Cha rtArea.Select
ActiveChart.Plo tArea.Select
ActiveChart.App lyCustomType ChartType:=xlBu iltIn, TypeName:= _
"Lines on 2 Axes"
ActiveChart.Axe s(xlCategory).S elect
With Selection.TickL abels
.Alignment = xlCenter
.Offset = 100
.ReadingOrder = xlContext
.Orientation = xlUpward
End With
ActiveChart.Plo tArea.Select
ActiveChart.Cha rtArea.Select
Selection.AutoS caleFont = False
With Selection.Font
.Name = "Arial"
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyl eNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
Selection.Font. Bold = True
With Selection.Borde r
.Weight = xlHairline
.LineStyle = xlNone
End With
Selection.Shado w = False
Selection.Fill. TwoColorGradien t Style:=msoGradi entHorizontal, Variant:=1
With Selection
.Fill.Visible = True
.Fill.ForeColor .SchemeColor = 35
.Fill.BackColor .SchemeColor = 2
End With
ActiveChart.Plo tArea.Select
With Selection.Borde r
.ColorIndex = 16
.Weight = xlMedium
.LineStyle = xlContinuous
End With
Selection.Inter ior.ColorIndex = xlNone
With Selection.Borde r
.ColorIndex = 16
.Weight = xlMedium
.LineStyle = xlContinuous
End With
With Selection.Inter ior
.ColorIndex = 2
.PatternColorIn dex = 1
.Pattern = xlSolid
End With
With ActiveChart
.HasTitle = True
.ChartTitle.Cha racters.Text = "Utilities Tracker"
.Axes(xlCategor y, xlPrimary).HasT itle = True
.Axes(xlCategor y, xlPrimary).Axis Title.Character s.Text = "Date"
.Axes(xlValue, xlPrimary).HasT itle = True
.Axes(xlValue, xlPrimary).Axis Title.Character s.Text = "Volume"
.Axes(xlCategor y, xlSecondary).Ha sTitle = True
.Axes(xlCategor y, xlSecondary).Ax isTitle.Charact ers.Text = "£"
End With
With ActiveChart.Axe s(xlCategory)
.HasMajorGridli nes = False
.HasMinorGridli nes = False
End With
With ActiveChart.Axe s(xlValue)
.HasMajorGridli nes = True
.HasMinorGridli nes = False
End With
ActiveChart.Has Legend = True
ActiveChart.Leg end.Select
Selection.Posit ion = xlBottom
ActiveChart.Cha rtArea.Select
With ActiveChart
.Axes(xlCategor y, xlSecondary).Ha sTitle = False
.Axes(xlValue, xlSecondary).Ha sTitle = True
.Axes(xlValue, xlSecondary).Ax isTitle.Charact ers.Text = "£"
End With
ActiveChart.Ser iesCollection(5 ).Select
With Selection.Borde r
.ColorIndex = 57
.Weight = xlMedium
.LineStyle = xlContinuous
End With
With Selection
.MarkerBackgrou ndColorIndex = xlNone
.MarkerForegrou ndColorIndex = xlAutomatic
.MarkerStyle = xlNone
.Smooth = False
.MarkerSize = 5
.Shadow = False
End With
ActiveChart.Ser iesCollection(1 ).Select
With Selection.Borde r
.ColorIndex = 41
.Weight = xlMedium
.LineStyle = xlContinuous
End With
With Selection
.MarkerBackgrou ndColorIndex = xlAutomatic
.MarkerForegrou ndColorIndex = xlAutomatic
.MarkerStyle = xlNone
.Smooth = False
.MarkerSize = 5
.Shadow = False
End With
ActiveChart.Plo tArea.Select
ActiveChart.Ser iesCollection(4 ).Select
With Selection.Borde r
.ColorIndex = 57
.Weight = xlMedium
.LineStyle = xlContinuous
End With
With Selection
.MarkerBackgrou ndColorIndex = xlAutomatic
.MarkerForegrou ndColorIndex = xlAutomatic
.MarkerStyle = xlNone
.Smooth = False
.MarkerSize = 5
.Shadow = False
End With
ActiveChart.Ser iesCollection(2 ).Select
With Selection.Borde r
.ColorIndex = 57
.Weight = xlMedium
.LineStyle = xlContinuous
End With
With Selection
.MarkerBackgrou ndColorIndex = xlAutomatic
.MarkerForegrou ndColorIndex = xlAutomatic
.MarkerStyle = xlNone
.Smooth = False
.MarkerSize = 5
.Shadow = False
End With
ActiveChart.Ser iesCollection(5 ).Select
With Selection.Borde r
.ColorIndex = 3
.Weight = xlMedium
.LineStyle = xlContinuous
End With
With Selection
.MarkerBackgrou ndColorIndex = xlNone
.MarkerForegrou ndColorIndex = xlNone
.MarkerStyle = xlNone
.Smooth = False
.MarkerSize = 5
.Shadow = False
End With
ActiveChart.Axe s(xlValue).Majo rGridlines.Sele ct
ActiveChart.Ser iesCollection(3 ).Select
With Selection.Borde r
.ColorIndex = 4
.Weight = xlMedium
.LineStyle = xlContinuous
End With
With Selection
.MarkerBackgrou ndColorIndex = xlNone
.MarkerForegrou ndColorIndex = xlAutomatic
.MarkerStyle = xlNone
.Smooth = False
.MarkerSize = 5
.Shadow = False
End With
ActiveChart.Axe s(xlValue, xlSecondary).Se lect
With ActiveChart.Axe s(xlValue, xlSecondary)
.MinimumScaleIs Auto = True
.MaximumScale = 6000
.MinorUnitIsAut o = True
.MajorUnitIsAut o = True
.Crosses = xlAutomatic
.ReversePlotOrd er = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
End Sub
Wondered if you could help me with a little issue I'm having. I'm exporting some data from Access to Excel and converting into some fancy graphs. The number of series' ranges from 2 to 5. Below isa copy of the code I am using to format the graph in terms of series names, layout, colours of lines, etc. When the full 5 series are there to be charted there is no problem with the code, however, when I try to convert less the 5 series I get an error message saying error code 400. I have tried to trap the error and it seems to be when I try to name the values on the X-axis. I've tried to take that line out but I can't seem to get it to work. Any help would be greatly appreciated.
Sub AutoExec()
Dim budgetusage As Boolean, actualcost As Boolean, budgetcost As Boolean
Dim row As Integer, series As Integer
Dim sheet_name As String, i As Integer
'MsgBox "auto"
On Error GoTo last_sheet
While 1
ActiveSheet.Nex t.Select
Wend
last_sheet:
sheet_name = ActiveSheet.Nam e
On Error GoTo 0
Sheets(sheet_na me).Select
Range("A1").Sel ect
Selection.End(x lDown).Select
row = ActiveCell.row
Cells(row + 1, 4).Formula = "=SUM(D2:D" & row & ")"
budgetusage = (Cells(row + 1, 4).Value <> 0)
Cells(row + 1, 5).Formula = "=SUM(E2:E" & row & ")"
actualcost = (Cells(row + 1, 5).Value <> 0)
Cells(row + 1, 6).Formula = "=SUM(F2:F" & row & ")"
budgetcost = (Cells(row + 1, 6).Value <> 0)
Sheets("Graphic alAnalysisWeek" ).Select
ActiveChart.Cha rtArea.Select
Sheets("Graphic alAnalysisWeek" ).Copy after:=Sheets(s heet_name)
Sheets(ActiveSh eet.Name).Name = sheet_name & " chart"
series = 1
ActiveChart.Ser iesCollection.N ewSeries
ActiveChart.Ser iesCollection(s eries).Values = "='" & sheet_name & "'!R2C2:R" & row & "C2"
ActiveChart.Ser iesCollection(s eries).Name = "=""Product ion Volume"""
series = 2
ActiveChart.Ser iesCollection.N ewSeries
ActiveChart.Ser iesCollection(s eries).Values = "='" & sheet_name & "'!R2C3:R" & row & "C3"
ActiveChart.Ser iesCollection(s eries).Name = "=""Usage Volume"""
series = 3
If budgetusage Then
ActiveChart.Ser iesCollection.N ewSeries
ActiveChart.Ser iesCollection(s eries).Values = "='" & sheet_name & "'!R2C4:R" & row & "C4"
ActiveChart.Ser iesCollection(s eries).Name = "=""Budget Usage"""
series = series + 1
End If
If actualcost Then
ActiveChart.Ser iesCollection.N ewSeries
ActiveChart.Ser iesCollection(s eries).Values = "='" & sheet_name & "'!R2C5:R" & row & "C5"
ActiveChart.Ser iesCollection(s eries).Name = "=""Actual Cost"""
series = series + 1
End If
If budgetcost Then
ActiveChart.Ser iesCollection.N ewSeries
ActiveChart.Ser iesCollection(s eries).Values = "='" & sheet_name & "'!R2C6:R" & row & "C6"
ActiveChart.Ser iesCollection(s eries).Name = "=""Budget Cost"""
End If
MsgBox ""
For i = 1 To series
ActiveChart.Ser iesCollection(i ).XValues = "='" & sheet_name & "'!R2C1:R" & row & "C1"
Next i
MsgBox ""
ActiveChart.Cha rtArea.Select
ActiveChart.Plo tArea.Select
ActiveChart.App lyCustomType ChartType:=xlBu iltIn, TypeName:= _
"Lines on 2 Axes"
ActiveChart.Axe s(xlCategory).S elect
With Selection.TickL abels
.Alignment = xlCenter
.Offset = 100
.ReadingOrder = xlContext
.Orientation = xlUpward
End With
ActiveChart.Plo tArea.Select
ActiveChart.Cha rtArea.Select
Selection.AutoS caleFont = False
With Selection.Font
.Name = "Arial"
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyl eNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
Selection.Font. Bold = True
With Selection.Borde r
.Weight = xlHairline
.LineStyle = xlNone
End With
Selection.Shado w = False
Selection.Fill. TwoColorGradien t Style:=msoGradi entHorizontal, Variant:=1
With Selection
.Fill.Visible = True
.Fill.ForeColor .SchemeColor = 35
.Fill.BackColor .SchemeColor = 2
End With
ActiveChart.Plo tArea.Select
With Selection.Borde r
.ColorIndex = 16
.Weight = xlMedium
.LineStyle = xlContinuous
End With
Selection.Inter ior.ColorIndex = xlNone
With Selection.Borde r
.ColorIndex = 16
.Weight = xlMedium
.LineStyle = xlContinuous
End With
With Selection.Inter ior
.ColorIndex = 2
.PatternColorIn dex = 1
.Pattern = xlSolid
End With
With ActiveChart
.HasTitle = True
.ChartTitle.Cha racters.Text = "Utilities Tracker"
.Axes(xlCategor y, xlPrimary).HasT itle = True
.Axes(xlCategor y, xlPrimary).Axis Title.Character s.Text = "Date"
.Axes(xlValue, xlPrimary).HasT itle = True
.Axes(xlValue, xlPrimary).Axis Title.Character s.Text = "Volume"
.Axes(xlCategor y, xlSecondary).Ha sTitle = True
.Axes(xlCategor y, xlSecondary).Ax isTitle.Charact ers.Text = "£"
End With
With ActiveChart.Axe s(xlCategory)
.HasMajorGridli nes = False
.HasMinorGridli nes = False
End With
With ActiveChart.Axe s(xlValue)
.HasMajorGridli nes = True
.HasMinorGridli nes = False
End With
ActiveChart.Has Legend = True
ActiveChart.Leg end.Select
Selection.Posit ion = xlBottom
ActiveChart.Cha rtArea.Select
With ActiveChart
.Axes(xlCategor y, xlSecondary).Ha sTitle = False
.Axes(xlValue, xlSecondary).Ha sTitle = True
.Axes(xlValue, xlSecondary).Ax isTitle.Charact ers.Text = "£"
End With
ActiveChart.Ser iesCollection(5 ).Select
With Selection.Borde r
.ColorIndex = 57
.Weight = xlMedium
.LineStyle = xlContinuous
End With
With Selection
.MarkerBackgrou ndColorIndex = xlNone
.MarkerForegrou ndColorIndex = xlAutomatic
.MarkerStyle = xlNone
.Smooth = False
.MarkerSize = 5
.Shadow = False
End With
ActiveChart.Ser iesCollection(1 ).Select
With Selection.Borde r
.ColorIndex = 41
.Weight = xlMedium
.LineStyle = xlContinuous
End With
With Selection
.MarkerBackgrou ndColorIndex = xlAutomatic
.MarkerForegrou ndColorIndex = xlAutomatic
.MarkerStyle = xlNone
.Smooth = False
.MarkerSize = 5
.Shadow = False
End With
ActiveChart.Plo tArea.Select
ActiveChart.Ser iesCollection(4 ).Select
With Selection.Borde r
.ColorIndex = 57
.Weight = xlMedium
.LineStyle = xlContinuous
End With
With Selection
.MarkerBackgrou ndColorIndex = xlAutomatic
.MarkerForegrou ndColorIndex = xlAutomatic
.MarkerStyle = xlNone
.Smooth = False
.MarkerSize = 5
.Shadow = False
End With
ActiveChart.Ser iesCollection(2 ).Select
With Selection.Borde r
.ColorIndex = 57
.Weight = xlMedium
.LineStyle = xlContinuous
End With
With Selection
.MarkerBackgrou ndColorIndex = xlAutomatic
.MarkerForegrou ndColorIndex = xlAutomatic
.MarkerStyle = xlNone
.Smooth = False
.MarkerSize = 5
.Shadow = False
End With
ActiveChart.Ser iesCollection(5 ).Select
With Selection.Borde r
.ColorIndex = 3
.Weight = xlMedium
.LineStyle = xlContinuous
End With
With Selection
.MarkerBackgrou ndColorIndex = xlNone
.MarkerForegrou ndColorIndex = xlNone
.MarkerStyle = xlNone
.Smooth = False
.MarkerSize = 5
.Shadow = False
End With
ActiveChart.Axe s(xlValue).Majo rGridlines.Sele ct
ActiveChart.Ser iesCollection(3 ).Select
With Selection.Borde r
.ColorIndex = 4
.Weight = xlMedium
.LineStyle = xlContinuous
End With
With Selection
.MarkerBackgrou ndColorIndex = xlNone
.MarkerForegrou ndColorIndex = xlAutomatic
.MarkerStyle = xlNone
.Smooth = False
.MarkerSize = 5
.Shadow = False
End With
ActiveChart.Axe s(xlValue, xlSecondary).Se lect
With ActiveChart.Axe s(xlValue, xlSecondary)
.MinimumScaleIs Auto = True
.MaximumScale = 6000
.MinorUnitIsAut o = True
.MajorUnitIsAut o = True
.Crosses = xlAutomatic
.ReversePlotOrd er = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
End Sub