Set FilterOleObjectsByType = colDestination Public Function FilterOleObjectsByType(colSource As Collection, varTypes As Variant) As Collectionĭim colDestination As Collection: Set colDestination = New Collectionĭim varElement As Variant: For Each varElement In colSourceĭim shpElement As Shape: Set shpElement = varElementĭim i As Long: For i = LBound(varTypes) To UBound(varTypes)
Get activex controls code#
I think it's an important task to reach all OLE Objects, so I created the below code in a modular fashion and tested on some example objects: Option Explicitĭim colOleObjects As Collection: Set colOleObjects = CollectOleObjectsOnWorksheet(ActiveSheet)ĭim colCheckboxesAndOptionboxes As Collection: Set colCheckboxesAndOptionboxes = FilterOleObjectsByType(colOleObjects, Array("Forms.CheckBox.1", "Forms.OptionButton.1"))ĭim varItem As Variant: For Each varItem In colCheckboxesAndOptionboxesĭim shpItem As Shape: Set shpItem = varItem If TypeName(sh.) = "OptionButton" Or TypeName(sh.) = "CheckBox" Then ' Found OptionButton or CheckBox: Add it to Dictionary. ' Debug.Print sh.Name sh.Type TypeName(sh.) ListObjects ActiveSheet.Shapes, ListOfOptions Note that you need a reference to the Scripting Library.
Get activex controls plus#
Update: The code now creates a Dictionary containing all CheckBoxex and RadioButtons plus their value. See the following code to write all OLEObjects. Groups have the Type msoGroup (6) and have a Collection GroupItems that holds all Shapes within that group. You can check for Type = msoOLEControlObject (12) of the shape so that you list only OLEObjects. To get all ActiveX Objects, even when put into a group, start by using the Shapes-Collection rather than the OLEObjects-Collection.
0 kommentar(er)
