This is driving me crazy!!
I have a number of controls on a sheet. (Check boxes, option buttons and
combo boxes). I know that "cmbTitle.value" or "optBlue.value" 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 "OLEobject" does not have a
"value" 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 = "Forms.CheckBox.1" Then
Set MyObj = Obj
MyObj.Value = False
End If
Next Obj

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

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 = "Forms.ComboBox.1" 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: http://www.excelforum.com/member.php...o&userid=18465
View this thread: http://www.excelforum.com/showthread...hreadid=505139

B,

Note the object.object...
'-------------------------------
Sub ARoundForTheHouse()
Dim obj As OLEObject
For Each obj In ActiveSheet.OLEObjects
If TypeName(obj.Object) = "CheckBox" Then
MsgBox obj.Name & " " & obj.Object.Value
ElseIf TypeName(obj.Object) = "ComboBox" Then
MsgBox obj.Name & obj.Object.Value
End If
Next 'obj
End Sub
'----------------

Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"Broadband Al"

wrote in message
...
This is driving me crazy!!
I have a number of controls on a sheet. (Check boxes, option buttons and
combo boxes). I know that "cmbTitle.value" or "optBlue.value" 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 "OLEobject" does not have a
"value" 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

"Jim Cone" wrote:

B,

Note the object.object...
'-------------------------------
Sub ARoundForTheHouse()
Dim obj As OLEObject
For Each obj In ActiveSheet.OLEObjects
If TypeName(obj.Object) = "CheckBox" Then
MsgBox obj.Name & " " & obj.Object.Value
ElseIf TypeName(obj.Object) = "ComboBox" Then
MsgBox obj.Name & obj.Object.Value
End If
Next 'obj
End Sub
'----------------

Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"Broadband Al"

wrote in message
...
This is driving me crazy!!
I have a number of controls on a sheet. (Check boxes, option buttons and
combo boxes). I know that "cmbTitle.value" or "optBlue.value" 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 "OLEobject" does not have a
"value" 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!