There's no such thing as a stupid question, but they're the easiest to answer.


Search Search
Search for:
Tech Support Guy > > >

macro to format charts


dstreng's Avatar
dstreng dstreng is offline
Junior Member with 6 posts.
Join Date: Mar 2009
Experience: Intermediate
01-May-2009, 11:55 AM #1
macro to format charts
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.

Gregor1234's Avatar
Gregor1234 Gregor1234 is offline
Member with 237 posts.
Join Date: Jun 2004
Experience: Intermediate
01-May-2009, 01:53 PM #2
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
File Type: zip (14.4 KB, 367 views)

Last edited by Gregor1234; 01-May-2009 at 03:50 PM..
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine

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.

excel 03, excel chart, excel macro, macro, vba

Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
Thread Tools

You Are Using: Server ID
Trusted Website Back to the Top ↑