Results 1 to 2 of 2
  1. #1
    ili_sophia is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Oct 2017
    Posts
    1

    Method of range of object Global failed

    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

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    You dont recreate the chart.
    The chart is already there. You just post data over the old data range.
    then adjust the existing chart range to match the data.

    you cant create a new chart everytime, because all the names get incremented:
    1st time , chart1 is created
    2nd time, chart2 is created ,maybe. you can no longer create chart1.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 9
    Last Post: 11-17-2020, 11:00 AM
  2. Replies: 3
    Last Post: 09-18-2014, 12:24 PM
  3. Replies: 11
    Last Post: 06-30-2014, 11:34 PM
  4. Replies: 1
    Last Post: 07-13-2012, 07:58 PM
  5. Method InsideHeight of object PlotArea failed
    By dgardineer in forum Programming
    Replies: 1
    Last Post: 10-26-2011, 01:52 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums