Hi - I have a workbook of macros written in 2003 version byut they keep hanging up in 2007 version...apparently in the Chart Type row withih VBA code. Hoping someone can help me here. Code (sorry it is so long) is pasted below.
Error received on line:
ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:= _
"Lines on 2 Axes"
Error is noted as:
Run-time error '-2147467259 (80004005)': The specified diemnion is not valid for the current chart type.
==================================
Sub TableChart_Maker()
'
' Macro recorded 1/16/2008
'
Dim xx As Integer, yy As Integer, zz As Integer
Dim recdcount As Integer, predcount As Integer
Dim xwidth As Integer, xheight As Integer, Rw As Integer, cl As Integer
Dim sheetn As Integer
predcount = Range("TOTPREDTCONT").Value
recdcount = Range("TOTRECDCONT") + 3
sheetn = Sheets.count
' If the number of records is greater than 997, then the macro will stop
If recdcount > 4997 Then
Sheets("DATA").Select
Range("A5001").Select
MsgBox "Paste formulars in rows below 5000, and revise formulars in I to K", vbExclamation
End
Else
'if the number of records is less than 997, then the macro will product the tables and charts
If sheetn > 4 Then
MsgBox "Please delete all chart tabs!", vbExclamation
End
Else
' Chart size and position
xwidth = 580
xheight = 370
Rw = 5
cl = 5
zz = 0
For i = 4 To recdcount
Sheets("DATA").Select
'Extract data of each preditor from DATA sheet and then copy them to 'TEMPLATE' sheet
While Cells(i, "F").Value = "BL"
xx = Cells(i, "D").Value - 1
yy = Cells(i, "B").Value
If xx > 94 Then
Sheets("TEMPLATE").Select
Range("A1").Select
MsgBox "Paste formulars in rows below 100 and revise formulars in line 4", vbExclamation
End
Else
Cells(i, "F").Select
ActiveCell.Offset(-xx, 1).Activate
Range(ActiveCell, Cells(i, "J")).Select
Selection.Copy
Sheets("TEMPLATE").Select
Range("O5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range(Cells(5, "B"), Cells(5 + xx, "G")).Select
Selection.Copy
Sheets("TABLE").Select
'Copy the table of the first preditor from TEMPLATE sheet to TABLE sheet.
If yy = 1 Then
Range("A5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Sheets("TEMPLATE").Select
Range("B4", "G4").Select
Selection.Copy
Sheets("TABLE").Select
Cells(6 + xx, "A").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
'Add chart for the first predictor
myname = Cells(5, "A").Value
Application.ScreenUpdating = False
Worksheets.Add
ActiveSheet.Move After:=Sheets(ActiveWorkbook.Sheets.count)
Application.ScreenUpdating = True ' Enables screen refreshing.
ActiveSheet.Name = myname
Sheets("TABLE").Select
Union(Range(Cells(5, "B"), Cells(5 + xx, "B")), Range(Cells(5, "F"), Cells(5 + xx, "F")), _
Range(Cells(5, "E"), Cells(5 + xx, "E"))).Select
myrange = Selection.Address
myrelodds = Range(Cells(5, "F"), Cells(5 + xx, "F")).Address
mycolumnrange = Range(Cells(5, "E"), Cells(5 + xx, "E")).Address
mysheetname = ActiveSheet.Name
Application.CutCopyMode = False
Charts.Add
mychart = ActiveChart.Name
ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:= _
"Lines on 2 Axes"
ActiveChart.SetSourceData Source:=Sheets(mysheetname).Range(myrange), PlotBy:=xlColumns
ActiveChart.SeriesCollection(1).Values = Sheets("TABLE").Range(myrelodds)
ActiveChart.SeriesCollection(2).Values = Sheets("TABLE").Range(mycolumnrange)
ActiveChart.SeriesCollection(2).ChartType = xlColumnClustered
ActiveChart.SeriesCollection(1).Name = "=""Target Density"""
ActiveChart.SeriesCollection(2).Name = "=""Dist'n"""
ActiveChart.HasLegend = False
ActiveChart.HasDataTable = True
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = myname
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Parameter"
.Axes(xlValue, xlSecondary).HasTitle = True
.Axes(xlValue, xlSecondary).AxisTitle.Characters.Text = "Dist'n"
End With
With ActiveChart.Axes(xlValue, xlSecondary)
.MinimumScaleIsAuto = True
.MaximumScale = 1
.MinorUnitIsAuto = True
.MajorUnitIsAuto = True
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
ActiveChart.Location Where:=xlLocationAsObject, Name:=myname
With ActiveSheet.ChartObjects(yy)
.Width = xwidth
.Height = xheight
.Left = cl
.Top = Rw
End With
Sheets("TEMPLATE").Select
Range("O5:R103").Select
Selection.ClearContents
'If it isn't the first predictor, then copy the standard table to TABLE sheet
Else
Cells(3 + zz + 2 * yy, "A").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Sheets("TEMPLATE").Select
Range("B4", "G4").Select
Selection.Copy
Sheets("TABLE").Select
Cells(4 + zz + 2 * yy + xx, "A").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
'Add charts for the rest predictors
myname = Cells(3 + zz + 2 * yy, "A").Value
Application.ScreenUpdating = False
Worksheets.Add
ActiveSheet.Move After:=Sheets(ActiveWorkbook.Sheets.count)
Application.ScreenUpdating = True ' Enables screen refreshing.
ActiveSheet.Name = myname
Sheets("TABLE").Select
Union(Range(Cells(3 + zz + 2 * yy, "B"), Cells(3 + zz + 2 * yy + xx, "B")), _
Range(Cells(3 + zz + 2 * yy, "F"), Cells(3 + zz + 2 * yy + xx, "F")), _
Range(Cells(3 + zz + 2 * yy, "E"), Cells(3 + zz + 2 * yy + xx, "E"))).Select
myrange = Selection.Address
myrelodds = Range(Cells(3 + zz + 2 * yy, "F"), Cells(3 + zz + 2 * yy + xx, "F")).Address
mycolumnrange = Range(Cells(3 + zz + 2 * yy, "E"), Cells(3 + zz + 2 * yy + xx, "E")).Address
mysheetname = ActiveSheet.Name
Application.CutCopyMode = False
Charts.Add
mychart = ActiveChart.Name
ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:= _
"Lines on 2 Axes"
ActiveChart.SetSourceData Source:=Sheets(mysheetname).Range(myrange), PlotBy:=xlColumns
ActiveChart.SeriesCollection(1).Values = Sheets("TABLE").Range(myrelodds)
ActiveChart.SeriesCollection(2).Values = Sheets("TABLE").Range(mycolumnrange)
ActiveChart.SeriesCollection(2).ChartType = xlColumnClustered
ActiveChart.SeriesCollection(1).Name = "=""Target Density"""
ActiveChart.SeriesCollection(2).Name = "=""Dist'n"""
ActiveChart.HasLegend = False
ActiveChart.HasDataTable = True
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = myname
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Parameter"
.Axes(xlValue, xlSecondary).HasTitle = True
.Axes(xlValue, xlSecondary).AxisTitle.Characters.Text = "Dist'n"
End With
With ActiveChart.Axes(xlValue, xlSecondary)
.MinimumScaleIsAuto = True
.MaximumScale = 1
.MinorUnitIsAuto = True
.MajorUnitIsAuto = True
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
ActiveChart.Location Where:=xlLocationAsObject, Name:=myname
With ActiveSheet.ChartObjects(1)
.Width = xwidth
.Height = xheight
.Left = cl
.Top = Rw
End With
Sheets("TEMPLATE").Select
Range("O5:R103").Select
Selection.ClearContents
End If
zz = zz + xx + 1
End If
Wend
Next i
Sheets("TEMPLATE").Select
Range("A1").Select
Sheets("TABLE").Select
Range("A1").Select
Sheets("DATA").Select
Range("G1").Select
Sheets("INSTRUCTION").Select
Cells(21, "G").Select
MsgBox ("Tables and Charts are done!")
End If
End If
End Sub
=====================
Thanks for taking time to read this,
M
Error received on line:
ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:= _
"Lines on 2 Axes"
Error is noted as:
Run-time error '-2147467259 (80004005)': The specified diemnion is not valid for the current chart type.
==================================
Sub TableChart_Maker()
'
' Macro recorded 1/16/2008
'
Dim xx As Integer, yy As Integer, zz As Integer
Dim recdcount As Integer, predcount As Integer
Dim xwidth As Integer, xheight As Integer, Rw As Integer, cl As Integer
Dim sheetn As Integer
predcount = Range("TOTPREDTCONT").Value
recdcount = Range("TOTRECDCONT") + 3
sheetn = Sheets.count
' If the number of records is greater than 997, then the macro will stop
If recdcount > 4997 Then
Sheets("DATA").Select
Range("A5001").Select
MsgBox "Paste formulars in rows below 5000, and revise formulars in I to K", vbExclamation
End
Else
'if the number of records is less than 997, then the macro will product the tables and charts
If sheetn > 4 Then
MsgBox "Please delete all chart tabs!", vbExclamation
End
Else
' Chart size and position
xwidth = 580
xheight = 370
Rw = 5
cl = 5
zz = 0
For i = 4 To recdcount
Sheets("DATA").Select
'Extract data of each preditor from DATA sheet and then copy them to 'TEMPLATE' sheet
While Cells(i, "F").Value = "BL"
xx = Cells(i, "D").Value - 1
yy = Cells(i, "B").Value
If xx > 94 Then
Sheets("TEMPLATE").Select
Range("A1").Select
MsgBox "Paste formulars in rows below 100 and revise formulars in line 4", vbExclamation
End
Else
Cells(i, "F").Select
ActiveCell.Offset(-xx, 1).Activate
Range(ActiveCell, Cells(i, "J")).Select
Selection.Copy
Sheets("TEMPLATE").Select
Range("O5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range(Cells(5, "B"), Cells(5 + xx, "G")).Select
Selection.Copy
Sheets("TABLE").Select
'Copy the table of the first preditor from TEMPLATE sheet to TABLE sheet.
If yy = 1 Then
Range("A5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Sheets("TEMPLATE").Select
Range("B4", "G4").Select
Selection.Copy
Sheets("TABLE").Select
Cells(6 + xx, "A").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
'Add chart for the first predictor
myname = Cells(5, "A").Value
Application.ScreenUpdating = False
Worksheets.Add
ActiveSheet.Move After:=Sheets(ActiveWorkbook.Sheets.count)
Application.ScreenUpdating = True ' Enables screen refreshing.
ActiveSheet.Name = myname
Sheets("TABLE").Select
Union(Range(Cells(5, "B"), Cells(5 + xx, "B")), Range(Cells(5, "F"), Cells(5 + xx, "F")), _
Range(Cells(5, "E"), Cells(5 + xx, "E"))).Select
myrange = Selection.Address
myrelodds = Range(Cells(5, "F"), Cells(5 + xx, "F")).Address
mycolumnrange = Range(Cells(5, "E"), Cells(5 + xx, "E")).Address
mysheetname = ActiveSheet.Name
Application.CutCopyMode = False
Charts.Add
mychart = ActiveChart.Name
ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:= _
"Lines on 2 Axes"
ActiveChart.SetSourceData Source:=Sheets(mysheetname).Range(myrange), PlotBy:=xlColumns
ActiveChart.SeriesCollection(1).Values = Sheets("TABLE").Range(myrelodds)
ActiveChart.SeriesCollection(2).Values = Sheets("TABLE").Range(mycolumnrange)
ActiveChart.SeriesCollection(2).ChartType = xlColumnClustered
ActiveChart.SeriesCollection(1).Name = "=""Target Density"""
ActiveChart.SeriesCollection(2).Name = "=""Dist'n"""
ActiveChart.HasLegend = False
ActiveChart.HasDataTable = True
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = myname
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Parameter"
.Axes(xlValue, xlSecondary).HasTitle = True
.Axes(xlValue, xlSecondary).AxisTitle.Characters.Text = "Dist'n"
End With
With ActiveChart.Axes(xlValue, xlSecondary)
.MinimumScaleIsAuto = True
.MaximumScale = 1
.MinorUnitIsAuto = True
.MajorUnitIsAuto = True
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
ActiveChart.Location Where:=xlLocationAsObject, Name:=myname
With ActiveSheet.ChartObjects(yy)
.Width = xwidth
.Height = xheight
.Left = cl
.Top = Rw
End With
Sheets("TEMPLATE").Select
Range("O5:R103").Select
Selection.ClearContents
'If it isn't the first predictor, then copy the standard table to TABLE sheet
Else
Cells(3 + zz + 2 * yy, "A").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Sheets("TEMPLATE").Select
Range("B4", "G4").Select
Selection.Copy
Sheets("TABLE").Select
Cells(4 + zz + 2 * yy + xx, "A").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
'Add charts for the rest predictors
myname = Cells(3 + zz + 2 * yy, "A").Value
Application.ScreenUpdating = False
Worksheets.Add
ActiveSheet.Move After:=Sheets(ActiveWorkbook.Sheets.count)
Application.ScreenUpdating = True ' Enables screen refreshing.
ActiveSheet.Name = myname
Sheets("TABLE").Select
Union(Range(Cells(3 + zz + 2 * yy, "B"), Cells(3 + zz + 2 * yy + xx, "B")), _
Range(Cells(3 + zz + 2 * yy, "F"), Cells(3 + zz + 2 * yy + xx, "F")), _
Range(Cells(3 + zz + 2 * yy, "E"), Cells(3 + zz + 2 * yy + xx, "E"))).Select
myrange = Selection.Address
myrelodds = Range(Cells(3 + zz + 2 * yy, "F"), Cells(3 + zz + 2 * yy + xx, "F")).Address
mycolumnrange = Range(Cells(3 + zz + 2 * yy, "E"), Cells(3 + zz + 2 * yy + xx, "E")).Address
mysheetname = ActiveSheet.Name
Application.CutCopyMode = False
Charts.Add
mychart = ActiveChart.Name
ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:= _
"Lines on 2 Axes"
ActiveChart.SetSourceData Source:=Sheets(mysheetname).Range(myrange), PlotBy:=xlColumns
ActiveChart.SeriesCollection(1).Values = Sheets("TABLE").Range(myrelodds)
ActiveChart.SeriesCollection(2).Values = Sheets("TABLE").Range(mycolumnrange)
ActiveChart.SeriesCollection(2).ChartType = xlColumnClustered
ActiveChart.SeriesCollection(1).Name = "=""Target Density"""
ActiveChart.SeriesCollection(2).Name = "=""Dist'n"""
ActiveChart.HasLegend = False
ActiveChart.HasDataTable = True
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = myname
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Parameter"
.Axes(xlValue, xlSecondary).HasTitle = True
.Axes(xlValue, xlSecondary).AxisTitle.Characters.Text = "Dist'n"
End With
With ActiveChart.Axes(xlValue, xlSecondary)
.MinimumScaleIsAuto = True
.MaximumScale = 1
.MinorUnitIsAuto = True
.MajorUnitIsAuto = True
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
ActiveChart.Location Where:=xlLocationAsObject, Name:=myname
With ActiveSheet.ChartObjects(1)
.Width = xwidth
.Height = xheight
.Left = cl
.Top = Rw
End With
Sheets("TEMPLATE").Select
Range("O5:R103").Select
Selection.ClearContents
End If
zz = zz + xx + 1
End If
Wend
Next i
Sheets("TEMPLATE").Select
Range("A1").Select
Sheets("TABLE").Select
Range("A1").Select
Sheets("DATA").Select
Range("G1").Select
Sheets("INSTRUCTION").Select
Cells(21, "G").Select
MsgBox ("Tables and Charts are done!")
End If
End If
End Sub
=====================
Thanks for taking time to read this,
M