Change Chart Sheet Name in VBA

[st471438]

Hi,

I have been reading the forums and I found it great! I have been running through a problem in my one of the report that I generate. I have more then 15 charts to create every week in one report and what I do is, create charts into each's Sheets Example: A data is in "data" sheet and then Create a chart for Boston city and choose a "Boston" named sheet to have chart over there and so on.. Then I go ahead and maximize the chart to fits into screen and adjust the fonts and so on. Since the data source range keeps changing every time thats how I do manually the charts BUT I tried to record macro to maximize the chart fits into screen and adjust fonts and then tried to run the same macro in another chart sheet but off course since the macro was recorded in sheet name "Boston" it gives me error and I dont know how and what should I put so it takes the sheet names automatic or at least I can click on every sheet and make it active and just press any key stroke that is assigned to macro so that can be done easily but again I tried giving some active.sheet name and stuff into chart name but its not working. In macro it gives chart number Example: "Chart 39" and so on?! I dont know where that numbers is coming from.

Any help?

 

[Datsmart]

Welcome to the forum.

"Chart 39" means that is the 39th chart created in that workbook.

Please post your recorded code. Hard to guess what it looks like from what you are describing.

 

[st471438]

Ok,

here is the macro code which I simply recorded - What I want to do is, when I go to second sheet and run macro - I want macro to use all these commands on that new chart such as fonts, size etc. but the problem is when I tried to run this recorded macro in another sheet it gives me error and stucks on "Chart 2" - because its a different sheet "Chart 2" probably becomes "Chart 3" or something else so how can macro automatic detect the current sheet and change chart number or consider that sheet?

Here is the code.

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 6/7/2010 by strivedi
'
'
ActiveSheet.Shapes("Chart 2").ScaleWidth 1.99, msoFalse, msoScaleFromMiddle
ActiveSheet.Shapes("Chart 2").ScaleHeight 2.01, msoFalse, msoScaleFromMiddle
ActiveChart.ChartTitle.Select
Selection.AutoScaleFont = True
With Selection.Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 12
.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 = "Arial"
.FontStyle = "Regular"
.Size = 10
.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 = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
ActiveChart.Legend.Select
Selection.AutoScaleFont = True
With Selection.Font
.Name = "Arial"
.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
With Selection.TickLabels
.Alignment = xlCenter
.Offset = 100
.ReadingOrder = xlContext
.Orientation = xlUpward
End With
End Sub

 

[Datsmart]

This code assigns a variable for the sheet and chart names for whatever worksheet it is run on.

Sub RefreshChart()
Dim sh As String, cn As String
Dim ChObj As Object
    'Assign Variable for Chart Name
    For Each ChObj In ActiveSheet.ChartObjects
        cn = ChObj.Name
    Next ChObj
    'Assign Variable for Sheet Name
    sh = ActiveSheet.Name
    
    Set CurrentChart = Sheets(sh).ChartObjects(1).Chart
    ActiveSheet.Shapes(cn).ScaleWidth 1.99, msoFalse, msoScaleFromMiddle
    ActiveSheet.Shapes(cn).ScaleHeight 2.01, msoFalse, msoScaleFromMiddle

    Sheets(sh).ChartObjects(cn).Chart.ChartTitle.AutoScaleFont = True
    With CurrentChart.ChartTitle.Font
        .Name = "Arial"
        .FontStyle = "Bold"
        .Size = 12
    End With
    
    Sheets(sh).ChartObjects(cn).Chart.Axes(xlCategory).TickLabels.AutoScaleFont = True
    With CurrentChart.Axes(xlCategory).TickLabels.Font
        .Name = "Arial"
        .FontStyle = "Regular"
        .Size = 10
    End With
    
    Sheets(sh).ChartObjects(cn).Chart.Axes(xlValue).TickLabels.AutoScaleFont = True
    With CurrentChart.Axes(xlValue).TickLabels.Font
        .Name = "Arial"
        .FontStyle = "Regular"
        .Size = 10
    End With
    
    Sheets(sh).ChartObjects(cn).Chart.Legend.AutoScaleFont = True
    With CurrentChart.Legend.Font
        .Name = "Arial"
        .FontStyle = "Regular"
        .Size = 10
    End With
    
    Sheets(sh).ChartObjects(cn).Chart.Axes(xlCategory).TickLabels.AutoScaleFont = True
    With CurrentChart.Axes(xlCategory).TickLabels
        .Alignment = xlCenter
        .Offset = 100
        .ReadingOrder = xlContext
        .Orientation = xlUpward
    End With
End Sub

Code assumes there is a single chart on the worksheet it is run on.
Also assumes the chart includes Title, Legend, etc.

 

[st471438]

WOW - COOL!! Thank you so much! this will save my bunch of time. This is great forum this will also help me to learn. I just completed my VB.net online classes but strugling to understand in VBA thank you!