software

This is driving me crazy!!
I have a number of controls on a sheet. (Check boxes, option buttons and
combo boxes). I know that quot;cmbTitle.valuequot; or quot;optBlue.valuequot; sets/returns
what I want for an individual control but how do you refer to the controls in
a loop without using their individual names?

ie I want a macro to loop through the controls and return or set the
controls' values to/from an array. I have been using:
thisworkbook.sheets(s).OLEobject(x)...... where x=the control's name.

This works for pulling up their names etc. but quot;OLEobjectquot; does not have a
quot;valuequot; property. While about it, how do you return the type of a control ie
whether it is a combo of check box?
Please help...... I don't have much hair left!


Hello Broadband Al,

As you have found out working with ActiveX (Control Toolbox) objects
isn't intuitively obvious. Hopefully these examples will make things
clearer.

Clearing/Setting ActiveX Controls:

Dim MyObj As Object
Dim Obj
For Each Obj In ActiveSheet.OLEOBjects
If Obj.ProgID = quot;Forms.CheckBox.1quot; Then
Set MyObj = Obj
MyObj.Value = False
End If
Next Obj

These can be used for OptionButtons also. Change Obj.ProgID to
Obj.PorgID = quot;Forms.OptionButton.1quot;.

To set the value instead of clearing it, change MyObj.Value = False to
MyObj.Value = True.

Clearing a ComboBoxes:

Dim MyObj As Object
Dim Obj

For Each Obj In ActiveSheet.OLEObjects
If Obj.ProgID = quot;Forms.ComboBox.1quot; Then
Set MyObj = Obj
MyObj.Clear
End If
Next Obj

If you have any further questions, you can contact me by email at
.

Sincerely,
Leith Ross--
Leith Ross
------------------------------------------------------------------------
Leith Ross's Profile: www.excelforum.com/member.php...oamp;userid=18465
View this thread: www.excelforum.com/showthread...hreadid=505139B,

Note the object.object...
'-------------------------------
Sub ARoundForTheHouse()
Dim obj As OLEObject
For Each obj In ActiveSheet.OLEObjects
If TypeName(obj.Object) = quot;CheckBoxquot; Then
MsgBox obj.Name amp; quot; quot; amp; obj.Object.Value
ElseIf TypeName(obj.Object) = quot;ComboBoxquot; Then
MsgBox obj.Name amp; obj.Object.Value
End If
Next 'obj
End Sub
'----------------

Jim Cone
San Francisco, USA
www.realezsites.com/bus/primitivesoftware
quot;Broadband Alquot;
gt;
wrote in message
news
This is driving me crazy!!
I have a number of controls on a sheet. (Check boxes, option buttons and
combo boxes). I know that quot;cmbTitle.valuequot; or quot;optBlue.valuequot; sets/returns
what I want for an individual control but how do you refer to the controls in
a loop without using their individual names?

ie I want a macro to loop through the controls and return or set the
controls' values to/from an array. I have been using:
thisworkbook.sheets(s).OLEobject(x)...... where x=the control's name.

This works for pulling up their names etc. but quot;OLEobjectquot; does not have a
quot;valuequot; property. While about it, how do you return the type of a control ie
whether it is a combo of check box?
Please help...... I don't have much hair left!

Thanks guys.. the obj.object did the trick. Also, I had not declared an
object type variable so I must have been trying to set object properties to
string variables - and getting my strings in knots (ha ha)!
Your suggestions worked perfectly and I can go to bed happy at 0120hrs here
in the UK! Thanks.
Al

quot;Jim Conequot; wrote:

gt; B,
gt;
gt; Note the object.object...
gt; '-------------------------------
gt; Sub ARoundForTheHouse()
gt; Dim obj As OLEObject
gt; For Each obj In ActiveSheet.OLEObjects
gt; If TypeName(obj.Object) = quot;CheckBoxquot; Then
gt; MsgBox obj.Name amp; quot; quot; amp; obj.Object.Value
gt; ElseIf TypeName(obj.Object) = quot;ComboBoxquot; Then
gt; MsgBox obj.Name amp; obj.Object.Value
gt; End If
gt; Next 'obj
gt; End Sub
gt; '----------------
gt;
gt; Jim Cone
gt; San Francisco, USA
gt; www.realezsites.com/bus/primitivesoftware
gt;
gt;
gt;
gt; quot;Broadband Alquot;
gt; gt;
gt; wrote in message
gt; news
gt; This is driving me crazy!!
gt; I have a number of controls on a sheet. (Check boxes, option buttons and
gt; combo boxes). I know that quot;cmbTitle.valuequot; or quot;optBlue.valuequot; sets/returns
gt; what I want for an individual control but how do you refer to the controls in
gt; a loop without using their individual names?
gt;
gt; ie I want a macro to loop through the controls and return or set the
gt; controls' values to/from an array. I have been using:
gt; thisworkbook.sheets(s).OLEobject(x)...... where x=the control's name.
gt;
gt; This works for pulling up their names etc. but quot;OLEobjectquot; does not have a
gt; quot;valuequot; property. While about it, how do you return the type of a control ie
gt; whether it is a combo of check box?
gt; Please help...... I don't have much hair left!
gt;