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
it actions everything apart from the font size change. Has anyone any idea why the procedure would perform differently with screenupdating turned off??
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