macro for charting

[rbottner]

Hello,

I need some help! I am good with computers, but I am brand new to macros and VB.

I have about 30 work books, each with about 10 worksheets. Each worksheet has about 2 graphs (some have many as 10).

I need to change the fonts, background colors, colors of the bars, etc. About 10 changes in all - to each graph.

I clicked on one graph in sheet 1 and recorded the changes. I then clicked on the next graph and applied the macro I made.

Can someone explain to me how I go about applying the macro I created to the 200+ graphs I have? (I am currently getting an error 91.)

Thank you again!


This is what the Macro Editor came up for what I have now based on my recording:

Sub Graphs()
'
' Graphs Macro
'

'
ActiveChart.ChartTitle.Select
Selection.AutoScaleFont = True
With Selection.Font
.Name = "Tahoma"
.FontStyle = "Bold"
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
ActiveChart.Axes(xlValue).Select
Selection.TickLabels.AutoScaleFont = True
With Selection.TickLabels.Font
.Name = "Tahoma"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
ActiveChart.Axes(xlCategory).Select
Selection.TickLabels.AutoScaleFont = True
With Selection.TickLabels.Font
.Name = "Tahoma"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
ActiveChart.SeriesCollection(1).DataLabels.Select
Selection.AutoScaleFont = True
With Selection.Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
ActiveChart.Axes(xlValue).AxisTitle.Select
Selection.AutoScaleFont = True
With Selection.Font
.Name = "Tahoma"
.FontStyle = "Bold"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
Windows("skills.xls").SmallScroll Down:=3
ActiveChart.Axes(xlCategory).AxisTitle.Select
Selection.AutoScaleFont = True
With Selection.Font
.Name = "Tahoma"
.FontStyle = "Bold"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
ActiveChart.Axes(xlCategory).Select
ActiveChart.ChartArea.Select
With Selection.Border
.Weight = 1
.LineStyle = -1
End With
With Selection.Interior
.ColorIndex = 17
.PatternColorIndex = 1
.Pattern = 1
End With
Sheets("multi-task").DrawingObjects("Chart 2").RoundedCorners = True
Sheets("multi-task").DrawingObjects("Chart 2").Shadow = False
ActiveChart.PlotArea.Select
With Selection.Border
.ColorIndex = 16
.Weight = xlThin
.LineStyle = xlContinuous
End With
With Selection.Interior
.ColorIndex = 19
.PatternColorIndex = 1
.Pattern = xlSolid
End With
ActiveChart.SeriesCollection(1).Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlAutomatic
End With
Selection.Shadow = False
Selection.InvertIfNegative = False
Selection.Fill.Patterned Pattern:=msoPatternNarrowHorizontal
With Selection
.Fill.Visible = True
.Fill.ForeColor.SchemeColor = 18
.Fill.BackColor.SchemeColor = 25
End With
With Selection.Border
.Weight = xlThin
.LineStyle = xlAutomatic
End With
Selection.Shadow = True
Selection.InvertIfNegative = False
End Sub

[duane]

' Here is a shot at it try running this on each workbook (1 at a time) or try
' one anyway - Not sure if this will run though
' if it runs, the next step would be to put all the setpoints in named ranges
' on the sheet so you could change them there
'
Sub macro1()
Dim mywksht As Sheets
Dim mycht As ChartObject
On Error Resume Next
For Each mywksht In ActiveWorkbook.Sheets
For Each mycht In ActiveSheet.ChartObjects
mycht.Activate
mycht.Chart.ChartTitle.Select
Selection.AutoScaleFont = True
With Selection.Font
.Name = "Tahoma"
.FontStyle = "Bold"
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
ActiveChart.Axes(xlValue).Select
Selection.TickLabels.AutoScaleFont = True
With Selection.TickLabels.Font
.Name = "Tahoma"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
ActiveChart.Axes(xlCategory).Select
Selection.TickLabels.AutoScaleFont = True
With Selection.TickLabels.Font
.Name = "Tahoma"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
ActiveChart.SeriesCollection(1).DataLabels.Select
Selection.AutoScaleFont = True
With Selection.Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
ActiveChart.Axes(xlValue).AxisTitle.Select
Selection.AutoScaleFont = True
With Selection.Font
.Name = "Tahoma"
.FontStyle = "Bold"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
Next mycht
Next mywkbk
End Sub

[rbottner]

I got a "compile error: invalid next control variable reference"

THANKS AGAIN!

[VBA Noob]

Please read forum below rules and then wrap your code

VBA Noob

[duane]

just looking in from work real quick.....my error

Next mywkbk should be

Please Login or Register  to view this content.

[duane]

Please Login or Register  to view this content.