Charts Named ???? VBA Script to change (Not manual changes) over 150 charts.

[ianb]

Hi All,


I have charts named 1, 2 1894, 2347 to name a few on numberous worksheets.


I would like to number each chart 1-5 or 1-9 on each worksheet.


I could goto LAYOUT and rename each one individually.


Is it possiable via a VBA script to goto each worksheet select each name and rename is sequence.


5 becomes 1

1878 becomes 2

5434 becomes 3 then next work sheet


7 becomes 1

1524 becomes 2

9984 becomes 3


Then I can run a script to change the themes, effect etc as i know who many charts are on each worksheet.


Ian.

 

[SirJB7]

Hi, ianb!


Give a look at this file:

https://dl.dropbox.com/u/60558749/Charts%20Named%20____%20VBA%20Script%20to%20change%20%28Not%20manual%20changes%29%20over%20150%20charts.%20%28for%20ianb%20at%20chandoo.org%29.xlsm


The procedure RenameCharts renames each chart of each sheet with numbers from 1 in advance. This is the involved code:

-----

[pre]

Option Explicit

Sub RenameCharts()
' constants
' declarations
Dim I As Integer, J As Integer, A As String
' start
' process
With ActiveWorkbook
For I = 1 To .Worksheets.Count
With .Worksheets(I)
For J = 1 To .Shapes.Count
Debug.Print I, .Name, J, .Shapes(J).Name, .Shapes(J).Type
If .Shapes(J).Type = msoChart Then .Shapes(J).Name = CStr(J)
Next J
End With
Next I
End With
' end
End Sub

[/pre]
-----


Just advise if any issue.


Regards!

 

[ianb]

Genius.... Many Thanks. Make my life so easy now...

 

[ianb]

Also the formula's previous work perfect in the DASHBOARD......

 

[ianb]

Also I have been working on code to change :


Colour Scheme

Theme Effects

Theme Fonts


I am writing macros that are long. know of a short way to do this for all worksheets


I have two macros one for dark and one for light.


e.g.


' Colour Scheme


' ActiveWorkbook.Theme.ThemeColorScheme.Load ( _

' "Crogram FilesMicrosoft OfficeDocument Themes 12Theme ColorsUrban.xml")

' ActiveWorkbook.Theme.ThemeColorScheme.Load ( _

' "Crogram FilesMicrosoft OfficeDocument Themes 12Theme ColorsMetro.xml")

' ActiveWorkbook.Theme.ThemeColorScheme.Load ( _

' "Crogram FilesMicrosoft OfficeDocument Themes 12Theme ColorsMedian.xml")

ActiveWorkbook.Theme.ThemeColorScheme.Load ( _

"Crogram FilesMicrosoft OfficeDocument Themes 12Theme ColorsTechnic.xml")

' ActiveWorkbook.Theme.ThemeColorScheme.Load ( _

' "Crogram FilesMicrosoft OfficeDocument Themes 12Theme ColorsEquity.xml")

' ActiveWorkbook.Theme.ThemeColorScheme.Load ( _

' "C:UsersKalaniahAppDataRoamingMicrosoftTemplatesDocument ThemesTheme ColorsSilk.xml" _

' )


' Theme Effects


ActiveWorkbook.Theme.ThemeEffectScheme.Load ( _

"Crogram FilesMicrosoft OfficeDocument Themes 12Theme EffectsApex.eftx")


'Theme Fonts


ActiveWorkbook.Theme.ThemeFontScheme.Load ( _

"Crogram FilesMicrosoft OfficeDocument Themes 12Theme FontsTechnic.xml")


'Chart Configurations - Must be numbers in Layout Section 1-21 examples.


Sheets("Dashboard (????????)").Select

Range("A6").Select


ActiveSheet.ChartObjects("Chart 1").Activate

ActiveChart.ClearToMatchStyle

ActiveChart.ChartStyle = 18

ActiveChart.ClearToMatchStyle

ActiveSheet.ChartObjects("Chart 2").Activate

ActiveChart.ClearToMatchStyle

ActiveChart.ChartStyle = 18

ActiveChart.ClearToMatchStyle

ActiveSheet.ChartObjects("Chart 3").Activate

ActiveChart.ClearToMatchStyle

ActiveChart.ChartStyle = 18

ActiveChart.ClearToMatchStyle

ActiveSheet.ChartObjects("Chart 4").Activate

ActiveChart.ClearToMatchStyle

ActiveChart.ChartStyle = 18

ActiveChart.ClearToMatchStyle

ActiveSheet.ChartObjects("Chart 5").Activate

ActiveChart.ClearToMatchStyle


Ian.

 

[SirJB7]

Hi, ianb!


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


For the first part, you can write your code in this way too, it's clearer to read:

-----

[pre]

Option Explicit

Sub x()
' constants
Const ksPath1 = "C:Program FilesMicrosoft OfficeDocument Themes 12Theme Colors"
Const ksPath2 = "C:UsersKalaniahAppDataRoamingMicrosoftTemplatesDocument ThemesTheme Colors"
' declarations
' start
' process
With ActiveWorkbook.Theme.ThemeColorScheme
' choose one
.Load (ksPath1 & "Urban.xml")
.Load (ksPath1 & "Metro.xml")
.Load (ksPath1 & "Median.xml")
.Load (ksPath1 & "Technic.xml")
.Load (ksPath1 & "Equity.xml")
.Load (ksPath2 & "Silk.xml")
End With
' end
End Sub

[/pre]
-----


Regards!

 

[ianb]

Many Thanks again. I have an idea. do you know how to pick one at random from the list or goto 1 to 5 in order.


' constants

Const ksPath1 = "Crogram FilesMicrosoft OfficeDocument Themes 12Theme Colors"

' declarations

' start

' process

With ActiveWorkbook.Theme.ThemeColorScheme

' choose one

.Load (ksPath1 & "Urban.xml")

.Load (ksPath1 & "Metro.xml")

.Load (ksPath1 & "Median.xml")

.Load (ksPath1 & "Technic.xml")

.Load (ksPath1 & "Equity.xml")

End With

' end

 

[ianb]

Having fun tonight with this... until the movie is ready.... I watch Supernatural (The two borthers season 7) very funny.. must watch from the start season 1..

 

[SirJB7]

Hi, ianb!

For getting a random value between 1 and 5 you might use:

I = Int(Rnd()*5)+1

Define I previously as integer

Regards!

 

[Colin Legg]

Just a note that the VBA helpfile topic on Rnd() suggests:


I = Int(Rnd()*5+1)


which gives slightly different results. For example if Rnd() returns 0.2

 

[ianb]

many thanks.