List Controls on Worksheet

[Belch]

Hi all,

I have a number of ProgressBar controls on a worksheet and would like to be able to use a For Each loop to go through them to find the one(s) I want. Is there any way to do this?

I've got as far as
[VBA]Dim pBar as ProgressBar
For Each pBar In Sheet11.????
code...
Next pBar[/VBA]
but cannot see any collection of controls for Sheet11.

Any help appreciated, thanks.

[Bob Phillips]

Whee do these controls come from?

[Belch]

I added them to the worksheet from the control toolbox, so they are 'floating' on the sheet, not on any form.

I can access each ProgressBar control separately using Sheet11.progBar1, Sheet11.progBar2, etc but I need to access the collection so I can run through them with a For loop.

[Bob Phillips]

[vba]

Dim OLEobj As OLEObject

For Each OLEobj In ActiveSheet.OLEObjects
If TypeName(OLEobj.Object) = "ProgressBar" Then
Debug.Print OLEobj.Name
End If
Next OLEobj
[/vba]

[Belch]

I ran the code which output each ProgressBar control Name to the Immediate window - now is there any way to edit the Value property of each ProgressBar whilst in that For loop? I tried OLEobj.Value but I get the "object doesn't support this property or method" error.
Thanks,

[Bob Phillips]

It is probably OLEObj.Object.Value that is the property.

[Belch]

Thanks, I'll give it a try when I have a chance and let you know how it goes.