Select multiple Excel charts-CodePudding

I am looking for a way to select multiple charts in Excel using VBA.

It can be done by using .Select on ChatObject but I cannot select multiple charts like that. If I have multiple charts in ActiveSheet every time I use .Select I select only the "last" chart and unselect previous chart.

How can I select multiple charts that satisfy my condition (whatever it is)? Or, how to add new selection to previously selected items?

Sub select_charts()

Dim chtObjs As ChartObjects
Set chtObjs = ActiveSheet.ChartObjects

Dim chtObj As ChartObject
For Each chtObj In chtObjs
    If chtObj.Height > 100 Then
        chtObj.Select
    End If
Next

End Sub

CodePudding user response：

VBA is able to select more objects using an array containing the objects Names, or their indexes.

The simplest way, answering your question is:

 ActiveSheet.Shapes.Range(Array("Chart 1", "Chart 2")).Select

You can build such an array (using some conditions) and finally use it for selection:

Sub testSelectCharts()
  Dim sh As Worksheet, s As Shape, arrChObj(), arrChIndex(), i As Long, k As Long
  
  Set sh = ActiveSheet
  ReDim arrChObj(sh.Shapes.count - 1)    'ReDim the array to be sure that it has enough elements
  ReDim arrChIndex(sh.Shapes.count - 1)  'ReDim the array to be sure that it has enough elements
  For Each s In sh.Shapes                'iterate between all shapes
    i = i   1                            'the shape index
    If TypeOf s.OLEFormat.Object Is ChartObject Then 'act only for chart objects:
        arrChIndex(k) = i               'loading an array of shapes index
        arrChObj(k) = s.Name: k = k   1 'loading an array of shapes name
    End If
  Next
  ReDim Preserve arrChObj(k - 1)        'preserving only the array elements containing values
  ReDim Preserve arrChIndex(k - 1)      'preserving only the array elements containing values
  sh.Shapes.Range(arrChObj).Select      'selecting using array object names  
  'sh.Shapes.Range(arrChIndex).Select   'it works using sheet indexes, too (just uncomment and comment the above code line)
End Sub

You can select/activate sheets in the next way:

Sub testSheetsSelect()
  Worksheets(Array(Sheets(1).Name, Sheets(3).Name)).Select
  'Worksheets(Array(1, 3)).Select     'using an array of sheets indexes
End Sub

The next testing sub selects sheet ActiveX controls of type "CommandButton". Of course, the active sheet should have at least one such a control and some others, of different type (combo box, list box etc.):

Sub testObjectsNameArray()
  Dim sh As Worksheet, objOLE As OLEObject, arrOLE(), k As Long

  Set sh = ActiveSheet
  ReDim arrOLE(sh.OLEObjects.count - 1)
  For Each objOLE In sh.OLEObjects
    If TypeOf objOLE.Object Is MSForms.CommandButton Then
        arrOLE(k) = objOLE.Name: k = k   1
    End If
  Next
  ReDim Preserve arrOLE(k - 1)
  sh.OLEObjects(arrOLE).Select
End Sub

The array of indexes should also work...