1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

macro to format charts

Discussion in 'Business Applications' started by dstreng, May 1, 2009.

Thread Status:
Not open for further replies.
  1. dstreng

    dstreng Thread Starter

    Mar 12, 2009
    I'll try to explain this best I can. I am working with a lot of excel line charts. Once I create a chart I want them always to get reformatted in the same way. I have the macro written that reformats the charts the way I want them. The problem is I don't know how to write the macro so that it always formats the chart I have selected.

    I can have it work for a specific chart (e.g. "Chart 1") by using the following code
    ActiveSheet.ChartObjects("Chart 1").Activate

    What I would like to be able to do is simply highlight any chart that I have already created and run the macro and have that chart be formatted by the macro

    So if anyone can give me code that would make the macro work for any specefic chart I select please let me know.

  2. Gregor1234


    Jun 3, 2004
    First you need to identify the chart(s) that are selected then loop through them and do the formatting things that you want do to each of them.

    SO, working backwords, you need to declare a "chart object" and write a subroutine that does your formatting things to that chart object:

    Public Sub FormatThisChart(ThisChartObj As ChartObject)
    -do what you like here to reformat ThisChartObject
    End Sub

    But that's the back end. FIrst, you need to look at what's in your seletion and decide if it's one or more charts (or something else that you can't work with)

    Now you can fool around with this trying test macros to see what turns up in the selection when several things are formatted using the "TypeName(oject)" function.

    This function returns the name of the type of the object given in the argument so you can write a macro that simply prints the type of object the selection turns out to be when select different things

    Debug.print TypeName(Selection)

    I've fooled around a little and found that if you just click on one chart, you get a "ChartArea" object returned and you need to get the .Parent of its .Parent to get to the chart object.

    If you select multiple charts, you get a DrawingObjects collection returned whose constituents are CharObjects.

    Under some circumstances, you can might get the single ChartObject in the selection.

    SO once you've found the selection typename...

    If the TypeName is ChartArea, call the formatting routine using the selection Parent.Parent

    If the TypeName is ChartObject than send the selection to the formatting routine

    If the TypeName is DrawingObject, the interate through the ChartObjects contained in it, sending each one to the formatting routine in turn

    Sub ReFormatSelectedCharts()

    Dim ThisChartObj As ChartObject

    Dim ThisType As String

    ThisType = TypeName(Selection)

    Select Case ThisType

    Case "DrawingObjects"

    For Each ThisChartObj In Selection

    Call FormatThisChart(ThisChartObj)

    Next ThisChartObj

    Case "ChartObject"
    Set ThisChartObj = Selection
    Call FormatThisChart(ThisChartObj)

    Case "ChartArea"

    Set ThisChartObj = Selection.Parent.Parent
    Call FormatThisChart(ThisChartObj)

    Case Else
    Call MsgBox("Sorry, you can't reformat a """ & ThisType & """." & vbNewLine & _
    "Select a chart or a number of charts and try again.", vbOKOnly + vbInformation, "Invalid Selection")

    End Select

    End Sub

    I've attached an zipped file as example

    Attached Files:

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/823459