[dstreng]

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.

-Derek

[Gregor1234]

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
Beep
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