Formatting selected chart in Excel 2007

[Luca Brasi]

I am using code like in the sample below to detect if a single chart shape is
selected on the active worksheet and then do some formatting.
But the code does not run properly. Whenever I call a property of the chObj
reference an error occurs. When I do some debugging and put this reference in
the Watch list Excel even crashes.

Using Excel 2007 SP2. Thanks for any hints! Luca

*************************************
Dim chObj As ChartObject
If Windows.Count > 0 Then
If TypeName(ActiveSheet) = "Worksheet" Then
If TypeName(ActiveWindow.Selection)="ChartObject" then
Set chObj = ActiveWindow.Selection
Debug.Print chObj.name
'here some more code to format the chart
End If
End If
End If
*************************************

 

[Jacob Skaria]

Hi Luca

You dont need to select the chart object to format; instead try

'if this is the only chart in the activesheet
With ActiveSheet.ChartObjects(1)
MsgBox .Name
'code for formatting
End With

OR

'if you have more than 1 chart specify the name instead of index
With ActiveSheet.ChartObjects("Chart 1")
MsgBox .Name
'code for formatting
End With


If this post helps click Yes

 

[Luca Brasi]

Jacob, I'm aware of this. However my add-in should format the chart object
the user currently has selected.

Luca

 

[Jacob Skaria]

OK. Try the below


Dim ws As Worksheet
Dim chObj As ChartObject

Set ws = ActiveWorkbook.Sheets("Sheet2")
Set chObj = Worksheets("Sheet2").ChartObjects("Chart 1")

ws.Activate
chObj.Select

If this post helps click Yes

 

[Luca Brasi]

Jacob, I really appreciate that you're trying to help, but I guess you don't
understand my problem (maybe I did not describe it good enough).

My add-in can be called from a button in the ribbon and should format the
chart THE USER currently has selected. I don't want to select a chart in the
VBA.

I can handle the case when no chart is selected (then my code loops through
all charts in the ActiveSheet) and when several charts are selected at the
same time (my code then loops through all shapes in ActiveWindow.Selection
and checks if they are charts).

However, it looks to me there's a bug in Excel 2007 VBA when just one chart
is selected (or I am doing something wrong of course...).

As workaround, I even tried to create a temporary shape and add it to the
selection to have the same initial position as if the user had several shapes
selected. But then things get very wierd! Completely different and never
selected shapes then get processed.

If someone experiences the same issue or even knows a solution/workaround, I
appreciate your information.

Thanks, Luca

 

[Jacob Skaria]

Currently I dont have an excel 07 instance to try out your code; but
definitely will follow up this in another 4-5 hours. In the mean time we will
wait for other contributors who can..

 

[Peter T]

I don't quite follow or understand what workaround you are looking for,
maybe something below will give some pointers

Sub test()
Dim cht As Chart
Set cht = ActiveSheet.ChartObjects(1).Chart
Set cht = ActiveChart
If Not cht Is Nothing Then
If TypeName(Selection) = "ChartObject" Then
' had been selected while holding Ctrl
' to activate the chart 2007 is different to earlier versions
If Application.Version >= 12 Then
cht.ChartArea.Select
Else
cht.Parent.Activate
End If
End If
Debug.Print "ChartObject(Shape) : Chart-name" & _
vbCr; cht.Parent.Name, cht.Name
End If
End Sub

You will want to be dealing with 'Chart', not ChartObject

set cht = ActiveSheet.ChartObjects(1).Chart

Regards,
Peter T

 

[Luca Brasi]

Peter, that's it! I forgot about the ActiveChart property. Using ActiveChart
instead of ActiveWindow.Selection.Chart works. I still don't understand why
the later does not work but it doesn't matter.

Thank you very much!
Luca

 

[Peter T]

Typo

If Application.Version >= 12 Then

better to do
If Val(Application.Version) >= 12 Then

Glad you found what you were after

Regards,
Peter T