Screenupdating off causes procedure to execute incorrectly??!!

[StuLythgoe]

I've written the below procedure to take an Impromptu exported workbook and add a chart and some formatting. When I run the procedure as outlined below with screen updating on it works fine, but when I turn screen updating off during the procedure it causes the following lines to execute incorrectly

ActiveChart.Axes(xlValue).Select
    Selection.TickLabels.AutoScaleFont = True
    Selection.TickLabels.Font.Size = 8
 
    ActiveChart.Axes(xlCategory).Select
    Selection.TickLabels.AutoScaleFont = True
    Selection.TickLabels.Font.Size = 8
 
    ActiveChart.SeriesCollection(1).ApplyDataLabels AutoText:=True, ShowValue:=True
    ActiveChart.SeriesCollection(1).DataLabels.Select
    Selection.AutoScaleFont = True
    With Selection
        .Font.Size = 8
        .Orientation = xlUpward
    End With

it actions everything apart from the font size change. Has anyone any idea why the procedure would perform differently with screenupdating turned off??

Sub Maintenance()
'
    Dim Path As String
    Dim Book1 As String, EndCell As String, TotalRange As String, TotalRange2 As String
    Dim StartTime As Double
 
    Path = InputBox("Enter details of path to folder containing report - (eg. S:\Stocks & Systems\Systems\Monday Reports\280811", "Path to File?")
 
        If Path = "" Then Exit Sub
 
    Book1 = Path & "\Component Summary.xls"
 
    'Application.ScreenUpdating = False
 
    StartTime = Timer
 
    On Error GoTo 1:
 
    Workbooks.Open Filename:=[Book1]
 
1:
        If Err.Number = "1004" Then Exit Sub
 
    Sheets(1).Name = "Sheet1"
    Range("A:A").End(xlDown).Select
    ActiveCell.Offset(0, 1).Range("A1").Select
        EndCell = ActiveCell.Range("A1").Address
        TotalRange = "B2" & ":" & EndCell
        TotalRange2 = "A1" & ":" & EndCell
 
    Columns("B:B").Insert Shift:=xlToRight
    Range("B1").Value = "Total Cost"
    Range("B2").Value = "=RC[1]*1"
    Range("B2").AutoFill Destination:=Range(TotalRange)
    Columns("A:C").Sort Key1:=Range("B2"), Order1:=xlDescending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    Columns("B:B").ColumnWidth = 9.6
    Columns("C:C").EntireColumn.Hidden = True
 
    Range(TotalRange2).Select
    Range(EndCell).Activate
    Sheets.Add
    Charts.Add
    ActiveChart.ChartType = xlColumnClustered
    ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range(TotalRange2), PlotBy _
        :=xlColumns
    ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet2"
    With ActiveChart
        .HasTitle = True
        .ChartTitle.Characters.Text = "Total Cost"
        .Axes(xlCategory, xlPrimary).HasTitle = False
        .Axes(xlValue, xlPrimary).HasTitle = False
    End With
    ActiveSheet.Shapes("Chart 1").IncrementLeft -219#
    ActiveSheet.Shapes("Chart 1").IncrementTop -141.75
    ActiveSheet.Shapes("Chart 1").ScaleWidth 1.95, msoFalse, msoScaleFromTopLeft
    ActiveSheet.Shapes("Chart 1").ScaleHeight 1.93, msoFalse, msoScaleFromTopLeft
 
    ActiveChart.Axes(xlValue).MajorGridlines.Delete
    ActiveChart.Legend.Delete
 
    ActiveChart.Axes(xlValue).Select
    Selection.TickLabels.AutoScaleFont = True
    Selection.TickLabels.Font.Size = 8
 
    ActiveChart.Axes(xlCategory).Select
    Selection.TickLabels.AutoScaleFont = True
    Selection.TickLabels.Font.Size = 8
 
    ActiveChart.SeriesCollection(1).ApplyDataLabels AutoText:=True, ShowValue:=True
    ActiveChart.SeriesCollection(1).DataLabels.Select
    Selection.AutoScaleFont = True
    With Selection
        .Font.Size = 8
        .Orientation = xlUpward
    End With
 
    Windows("Component Summary.xls").Activate
    Rows("1:1").Insert Shift:=xlDown
    Rows("1:1").RowHeight = 21
 
    With Rows("1:1").Font
        .Bold = True
        .Name = "Arial"
        .Size = 12
    End With
 
    With Range("A1:S1")
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .MergeCells = True
    End With
 
    ActiveCell.FormulaR1C1 = "Sheet Creation automated using VBA in Excel (in " & Format(Timer - StartTime, "00.00") & " seconds)"
 
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.ChartArea.Select
 
    'Application.ScreenUpdating = True
 
End Sub

 

[Jaye7]

I have had problems with screenupdating where if there are any kinds of formats in the cells, i.e font, borders etc... it causes the script to fall over, or sometimes I haven't selected range("A1") on the new sheet and when copying whole sheet data in it doesn't work as the range is different.

Not ideal and not what you want, however you could have it turn on the screenupdating where it crashes and then turn off again after the last error line, see if that works.