Looping through checkboxes
 

I have the line of code

Sheets(1).OLEObjects("CheckBox1").Object.Value = True

that works great, but how do I loop through all the checkboxes in my
sheet with something like this

For Each cb In Sheets(1).OLEObjects.CheckBoxes
Sheets(1).OLEObjects(cb.Name).Object.Value = True

I get a runtime error 430 Object doesn't support this property or
method.

How do I loop through the checkboxes?

Looping through checkboxes
 

On Jan 6, 8:59*am, jkrons wrote:
I have the line of code

Sheets(1).OLEObjects("CheckBox1").Object.Value = True

that works great, but how do I loop through all the checkboxes in my
sheet with something like this

For Each cb In Sheets(1).OLEObjects.CheckBoxes
Sheets(1).OLEObjects(cb.Name).Object.Value = True

I get a runtime error 430 Object doesn't support this property or
method.

How do I loop through the checkboxes?

I don't know of any way to do this directly. If you keep the default
control names (or consistently adopt a naming convention) you could
use the like operator:

Sub test()
Dim control As OLEObject

For Each control In Sheets(1).OLEObjects
If control.Name Like "CheckBox*" Then MsgBox control.Name
Next

End Sub

hth

Looping through checkboxes
 

On 6 Jan., 16:47, John Coleman wrote:
On Jan 6, 8:59*am, jkrons wrote:

I have the line of code

Sheets(1).OLEObjects("CheckBox1").Object.Value = True

that works great, but how do I loop through all the checkboxes in my
sheet with something like this

For Each cb In Sheets(1).OLEObjects.CheckBoxes
Sheets(1).OLEObjects(cb.Name).Object.Value = True

I get a runtime error 430 Object doesn't support this property or
method.

How do I loop through the checkboxes?

I don't know of any way to do this directly. If you keep the default
control names (or consistently adopt a naming convention) you could
use the like operator:

Sub test()
* * Dim control As OLEObject

* * For Each control In Sheets(1).OLEObjects
* * * * If control.Name Like "CheckBox*" Then MsgBox control.Name
* * Next

End Sub

hth

Thank you. That helped.

Jan

Looping through checkboxes
 

For Each mychkbox In wks.CheckBoxes
With mychkbox
.Value = True
End With
Next mychkbox


Gord


On Fri, 6 Jan 2012 05:59:54 -0800 (PST), jkrons wrote:

I have the line of code

Sheets(1).OLEObjects("CheckBox1").Object.Value = True

that works great, but how do I loop through all the checkboxes in my
sheet with something like this

For Each cb In Sheets(1).OLEObjects.CheckBoxes
Sheets(1).OLEObjects(cb.Name).Object.Value = True

I get a runtime error 430 Object doesn't support this property or
method.

How do I loop through the checkboxes?

Looping through checkboxes
 

On Jan 6, 11:36*am, Gord Dibben wrote:
For Each mychkbox In wks.CheckBoxes
* * * * With mychkbox
* * * * * .Value = True
* * * * End With
* * Next mychkbox

Gord

That seems to work for form controls but not ActiveX controls. It also
seems to be poorly documented (e.g. doesn't appear in the object
browser for worksheet methods) so thanks for pointing it out.



On Fri, 6 Jan 2012 05:59:54 -0800 (PST), jkrons wrote:
I have the line of code

Sheets(1).OLEObjects("CheckBox1").Object.Value = True

that works great, but how do I loop through all the checkboxes in my
sheet with something like this

For Each cb In Sheets(1).OLEObjects.CheckBoxes
Sheets(1).OLEObjects(cb.Name).Object.Value = True

I get a runtime error 430 Object doesn't support this property or
method.

How do I loop through the checkboxes?- Hide quoted text -

- Show quoted text -

Looping through checkboxes
 

You are correct John.

Forms only............was not paying attention(usual state).

Did not notice OP required code on Activex Checkboxes.

The code I posted was part of a larger set by Dave Peterson for
changing Forms checkbox linked cells.


Gord

On Fri, 6 Jan 2012 09:09:37 -0800 (PST), John Coleman
wrote:

On Jan 6, 11:36*am, Gord Dibben wrote:
For Each mychkbox In wks.CheckBoxes
* * * * With mychkbox
* * * * * .Value = True
* * * * End With
* * Next mychkbox

Gord

That seems to work for form controls but not ActiveX controls. It also
seems to be poorly documented (e.g. doesn't appear in the object
browser for worksheet methods) so thanks for pointing it out.



On Fri, 6 Jan 2012 05:59:54 -0800 (PST), jkrons wrote:
I have the line of code

Sheets(1).OLEObjects("CheckBox1").Object.Value = True

that works great, but how do I loop through all the checkboxes in my
sheet with something like this

For Each cb In Sheets(1).OLEObjects.CheckBoxes
Sheets(1).OLEObjects(cb.Name).Object.Value = True

I get a runtime error 430 Object doesn't support this property or
method.

How do I loop through the checkboxes?- Hide quoted text -

- Show quoted text -