VBA Determine which charts are selected

[cliffburton18]

Hi,

I have a macro which formats charts. It takes a user selected chart and formats it.

I would like to allow the user to select many charts and process them all at once (instead of just one at a time).

The problem is that when I have X charts selected in Excel, the Selection object assumes the "ChartObjects" type which includes all charts in the worksheet and not only those which are selected!
If I do "Selection.Count" it will count all charts (selected or not) and if I do "Selection.Item(x).Name" I can access any chart on the worksheet and not only those which are selected.

Questions:

1. Is there anyway to determine which charts are selected by the user? Either using the selection object or any other method?

2. When I select multiple objects (chart and non-charts), the selection object assumes the "DrawingObjects" type, which when counted returns the correct number of selected objects. However when I try to access them via "Selection.Item(x)" I again can access all DrawingObjects in the worksheet. Is there anyway to determine which drawing objects are selected? And access them (so I can determine whether they are charts)?

 

[Andrew Poulsom]

Welcome to MrExcel.

This worked for me:

Sub Test()
    Dim Item As Variant
    For Each Item In Selection
        If TypeName(Item) = "ChartObject" Then
'           Do stuff
            MsgBox Item.Name
        End If
    Next Item
End Sub

 

[cliffburton18]

Hi,

Thanks for the prompt answer.

That method works fine when there is a non-chart object select.

For example if I have 100 charts in a worksheet and 10 pictures and I selected 5 charts and 5 pictures, that code will format only the selected 5 charts and ignore the pictures as it should.

The problem remains when there are 100 charts in a worksheet, 5 of which are selected, and nothing else is selected. In this case the code will format the 100 charts in the worksheet and not only the 5 which are selected! Is there a solution to this problem?

 

[Andrew Poulsom]

With the code I posted, if I select say 3 charts from 5, only 3 message boxes appear, as I would expect. I don't see that having a non-chart object selected makes any difference.

 

[RoryA]

Which version of Excel are you using? Andrew's code works fine for me in 2010 and 2003.

 

[cliffburton18]

Now I have tryed your code exactly as posted and still have the same problem (seeing 1 textbox for each graph in the worksheet). I am using Excel2007. My code is the following:

Sub formatGraphRXH(ByVal control As IRibbonControl)
 
Const sSOURCE As String = "formatGraphRXH"
Dim objChart As Chart 'Chart to format
Dim sColorType As String 'Holds the color type
Dim bAxesVisible As Boolean 'Holds values axes visible yes/no
Dim varCycle As Variant 'Cycler for chartobjects and other objects in selection
Dim bFormat As Boolean 'True if at least one chart was formatted, false otherwise
 
On Error GoTo ErrorHandler
 
bFormat = False
 
'Turn off screen
With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With
 
'Check color ans axes
Select Case control.Id
    Case "IDGreenGraph"
        sColorType = "Green"
        bAxesVisible = True
    Case "IDBlueGraph"
        sColorType = "BlueOnly"
        bAxesVisible = True
    Case "IDGreenGraphNoAxis"
        sColorType = "Green"
        bAxesVisible = False
    Case "IDBlueGraphNoAxis"
        sColorType = "BlueOnly"
        bAxesVisible = False
End Select
 
'Check selection
If TypeName(Selection) = "ChartObjects" _
    Or TypeName(Selection) = "DrawingObjects" Then
    'Determine if it is a chart and if so format
    For Each varCycle In Selection
        If TypeName(varCycle) = "ChartObject" Then
            If varCycle.Chart.SeriesCollection.Count > 0 Then
                SRFormatGraph varCycle.Chart, sColorType, bAxesVisible
                bFormat = True
            End If
        End If
    Next varCycle
Else
    Set objChart = ActiveChart
    'Check if there is a chart selected and if so format
    If Not objChart Is Nothing Then
        If objChart.SeriesCollection.Count > 0 Then
            SRFormatGraph objChart, sColorType, bAxesVisible
            bFormat = True
        End If
    End If
End If
 
If bFormat = False Then MsgBox "Please select a chart!", vbOKOnly + vbExclamation, "TS Toolbar Version 2.0"
 
ErrorExit:
    
On Error Resume Next
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
    
Exit Sub
    
ErrorHandler:
    
If bCentralErrorHandler(msMODULE, sSOURCE, , True) Then
    Stop
    Resume
Else
    Resume ErrorExit
End If
End Sub

If there is no VBA solution, do you know any Win32 API call I can use to check which graphs are selected?

 

[RoryA]

Works for me in 2007 too. Do you have Service Pack 2 installed?

 

[cliffburton18]

True, Service Pack 2 solves the problem. Thanks.

Unfortunately, this means that any user which doesn't have service pack 2 installed will risk formatting more charts than he wants...

Is there any way I can test whether Service Pack 2 is installed via VBA?

 

[Andrew Poulsom]

You can get the Build number with:

MsgBox Application.Build

My SP2 is 6654. Here's a table:

http://support.microsoft.com/kb/928116

 

[cliffburton18]

Done. Thanks.