Need code to return OLEObject type

[MatthewGB]

Does anyone know if there is code which returns the type of an OLEObject (ie. commandbutton, checkbox, etc). Using OLEType only returns whether it is linked or embedded.

Also, Is there a way to name an OLEObject as you create it, rather than after it is created?

Thanks

 

[xlbo]

This should get you started - use typename

For Each ctrl In Me.Controls
Debug.Print TypeName(ctrl)
If TypeName(ctrl) = "CheckBox" Then 'change to whatever you want to find
MsgBox "Found"
Else
End If
Next ctrl
End Sub

HTH Rgds
~Geoff~

 

[MatthewGB]

Thank for the help geoff, but unfortunately the typename function doesn't seem to work when the checkbox's are situated on a excel worksheet. I tried both "Checkbox" and "Forms.Checkbox.1" as typenames but neither work. Is there a different set of typenames when dealing with worksheet OLEObjects?

 

[xlbo]

Yeah - they're not controls on a worksheet, they're "drawing objects" - to be honest, I can't remember whether this is for controls or forms objects ( I think it may be for controls) - they have different syntax - which are you using ??
This works for "Forms Toolbar" objects

Sub ControlLoop()

For Each ctrl In Sheets("Sheet1&quot.DrawingObjects
Debug.Print TypeName(ctrl)
If TypeName(ctrl) = "CheckBox" Then 'change to whatever you want to find
MsgBox "Found"
Else
End If
Next ctrl
End Sub Rgds
~Geoff~

 

[acron]

The following code should add a checkbox to th eactive sheet, and asign a name to the control. You would also need to include coordinate and measurement values.

ActiveSheet.OLEObjects.Add(ClassType:="Forms.CheckBox.1&quot.Name = "NewCheckbox"

A.C.