PDA

View Full Version : List Controls on Worksheet



Belch
08-29-2007, 04:52 AM
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
Dim pBar as ProgressBar
For Each pBar In Sheet11.????
code...
Next pBar
but cannot see any collection of controls for Sheet11.

Any help appreciated, thanks.

Bob Phillips
08-29-2007, 04:58 AM
Whee do these controls come from?

Belch
08-29-2007, 05:01 AM
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
08-29-2007, 05:29 AM
Dim OLEobj As OLEObject

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

Belch
08-31-2007, 01:08 AM
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
08-31-2007, 01:46 AM
It is probably OLEObj.Object.Value that is the property.

Belch
09-03-2007, 03:16 AM
Thanks, I'll give it a try when I have a chance and let you know how it goes.