• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Chart VBA Program (Not fully working)

ianb

ianb

Member
Hi,


I have been going at this for 1 hour and I am a little lost at the moment.


Here is my code with the two other parts listed below which I can not add to my program to make the chart resizing work.


The title is set to 14. I need the other font size to be set to 8 for all the other text and numbers on the charts.


Sub ChartTitles()


Dim ws As Worksheet

Dim ch As ChartObject


For Each ws In Worksheets

For Each ch In ws.ChartObjects


ch.Activate

ActiveChart.ClearToMatchStyle

ActiveChart.ChartStyle = 18

ActiveChart.ClearToMatchStyle

Next ch

Next ws


'Font Size and Colour in Charts

'Dim ws As Worksheet

'Dim ch As ChartObject


Dim Fnt As String

Dim FntSz As Double

Dim FntR As Integer

Dim FntG As Integer

Dim FntB As Integer


Dim ax As Axis


Fnt = "Rockwell (Body)" 'Set Font type

FntSz = 14 'Set Font Size

FntSzs = 8 'Set Font Size Small


'Black Text


FntR = 0 'Set Font Color Red

FntG = 0 'Set Font Color Green

FntB = 0 'Set Font Color Blue


For Each ws In Worksheets

For Each ch In ws.ChartObjects


ch.Activate

ch.Chart.ChartTitle.Select

With Selection.Font.Name = Fnt

Selection.Font.Size = FntSz

Selection.Font.Color = RGB(FntR, FntG, FntB)

Application.Goto Range("a1")

End With


Next ch

Next ws


Application.Run "PivotTableConfigLight"

'Application.Run "BoxChangeColLight"


Sheets("Dashboard (Overview)").Select


End Sub


Second Attempt.


' ch.Chart.Axes(xlCategory).TickLabels.Select

' With Selection.Font.Name = Fnt

' Selection.Font.Size = FntSzs

' Selection.Font.Color = RGB(FntR, FntG, FntB)

' End With

'

' ch.Chart.Axes(xlValue).TickLabels.Select

' With Selection.Font.Name = Fnt

' Selection.Font.Size = FntSzs

' Selection.Font.Color = RGB(FntR, FntG, FntB)

' Application.Goto Range("a1")

' End With


Code given to me to add.


'Dim typelist As Variant, mr As Variant

'Dim ct As Integer

'

'typelist = Array(xlColumnStacked, xlColumnClustered, xlLineMarkers, xl3DPieExploded)

'ct = ch.Chart.ChartType

'On Error Resume Next

'mr = Null

'mr = Application.WorksheetFunction.Match(ct, typelist, 0)

'On Error GoTo 0

'

'If IsNull(mr) Then

' ch.Chart.Axes(xlCategory).TickLabels.Font.Size = 8

'ch.Chart.Axes(xlValues).TickLabels.Font.Size = 8

'End If
 
Hi Ian ,


Can you try this ?

[pre]
Code: 
Sub ChartTitles()
'Font Size and Colour in Charts

Dim ws As Worksheet
Dim ch As ChartObject

Dim typelist As Variant, mr As Variant
Dim ct As Integer

typelist = Array(xlPie, xlPieExploded, xlPieOfPie, xl3DPie, xl3DPieExploded)

Dim Fnt As String
Dim FntSz As Double
Dim FntSzs As Double
Dim FntR As Integer
Dim FntG As Integer
Dim FntB As Integer

Dim ax As Axis

Fnt = "Rockwell (Body)" 'Set Font type
FntSz = 14 'Set Font Size
FntSzs = 8 'Set Font Size Small

'Black Text

FntR = 0 'Set Font Color Red
FntG = 0 'Set Font Color Green
FntB = 0 'Set Font Color Blue

For Each ws In Worksheets
For Each ch In ws.ChartObjects
With ch.Chart
.ClearToMatchStyle
.ChartStyle = 18
.ClearToMatchStyle

.ChartTitle.Select
With Selection
.Font.Name = Fnt
.Font.Size = FntSz
.Font.Color = RGB(FntR, FntG, FntB)
End With

ct = .ChartType
On Error Resume Next
mr = Null
mr = Application.WorksheetFunction.Match(ct, typelist, 0)
On Error GoTo 0

