Hi there, i have a userform that allows the users to select the dates that they want and it will show the summary of the data in the subform.
My userform is able to export the summary data but i would like it to also create a graph based on the data.
I have recorded a macro for it and added in to the export command button. The first time i click on the export command button, the graph is able to successful be created. However if i try to use the export command button a second time, it gives me the error of Method of range of object Global failed. the Data is exported but the chart is not
Does anyone know why does this happen?
also is there a way to create the chart without selecting the specific cell range as the users selection of dates might differ
my code for my command button:
Private Sub DyeingExportExcel_Click()
Dim sht As Object
If Me.Dirty Then Me.Dirty = False
Dim rsClone As DAO.Recordset
Set rsClone = Me.subDyeing.Form.RecordsetClone
If (rsClone.BOF And rsClone.EOF) Then
MsgBox "No records found."
Set rsClone = Nothing
Exit Sub
End If
Dim xlApp As Object
Set xlApp = CreateObject("Excel.Application")
With xlApp
.Visible = True
.Workbooks.Add
Set sht = .Sheets("Sheet1")
sht.Activate
' put the data first
rsClone.MoveFirst
sht.Range("A2").CopyFromRecordset rsClone
' next put the Column Header
For i = 1 To rsClone.Fields.count
sht.Cells(1, i).Value = rsClone.Fields(i - 1).Name
Next i
With sht
Dim lngRow As Long
' instead well use the Recordcount
lngRow = rsClone.RecordCount + 1
.Range("$A$" & lngRow + 2).Value = "Total:"
' 1. Budget (Sample Machine)
.Range("$B$" & lngRow + 2).Formula = "=Sum($B$2:$B$" & lngRow & ")"
' 2. Actual (Sample Machine)
.Range("$C$" & lngRow + 2).Formula = "=Sum($C$2:$C$" & lngRow & ")"
.Range("$D$" & lngRow + 2).Formula = "=Sum($D$2:$D$" & lngRow & ")"
' 3. Budget (Mass Machine)
.Range("$E$" & lngRow + 2).Formula = "=Sum($E$2:$E$" & lngRow & ")"
' 4. Actual (Mass Machine)
.Range("$F$" & lngRow + 2).Formula = "=Sum($F$2:$F$" & lngRow & ")"
.Range("$G$" & lngRow + 2).Formula = "=Sum($G$2:$G$" & lngRow & ")"
' 5. Hours
.Range("$H$" & lngRow + 2).Formula = "=Sum($H$2:$H$" & lngRow & ")"
' 6. Mass Machine Capacity
.Range("$I$" & lngRow + 2).Formula = "=Sum($I$2:$I$" & lngRow & ")"
' 7. Mass Machine Used
.Range("$J$" & lngRow + 2).Formula = "=Sum($J$2:$J$" & lngRow & ")"
' insert calculated columns
Dim j As Integer
xlApp.Columns("D").Select
xlApp.Selection.Insert Shift:=-4152
xlApp.Selection.NumberFormat = "0%"
.Range("D1") = "Sample Machine Efficiency (LBS)"
For j = 2 To lngRow
.Range("D" & j).Formula = "=IF(C" & j & "=0,0,C" & j & "/B" & j & ")"
.Range("$D$" & lngRow + 2).Formula = "=Sum(C" & lngRow + 2 & "/B" & lngRow + 2 & ")"
Next
xlApp.Columns("G:G").Select
xlApp.Selection.Insert Shift:=-4152
xlApp.Selection.NumberFormat = "0%"
.Range("G1") = "Mass Machine Efficiency"
For j = 2 To lngRow
.Range("G" & j).Formula = "=IF(F" & j & "=0,0,F" & j & "/E" & j & ")"
.Range("$G$" & lngRow + 2).Formula = "=Sum(F" & lngRow + 2 & "/E" & lngRow + 2 & ")"
Next
xlApp.Columns("K:K").Select
xlApp.Selection.Insert Shift:=-4152
xlApp.Selection.NumberFormat = "0%"
.Range("K1") = "Mass Machine Utilization"
For j = 2 To lngRow
.Range("K" & j).Formula = "=IF(J" & j & "=0,0,J" & j & "/I" & j & ")"
.Range("$K$" & lngRow + 2).Formula = "=AVERAGE($K$2:$K$" & lngRow & ")"
Next
End With
' autofit Column
xlApp.Cells.EntireColumn.AutoFit
' NOW, the formatting of each column
xlApp.Columns("A:A").Select
xlApp.Selection.NumberFormat = "d-mmm-yy"
xlApp.Columns("C:C").Select
xlApp.Selection.NumberFormat = "#,##0"
xlApp.Columns("F:F").Select
xlApp.Selection.NumberFormat = "#,##0"
xlApp.Columns("E:E").Select
xlApp.Selection.NumberFormat = "#,##0"
xlApp.Columns("H:H").Select
xlApp.Selection.NumberFormat = "#,##0"
xlApp.Cells(2, 1).Select
' Freeze the Column Header
xlApp.ActiveWindow.FreezePanes = True
End With
Call DyeingChart
End Sub
My macro to create the chart:
Sub DyeingChart()
'
' Macro4 Macro
'
'
Range("A18").Select
ActiveSheet.Shapes.AddChart2(322, xlColumnClustered).Select
ActiveChart.FullSeriesCollection(1).ChartType = xlColumnClustered
ActiveChart.FullSeriesCollection(2).ChartType = xlColumnClustered
ActiveChart.FullSeriesCollection(3).ChartType = xlLine
ActiveChart.FullSeriesCollection(3).AxisGroup = 2
ActiveChart.ChartTitle.Select
ActiveChart.ChartTitle.Text = "Sample Production Output Efficiency"
Selection.Format.TextFrame2.TextRange.Characters.T ext = _
"Sample Production Output Efficiency"
With Selection.Format.TextFrame2.TextRange.Characters(1 , 35).ParagraphFormat
.TextDirection = msoTextDirectionLeftToRight
.Alignment = msoAlignCenter
End With
With Selection.Format.TextFrame2.TextRange.Characters(1 , 17).Font
.BaselineOffset = 0
.Bold = msoFalse
.NameComplexScript = "+mn-cs"
.NameFarEast = "+mn-ea"
.Fill.Visible = msoTrue
.Fill.ForeColor.RGB = RGB(89, 89, 89)
.Fill.Transparency = 0
.Fill.Solid
.Size = 14
.Italic = msoFalse
.Kerning = 12
.Name = "+mn-lt"
.UnderlineStyle = msoNoUnderline
.Spacing = 0
.Strike = msoNoStrike
End With
With Selection.Format.TextFrame2.TextRange.Characters(1 8, 18).Font
.BaselineOffset = 0
.Bold = msoFalse
.NameComplexScript = "+mn-cs"
.NameFarEast = "+mn-ea"
.Fill.Visible = msoTrue
.Fill.ForeColor.RGB = RGB(89, 89, 89)
.Fill.Transparency = 0
.Fill.Solid
.Size = 14
.Italic = msoFalse
.Kerning = 12
.Name = "+mn-lt"
.UnderlineStyle = msoNoUnderline
.Spacing = 0
.Strike = msoNoStrike
End With
ActiveChart.ChartArea.Select
Range("E1:G8").Select
Range("G1").Activate
ActiveSheet.Shapes.AddChart2(322, xlColumnClustered).Select
ActiveChart.FullSeriesCollection(1).ChartType = xlColumnClustered
ActiveChart.FullSeriesCollection(2).ChartType = xlColumnClustered
ActiveChart.FullSeriesCollection(3).ChartType = xlLine
ActiveChart.FullSeriesCollection(3).AxisGroup = 2
ActiveChart.ChartTitle.Select
ActiveChart.ChartTitle.Text = "Mass Pro "
Selection.Format.TextFrame2.TextRange.Characters.T ext = "Mass Pro "
With Selection.Format.TextFrame2.TextRange.Characters(1 , 9).ParagraphFormat
.TextDirection = msoTextDirectionLeftToRight
.Alignment = msoAlignCenter
End With
With Selection.Format.TextFrame2.TextRange.Characters(1 , 4).Font
.BaselineOffset = 0
.Bold = msoFalse
.NameComplexScript = "+mn-cs"
.NameFarEast = "+mn-ea"
.Fill.Visible = msoTrue
.Fill.ForeColor.RGB = RGB(89, 89, 89)
.Fill.Transparency = 0
.Fill.Solid
.Size = 14
.Italic = msoFalse
.Kerning = 12
.Name = "+mn-lt"
.UnderlineStyle = msoNoUnderline
.Spacing = 0
.Strike = msoNoStrike
End With
With Selection.Format.TextFrame2.TextRange.Characters(5 , 5).Font
.BaselineOffset = 0
.Bold = msoFalse
.NameComplexScript = "+mn-cs"
.NameFarEast = "+mn-ea"
.Fill.Visible = msoTrue
.Fill.ForeColor.RGB = RGB(89, 89, 89)
.Fill.Transparency = 0
.Fill.Solid
.Size = 14
.Italic = msoFalse
.Kerning = 12
.Name = "+mn-lt"
.UnderlineStyle = msoNoUnderline
.Spacing = 0
.Strike = msoNoStrike
End With
ActiveChart.ChartArea.Select
ActiveChart.Parent.Delete
Range("A1:A8,E1:G8").Select
Range("G1").Activate
ActiveSheet.Shapes.AddChart2(322, xlColumnClustered).Select
ActiveChart.FullSeriesCollection(1).ChartType = xlColumnClustered
ActiveChart.FullSeriesCollection(2).ChartType = xlColumnClustered
ActiveChart.FullSeriesCollection(3).ChartType = xlLine
ActiveChart.FullSeriesCollection(3).AxisGroup = 2
ActiveChart.ChartTitle.Select
ActiveChart.ChartTitle.Text = "Mass Production Output Efficiency"
Selection.Format.TextFrame2.TextRange.Characters.T ext = _
"Mass Production Output Efficiency"
With Selection.Format.TextFrame2.TextRange.Characters(1 , 33).ParagraphFormat
.TextDirection = msoTextDirectionLeftToRight
.Alignment = msoAlignCenter
End With
With Selection.Format.TextFrame2.TextRange.Characters(1 , 15).Font
.BaselineOffset = 0
.Bold = msoFalse
.NameComplexScript = "+mn-cs"
.NameFarEast = "+mn-ea"
.Fill.Visible = msoTrue
.Fill.ForeColor.RGB = RGB(89, 89, 89)
.Fill.Transparency = 0
.Fill.Solid
.Size = 14
.Italic = msoFalse
.Kerning = 12
.Name = "+mn-lt"
.UnderlineStyle = msoNoUnderline
.Spacing = 0
.Strike = msoNoStrike
End With
With Selection.Format.TextFrame2.TextRange.Characters(1 6, 18).Font
.BaselineOffset = 0
.Bold = msoFalse
.NameComplexScript = "+mn-cs"
.NameFarEast = "+mn-ea"
.Fill.Visible = msoTrue
.Fill.ForeColor.RGB = RGB(89, 89, 89)
.Fill.Transparency = 0
.Fill.Solid
.Size = 14
.Italic = msoFalse
.Kerning = 12
.Name = "+mn-lt"
.UnderlineStyle = msoNoUnderline
.Spacing = 0
.Strike = msoNoStrike
End With
End Sub