Screenupdating off causes procedure to execute incorrectly??!!

StuLythgoe

New Member
Joined
Oct 2, 2011
Messages
36
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

Code:
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??

Code:
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
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Jaye7

Well-known Member
Joined
Jul 7, 2010
Messages
1,056
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,193
Messages
5,594,767
Members
413,933
Latest member
Msar5586

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top