May 02 2018
02:00 PM
- last edited on
Jul 25 2018
09:59 AM
by
TechCommunityAP
May 02 2018
02:00 PM
- last edited on
Jul 25 2018
09:59 AM
by
TechCommunityAP
Hello,
I am not very educated on using VBA, but I found and wrote the below syntax to auto format chart headers and axis in excel 2016. It works. However, for some reason, this formatting macro does not format box-plot headers. The header font for box plots is always calibri with a font size of 14. I need the header and axis to be Segoe UI with other font sizes. Also, the legend for pie charts does not change to the right size of fonts either.
What I have done so far:
Changed excel's default font to Segoe UI under "Options"
Changed the "Normal" font to Segoe UI
Used the below VBA macro (This works on other charts, but not box plots)
Syntax:
Sub ChartTitleFont()
'Prevents screen refresh whilst macro executes
Application.ScreenUpdating = False
'Run the Error handler "ErrHandler" when an error occurs.
On Error GoTo Errhandler
Dim ChartList As Integer
Dim x As Integer
' Variable chartlist stores a count of all embedded charts.
ChartList = ActiveSheet.ChartObjects.Count
' Increments the counter variable 'X' in a loop.
For x = 1 To ChartList
' Selects the chart object.
ActiveSheet.ChartObjects(x).Select
' Makes chart active.
ActiveSheet.ChartObjects(x).Activate
ActiveChart.ChartTitle.Select
Selection.AutoScaleFont = True
With Selection.Font
.Name = "Segoe UI"
.FontStyle = "Regular"
.Size = 18
End With
ActiveChart.Axes(xlValue).AxisTitle.Select
Selection.AutoScaleFont = True
With Selection.Font
.Name = "Segoe UI"
.FontStyle = "Regular"
.Size = 16
End With
ActiveChart.Axes(xlCategory).AxisTitle.Select
Selection.AutoScaleFont = True
With Selection.Font
.Name = "Segoe UI"
.FontStyle = "Regular"
.Size = 18
End With
ActiveChart.Legend.Select
Selection.AutoScaleFont = True
With Selection.Font
.Name = "Segoe UI"
.FontStyle = "Regular"
.Size = 16
End With
Next
Errhandler:
End Sub
Can anyone help me modify this so that the syntax automatically auto formats box plots and pie charts as well? Any help, advice, or input would be very much appreciated! Thanks.