Header and Footer don\'t appear on chart page in Excel I have made the commands

Question

Header and Footer don't appear on chart page in Excel

I have made the commands for what is entered in the headers and footers on charts as they are made, using VBA; only they don't actually appear on the print page for the charts. It could be that I need to adjust the actual chart plot area or the dimension definitions or something but I don't know how to do that either. Below is the code for the charts. Also, think I'm missing 'Title' in the sub definition line. Help please...

Dimensions/Variables:

TankNum

TankHeight

height (height of the portion of data I am looking at)

StartDate

Title (name of my company)

Company (name of company charts will be for)

sheetname (name of the activesheet code is currently working on/with)

Sub deviationcharts(increment As Integer, Company As String, StartDate As String, TankNum As String, sheetname As String, height As Integer)
  
ActiveSheet.Range("C1:C" & increment).Select
Charts.Add
ActiveChart.ChartType = xlXYScatterLines
ActiveChart.SetSourceData Source:=Sheets(sheetname).Range("C1:D" & (increment + 1)), _
PlotBy:=xlColumns
ActiveChart.SeriesCollection(1).XValues = "=" & sheetname & "!C1:C" & (increment + 1)
ActiveChart.Location Where:=xlLocationAsObject, Name:=sheetname
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Plate deviation at height " & _
(Int(height)) & "m" & Chr(10) & "Tank number " & (Int(TankNum))
.ChartTitle.Characters.Font.Size = 12
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Angle (degrees)"
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Font.Size = 8
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Deviation (mm)"
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Font.Size = 8
End With
With ActiveChart
.HasAxis(xlCategory, xlPrimary) = True
.HasAxis(xlValue, xlPrimary) = True
End With
ActiveChart.ChartArea.Select
ActiveSheet.Shapes("Chart 1").ScaleHeight 1.7, msoFalse, msoScaleFromBottomRight
ActiveSheet.Shapes("Chart 1").ScaleWidth 1.83, msoFalse, msoScaleFromBottomRight
ActiveChart.Axes(xlCategory, xlPrimary).CategoryType = xlAutomatic
With ActiveChart.Axes(xlCategory)
.HasMajorGridlines = True
.HasMinorGridlines = True
End With
With ActiveChart.Axes(xlValue)
.HasMajorGridlines = True
.HasMinorGridlines = True
End With
ActiveChart.HasLegend = False
ActiveChart.PlotArea.Select
With Selection.Border
.ColorIndex = 16
.Weight = xlThin
.LineStyle = xlContinuous
End With
Selection.Interior.ColorIndex = xlNone
ActiveChart.Axes(xlCategory).Select
Selection.TickLabels.NumberFormat = "0"
ActiveChart.Axes(xlValue).Select
With Selection.Border
.Weight = xlHairline
.LineStyle = xlAutomatic
End With
With Selection
.MajorTickMark = xlOutside
.MinorTickMark = xlOutside
.TickLabelPosition = xlNextToAxis
End With
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
.MinimumScale = -50
.MaximumScale = 50
.MinorUnit = 1
.MajorUnit = 5
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
End With
ActiveChart.Axes(xlCategory).Select
With ActiveChart.Axes(xlCategory)
.MinimumScale = 0
.MaximumScale = 360
.MinorUnit = 10
.MajorUnit = 90
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
End With
ActiveChart.Axes(xlValue).MinorGridlines.Select
With Selection.Border
.ColorIndex = 57
.Weight = xlHairline
.LineStyle = xlDot
End With
ActiveChart.SeriesCollection(1).Select
With Selection.Border
.ColorIndex = 3
.Weight = xlThin
.LineStyle = xlContinuous
End With
With Selection
.MarkerBackgroundColorIndex = 3
.MarkerForegroundColorIndex = 3
.MarkerStyle = xlDiamond
.Smooth = False
.MarkerSize = 5
.Shadow = False
End With
With ActiveChart.PageSetup
.LeftHeader = "&" & Company
.CenterHeader = ""
.LeftFooter = "&" & Title
.CenterFooter = ""
.RightFooter = "Date: " & StartDate
.LeftMargin = Application.InchesToPoints(1.25)
.RightMargin = Application.InchesToPoints(1.25)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(1)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.ChartSize = xlScreenSize
.CenterHorizontally = True
.CenterVertically = True
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.BlackAndWhite = False
.Zoom = 100
End With
ActiveChart.ChartArea.Select

End Sub

Explanation / Answer

Hey u,

I think the .LeftHeader line should just be

.LeftHeader = Company

and the .LeftFooter line should be

.LeftFooter = Title

Including "&" at the beginning is a mistake - "&" in headers and footers is interpreted specially.
For example "&P" gives the page number and "&D" gives the date.

As you say, Title is not defined within this procedure.
You will need to decide what it should contain and either set it in the procedure or pass it in as a parameter to the procedure.

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.