nichehasem.blogg.se

1. #Excel vba how to get activex control properties code
2. #Excel vba how to get activex control properties plus

I modified the code: (1) OleObjects may have msoOLEControlObject, (2) I added a Function to filter the objects retrieved, so that they include only CheckBoxes and OptionBoxes.

#Excel vba how to get activex control properties code

You just have to include the three functions somewhere in the code and call CollectOleObjectsOnWorksheet from your part of the code. I think the advantage of my solution is that the enumeration of objects is decoupled from the actual task you want to do with them. However, once you have this collection, you can do anything with it. In my code, Example retrieves the Collection of OleObjects on the ActiveSheet, filters it to include only CheckBoxes and OptionBoxes by calling FilterOleObjectsByType then it prints the name of each. CollectionAddElements is just a helper function to create the union of two Collections. Public Sub CollectionAddElements(colTarget As Collection, colSource As Collection)īasically, CollectOleObjectsOnWorksheet returns a collection of all OleObjects on the Worksheet given as a parameter building on the functionality of recursively enumerating OleObjects provided by CollectOleObjectsOfShape. Public Function CollectOleObjectsOfShape(shpTarget As Shape) As CollectionĬase MsoShapeType.msoEmbeddedOLEObject, MsoShapeType.msoOLEControlObjectĭim varChild As Variant: For Each varChild In shpTarget.GroupItems

Set CollectOleObjectsOnWorksheet = colResult Public Function CollectOleObjectsOnWorksheet(ewsTarget As Worksheet) As Collectionĭim colResult As Collection: Set colResult = New Collectionĭim varChild As Variant: For Each varChild In ewsTarget.Shapesĭim shpChild As Shape: Set shpChild = varChildĭim colChild As Collection: Set colChild = CollectOleObjectsOfShape(shpChild)ĬollectionAddElements colResult, colChild 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) 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.

#Excel vba how to get activex control properties 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.