Hello,
I am a loss. I wrote a Macro to reformat a Chart Tab in a very specific format. I have two problems. The first is I must run the macro twice for the format to be correct. The plot area is fixed on the second running of the macro.
here is the code in all it's glory and yes it needs to run for any version of Excel starting with 2003 or newer (we use 2003 and 2007).
I have search the code to determine why, but to no avail. I need to give this macro to other and I really do not want to add a second running of the sub. If anyone could review this to see what I am missing, I would eternally grateful.
The second issue deals with the axis. Instead of letting Excel determine where the axis is, I do need to move them. The issue is that the titles are not centered along the axis. I thought just moving the category (horizontal) axis down and the value (vertical) axis left would leave the centered, but it does not. Any ideas would be helpful.
Thanks.
Mike Virostko
I am a loss. I wrote a Macro to reformat a Chart Tab in a very specific format. I have two problems. The first is I must run the macro twice for the format to be correct. The plot area is fixed on the second running of the macro.
here is the code in all it's glory and yes it needs to run for any version of Excel starting with 2003 or newer (we use 2003 and 2007).
Code:
Option Base 1
Option Explicit
Sub Run_RFMt_DS_Chart()
Dim ExcelVersion As Integer
Dim mlabel As Integer
mlabel = 1
ExcelVersion = Application.Version
Call RF_DS_Chart_Page(ExcelVersion, mlabel)
End Sub
Sub RF_DS_Chart_Page(ExcelVersion As Integer, mlabel As Integer)
Dim ChtType As Variant
Dim borderweight As Variant
'
borderweight = 2
'
' Reformat_Chart_page Macro
' Macro recorded 1/27/2006 by Michael J. Virostko
' Revised 7/14/2011 for Excel 2007 speed.
' Set up Overall Page size along with the Header and Footers
'
ActiveWindow.Zoom = 100
ActiveChart.Activate
Application.ScreenUpdating = False ' do all transfers in the background
Application.DisplayStatusBar = True
'
' Set Print Page Setup
'
ActiveChart.ChartType = xlXYScatterSmoothNoMarkers ' Force Scatter graph to me smooth lines
ChtType = ActiveChart.ChartType
With ActiveChart.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = _
"&""Times New Roman,Bold""Integrated Device Technologies" & Chr(10) & "&""Times New Roman,Regular""&8&F - &A"
.CenterFooter = ""
.RightFooter = "&""Times New Roman,Bold""&10Page &P" & Chr(10) & "Printed " & "&D"
.LeftMargin = Application.InchesToPoints(0.25)
.RightMargin = Application.InchesToPoints(0.25)
.TopMargin = Application.InchesToPoints(0.25)
.BottomMargin = Application.InchesToPoints(0.75)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.ChartSize = xlFullPage
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.BlackAndWhite = False
.Zoom = 100
End With
'
' For Excel 2007, Need to define the Chart Area size and area
' Define the Chart area (no border)
'
If ExcelVersion >= 12 Then
ActiveChart.ChartArea.Select
With Selection
.Top = 0
.Left = 0
.Width = 745 '735
.Height = 530 '530
.Border.LineStyle = xlNone
.Interior.ColorIndex = xlNone
End With
End If
'
' Define the area for the Plot Area within the Chart Area
'
ActiveChart.PlotArea.Select
With Selection
.Top = 40
.Left = 60
.Width = 685
.Height = 445
End With
'
' Format the plot area for white background, solid border.
'
ActiveChart.PlotArea.Border.Weight = borderweight + 1
'
' Set up graph to have only major gridlines
'
With ActiveChart.Axes(xlCategory)
.HasMajorGridlines = True
.HasMinorGridlines = False
End With
With ActiveChart.Axes(xlValue, xlPrimary)
.HasMajorGridlines = True
.HasMinorGridlines = False
End With
'
' Force no Chart Title
'
ActiveChart.HasTitle = False
'
' xlCategory represents x
' xlvalue represents y
'
' Format the y-axis (xlValue)
'
If ActiveChart.Axes(xlValue, xlPrimary).HasTitle = False Then
ActiveChart.Axes(xlValue, xlPrimary).HasTitle = True
End If
'
' Set up major gridlines to be a continous line
'
ActiveChart.Axes(xlValue, xlPrimary).MajorGridlines.Select
With Selection.Border
.ColorIndex = 1
.Weight = 1 ' use a number instead of XL variable
.LineStyle = xlContinuous 'xlDot
End With
ActiveChart.Axes(xlValue, xlPrimary).AxisTitle.Select
With Selection.Font
.Name = "Tahoma"
.FontStyle = "Bold"
.Size = 22
.ColorIndex = 1
.Background = xlAutomatic
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlTop
.ReadingOrder = xlContext
.Orientation = xlUpward
If mlabel <> 0 Then .Left = 10
End With
ActiveChart.Axes(xlValue, xlPrimary).Select
With Selection
.Crosses = xlCustom
.CrossesAt = -200
.Format.Line.Weight = 2 ' xlHairline
.TickLabelPosition = xlLow
.Border.Color = RGB(0, 0, 0)
.Crosses = xlCustom
.MajorTickMark = xlCross
.MinorTickMark = xlInside
.TickLabelPosition = xlNextToAxis
End With
With Selection.TickLabels.Font
.Name = "Tahoma"
.FontStyle = "Bold"
.Size = 18
.ColorIndex = 1
End With
'
' Format the x-axis (XlCaterory)
'
If ActiveChart.Axes(xlCategory).HasTitle = False Then
ActiveChart.Axes(xlCategory).HasTitle = True
End If
ActiveChart.Axes(xlCategory).MajorGridlines.Select
With Selection.Border
.ColorIndex = 1 ' 57
.Weight = xlThin
.LineStyle = xlContinuous 'xlDot
End With
ActiveChart.Axes(xlCategory).AxisTitle.Select
With Selection.Font
.Name = "Tahoma"
.FontStyle = "Bold"
.Size = 22
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.ReadingOrder = xlContext
.Orientation = xlHorizontal
If mlabel <> 0 Then .Top = 480
End With
ActiveChart.Axes(xlCategory).Select
With Selection
.Crosses = xlCustom
.CrossesAt = -200
.Format.Line.Weight = borderweight ' xlThin ' xlHairline
.TickLabelPosition = xlLow
.Border.Color = RGB(0, 0, 0)
.Crosses = xlCustom
.MajorTickMark = xlCross
.MinorTickMark = xlInside
.TickLabelPosition = xlNextToAxis
End With
With Selection.TickLabels.Font
.Name = "Tahoma"
.FontStyle = "Bold"
.Size = 18
.ColorIndex = 1
.Background = xlAutomatic
End With
'
' Force Legend to have a NO border, white solid fill.
'
ActiveChart.Legend.Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlLineStyleNone ' xlContinuous
End With
Selection.Shadow = False
With Selection.Interior
.ColorIndex = 2
.PatternColorIndex = 1
.Pattern = xlSolid
End With
Selection.AutoScaleFont = False
With Selection.Font
.Name = "Tahoma"
.FontStyle = "Bold"
.Size = 14
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 1
.Background = xlTransparent
End With
Application.ScreenUpdating = True ' do all transfers in the background
End Sub
I have search the code to determine why, but to no avail. I need to give this macro to other and I really do not want to add a second running of the sub. If anyone could review this to see what I am missing, I would eternally grateful.
The second issue deals with the axis. Instead of letting Excel determine where the axis is, I do need to move them. The issue is that the titles are not centered along the axis. I thought just moving the category (horizontal) axis down and the value (vertical) axis left would leave the centered, but it does not. Any ideas would be helpful.
Thanks.
Mike Virostko