referencing commandbuttons as OLEObjects

[Bert]

I want to be able to manipulate and get information about a large number of
commandbuttons using something like:
x = Worksheets(2).OLEObjects.Count
For b = 1 To x
xx = Worksheets(2).OLEObjects.Item(b).BackColor
Next b
This generates a error. (Although xx =
Worksheets(2).OLEObjects.Item(b).Name does not)
The only objects in the OLEObjects collection are commandbuttons.
How can I make this work?
Thanks.

 

[Peter T]

change

xx = Worksheets(2).OLEObjects.Item(b).BackColor

to
xx = Worksheets(2).OLEObjects.Item(b).Object.BackColor
or
xx = Worksheets(2).OLEObjects.(b).BackColor

you could loop like this

dim ole as OLEObject

for each ole in Activeworkbook.Worksheets(2).OLEObjects
xx = ole.Object.Backcolor
next

By default, backcolors of OLEObjects are sytem colours, eg
vbButtonFace = -2147483633 (&H8000000F)

Regards,
Peter T

 

[Jim Thomlinson]

Give this a try...

Sub test()
Dim ctl As OLEObject

For Each ctl In Sheet1.OLEObjects
MsgBox ctl.Object.BackColor
Next ctl
End Sub

Your code will work but it needs to refernece the object then the backcolor...