If IsNull(mr) Then
With .Axes(xlCategory).TickLabels
.Font.Name = Fnt
.Font.Size = FntSzs
.Font.Color = RGB(FntR, FntG, FntB)
End With

With .Axes(xlValue).TickLabels
.Font.Name = Fnt
.Font.Size = FntSzs
.Font.Color = RGB(FntR, FntG, FntB)
End With
End If
End With
Next ch
Next ws

Application.Goto Range("a1")
Application.Run "PivotTableConfigLight"
'Application.Run "BoxChangeColLight"

Sheets("Dashboard (Overview)").Select
End Sub
[/pre]
Narayan
 
Many Many Thanks to both - JB and NARAYANK


The location of my If and End (With and End With) was in a differnet location) I see it clearly now. Will test with excitement this evening when I am at home...Many Thanks..
 
Hi, ianb!

Glad you solved it, but in this case all credit is for NARAYANK991, I did nothing at this topic.

Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.

Regards!
 
Thanks Both, It works for me and look alot smoother when it does the 16 work sheets.


The only part it does not do is the Data Tables.


Do you know how I can do this ?
 
1, Data Tables

2. Data Label Values


How do I set these to = 8


With <Data Table> / <Data Label value>

.Font.Name = Fnt

.Font.Size = FntSzs

.Font.Color = RGB(FntR, FntG, FntB)

End With


Searching the internet and can not find any info. thanks again if any one can assist.
 
Final One : Some Have legends and some do not my attempt below gives all charts legends


3. Format Legend !!!


With ActiveChart

.HasLegend = True

With .Legend

.Position = xlTop

.Font.Name = Fnt

.Font.Size = FntSzs

.Font.Color = RGB(FntR, FntG, FntB)

End With

End With
 
NARAYANK,


Do you know the answer or can to direc me to a web site that lists all the chart definitions ?
 
Hi Ian ,


Is it OK if I answer tomorrow morning ?


I should think that the following link is the definitive guide to the Chart object :


http://msdn.microsoft.com/en-us/library/office/ff194426.aspx


Narayan
 
Thank you NARAYANK. I will try some of these commands in the program.


If you know how to set :


1, Data Tables

2. Data Label Values


You shall be most welcome....


Ian.
 
I have tried adding to the program and I would like to just add if the chart already has :


1, Data Tables

2. Data Label Values

3. Format Legend


Else do not adjust. I am getting closer. your expertise is greatly welcomed.


Ian.
 
Hi Narayank,


Have been trying to do this myself and I can not do the part for the data tables if the data tables does not exist hence all my charts end up with data tables which I do not require. same for Format Legends. I also after having an hour education on the web site you provided gained lots of knowledge and again was unable to find the data label values formula. again some have values some do not !!!!
 
Hi Ian ,


Sorry for the delay ; there are too many properties to be detailed ; let us first take up the DataTable property.


ActiveChart.HasDataTable = True will turn ON the display of the data table


ActiveChart.HasDataTable = False will turn OFF the display of the data table


When the data table is displayed , you can manipulate the borders and the font and other properties :


ActiveChart.DataTable.Font.Name = "Courier"

ActiveChart.DataTable.Font.Size = 10

ActiveChart.DataTable.Font.Bold = True


ActiveChart.DataTable.Border.Color = 6

ActiveChart.DataTable.Border.LineStyle = xlDash

ActiveChart.DataTable.Border.Weight = xlMedium


Narayan
 
Hi Ian ,


For the Data Labels , the syntax is :


ActiveChart.ApplyDataLabels


When you press a space after typing in ApplyDataLabels , the picklist will be displayed ; you can choose to display the Label , Value , Percent and some other options. If you want to turn off the data labels , choose :


ActiveChart.ApplyDataLabels xlDataLabelsNone


To format the Data Labels , use the following syntax :


ActiveChart.SeriesCollection(1).DataLabels.Format.TextFrame2.TextRange.Font.Size = 10


Narayan
 
Many Thanks. I will try this. The charts are look good. with the new sync chart program Ian...
 
I can do this individually for each of the tables that require font size changes thus all the charts will be at the correct font sizes... many thanks.


Ian.
 
You must log in or register to reply here.
Back
Top