How to select all ActiveX objects in an area using a mouse in Excel?

Question

Because of this problem with ActiveX objects changing size, I am not grouping my ActiveX objects in my Excel worksheet. Grouping them causes my solution hack to not work which is quite annoying.

However, I often times want to be able to essentially use the mouse and select a region and then select all ActiveX objects contained in the region. This would also be useful for easily selecting objects to group them initially.

Basically:

Use mouse to select area
Automatically select all ActiveX components in region

I'm fine with a VBA solution if needed.

How can I do this?

score 2 · Accepted Answer · edited Nov 01 '13 at 00:08

Consider:

Sub ShapePicker()
    Dim sh As Shape, st As Variant, Llist As String
    Dim ty As String
    Dim nm As String
    Dim r As Range

    Dim ary As Variant

    For Each sh In ActiveSheet.Shapes
        ty = sh.Type
        nm = sh.name
        Set r = sh.TopLeftCell
        If ty = msoOLEControlObject Then
            If Not Intersect(r, Selection) Is Nothing Then
                If Llist = "" Then
                    Llist = nm
                Else
                    Llist = Llist & "," & nm
                End If
            End If
        End If
    Next sh
    ary = Split(Llist, ",")
    ActiveSheet.Shapes.Range((ary)).Select
End Sub

How to select all ActiveX objects in an area using a mouse in Excel?

1 Answers1