ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Looping (https://www.excelbanter.com/excel-programming/299774-looping.html)

René
Looping
 
How can use a loop to do the following:

Sheet1.CheckBox1.Value = Unchecked
Sheet1.CheckBox2.Value = Unchecked
Sheet1.CheckBox3.Value = Unchecked
Sheet1.CheckBox4.Value = Unchecked
Sheet1.CheckBox5.Value = Unchecked
Sheet1.CheckBox6.Value = Unchecked
Sheet1.CheckBox7.Value = Unchecked
Sheet1.CheckBox8.Value = Unchecked
Sheet1.CheckBox9.Value = Unchecked
Sheet1.CheckBox10.Value = Unchecked
Sheet1.CheckBox11.Value = Unchecked
Sheet1.CheckBox12.Value = Unchecked
Sheet1.CheckBox13.Value = Unchecked
Sheet1.CheckBox14.Value = Unchecked
Sheet1.CheckBox15.Value = Unchecked
Sheet1.CheckBox16.Value = Unchecked
Sheet1.CheckBox17.Value = Unchecked
Sheet1.CheckBox18.Value = Unchecked
Sheet1.CheckBox19.Value = Unchecked
Sheet1.CheckBox20.Value = Unchecked
Sheet1.CheckBox21.Value = Unchecked
Sheet1.CheckBox22.Value = Unchecked
Sheet1.CheckBox23.Value = Unchecked
Sheet1.CheckBox24.Value = Unchecked
Sheet1.CheckBox25.Value = Unchecked
Sheet1.CheckBox26.Value = Unchecked
Sheet1.CheckBox27.Value = Unchecked
Sheet1.CheckBox28.Value = Unchecked
Sheet1.CheckBox29.Value = Unchecked
Sheet1.CheckBox30.Value = Unchecked

Many thanks in advance

Chris
Looping
 
Dim Obj As OLEObject
For Each Obj In Sheet1.OLEObjects
If TypeOf Obj.Object Is MSForms.CheckBox Then
Obj.Object.Value = False
End If
Next Obj
----- René wrote: -----

How can use a loop to do the following:

Sheet1.CheckBox1.Value = Unchecked
Sheet1.CheckBox2.Value = Unchecked
Sheet1.CheckBox3.Value = Unchecked
Sheet1.CheckBox4.Value = Unchecked
Sheet1.CheckBox5.Value = Unchecked
Sheet1.CheckBox6.Value = Unchecked
Sheet1.CheckBox7.Value = Unchecked
Sheet1.CheckBox8.Value = Unchecked
Sheet1.CheckBox9.Value = Unchecked
Sheet1.CheckBox10.Value = Unchecked
Sheet1.CheckBox11.Value = Unchecked
Sheet1.CheckBox12.Value = Unchecked
Sheet1.CheckBox13.Value = Unchecked
Sheet1.CheckBox14.Value = Unchecked
Sheet1.CheckBox15.Value = Unchecked
Sheet1.CheckBox16.Value = Unchecked
Sheet1.CheckBox17.Value = Unchecked
Sheet1.CheckBox18.Value = Unchecked
Sheet1.CheckBox19.Value = Unchecked
Sheet1.CheckBox20.Value = Unchecked
Sheet1.CheckBox21.Value = Unchecked
Sheet1.CheckBox22.Value = Unchecked
Sheet1.CheckBox23.Value = Unchecked
Sheet1.CheckBox24.Value = Unchecked
Sheet1.CheckBox25.Value = Unchecked
Sheet1.CheckBox26.Value = Unchecked
Sheet1.CheckBox27.Value = Unchecked
Sheet1.CheckBox28.Value = Unchecked
Sheet1.CheckBox29.Value = Unchecked
Sheet1.CheckBox30.Value = Unchecked

Many thanks in advance

Rob Bovey
Looping
 
Hi René,

Assuming you want the loop to apply to all of the checkbox objects on
Sheet1, you could do it like this:

Dim objControl As OLEObject
For Each objControl In Sheet1.OLEObjects
If TypeOf objControl.Object Is MSForms.CheckBox Then
objControl.Object.Value = False
End If
Next objControl

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *


"René" wrote in message
...
How can use a loop to do the following:

Sheet1.CheckBox1.Value = Unchecked
Sheet1.CheckBox2.Value = Unchecked
Sheet1.CheckBox3.Value = Unchecked
Sheet1.CheckBox4.Value = Unchecked
Sheet1.CheckBox5.Value = Unchecked
Sheet1.CheckBox6.Value = Unchecked
Sheet1.CheckBox7.Value = Unchecked
Sheet1.CheckBox8.Value = Unchecked
Sheet1.CheckBox9.Value = Unchecked
Sheet1.CheckBox10.Value = Unchecked
Sheet1.CheckBox11.Value = Unchecked
Sheet1.CheckBox12.Value = Unchecked
Sheet1.CheckBox13.Value = Unchecked
Sheet1.CheckBox14.Value = Unchecked
Sheet1.CheckBox15.Value = Unchecked
Sheet1.CheckBox16.Value = Unchecked
Sheet1.CheckBox17.Value = Unchecked
Sheet1.CheckBox18.Value = Unchecked
Sheet1.CheckBox19.Value = Unchecked
Sheet1.CheckBox20.Value = Unchecked
Sheet1.CheckBox21.Value = Unchecked
Sheet1.CheckBox22.Value = Unchecked
Sheet1.CheckBox23.Value = Unchecked
Sheet1.CheckBox24.Value = Unchecked
Sheet1.CheckBox25.Value = Unchecked
Sheet1.CheckBox26.Value = Unchecked
Sheet1.CheckBox27.Value = Unchecked
Sheet1.CheckBox28.Value = Unchecked
Sheet1.CheckBox29.Value = Unchecked
Sheet1.CheckBox30.Value = Unchecked

Many thanks in advance

Chris
Looping
 
If you specifically need 1 to 30 use this

Dim Obj As OLEObjec
For Each Obj In Sheet1.OLEObject
If TypeOf Obj.Object Is MSForms.CheckBox The
If Len(Obj.Object.name) = 9 then x = Cint(Right(Obj.Object.name,1)
If Len(Obj.Object.name) = 10 then x = Cint(Right(Obj.Object.name,2))
If x = 1 And x < 31 Then Obj.Object.value = False
Next Ob

----- René wrote: ----

How can use a loop to do the following

Sheet1.CheckBox1.Value = Unchecke
Sheet1.CheckBox2.Value = Unchecke
Sheet1.CheckBox3.Value = Unchecke
Sheet1.CheckBox4.Value = Unchecke
Sheet1.CheckBox5.Value = Unchecke
Sheet1.CheckBox6.Value = Unchecke
Sheet1.CheckBox7.Value = Unchecke
Sheet1.CheckBox8.Value = Unchecke
Sheet1.CheckBox9.Value = Unchecke
Sheet1.CheckBox10.Value = Unchecke
Sheet1.CheckBox11.Value = Unchecke
Sheet1.CheckBox12.Value = Unchecke
Sheet1.CheckBox13.Value = Unchecke
Sheet1.CheckBox14.Value = Unchecke
Sheet1.CheckBox15.Value = Unchecke
Sheet1.CheckBox16.Value = Unchecke
Sheet1.CheckBox17.Value = Unchecke
Sheet1.CheckBox18.Value = Unchecke
Sheet1.CheckBox19.Value = Unchecke
Sheet1.CheckBox20.Value = Unchecke
Sheet1.CheckBox21.Value = Unchecke
Sheet1.CheckBox22.Value = Unchecke
Sheet1.CheckBox23.Value = Unchecke
Sheet1.CheckBox24.Value = Unchecke
Sheet1.CheckBox25.Value = Unchecke
Sheet1.CheckBox26.Value = Unchecke
Sheet1.CheckBox27.Value = Unchecke
Sheet1.CheckBox28.Value = Unchecke
Sheet1.CheckBox29.Value = Unchecke
Sheet1.CheckBox30.Value = Unchecke

Many thanks in advanc

Bernie Deitrick
Looping
 
Rene,

For checkboxes from the Forms commandbar:

Dim i As Integer
For i = 1 To 30
ActiveSheet.CheckBoxes("Check Box " & i).Value = False
Next i

Just make sure the ("Check Box " & i) has the correct spacing.

HTH,
Bernie
MS Excel MVP

"René" wrote in message
...
How can use a loop to do the following:

Sheet1.CheckBox1.Value = Unchecked
Sheet1.CheckBox2.Value = Unchecked
Sheet1.CheckBox3.Value = Unchecked
Sheet1.CheckBox4.Value = Unchecked
Sheet1.CheckBox5.Value = Unchecked
Sheet1.CheckBox6.Value = Unchecked
Sheet1.CheckBox7.Value = Unchecked
Sheet1.CheckBox8.Value = Unchecked
Sheet1.CheckBox9.Value = Unchecked
Sheet1.CheckBox10.Value = Unchecked
Sheet1.CheckBox11.Value = Unchecked
Sheet1.CheckBox12.Value = Unchecked
Sheet1.CheckBox13.Value = Unchecked
Sheet1.CheckBox14.Value = Unchecked
Sheet1.CheckBox15.Value = Unchecked
Sheet1.CheckBox16.Value = Unchecked
Sheet1.CheckBox17.Value = Unchecked
Sheet1.CheckBox18.Value = Unchecked
Sheet1.CheckBox19.Value = Unchecked
Sheet1.CheckBox20.Value = Unchecked
Sheet1.CheckBox21.Value = Unchecked
Sheet1.CheckBox22.Value = Unchecked
Sheet1.CheckBox23.Value = Unchecked
Sheet1.CheckBox24.Value = Unchecked
Sheet1.CheckBox25.Value = Unchecked
Sheet1.CheckBox26.Value = Unchecked
Sheet1.CheckBox27.Value = Unchecked
Sheet1.CheckBox28.Value = Unchecked
Sheet1.CheckBox29.Value = Unchecked
Sheet1.CheckBox30.Value = Unchecked

Many thanks in advance

Bob Phillips[_6_]
Looping
 
or

For i = 1 To 30
ActiveSheet.OLEObjects("Checkbox" & i).Object.Value = False
Next i

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"chris" wrote in message
...
Dim Obj As OLEObject
For Each Obj In Sheet1.OLEObjects
If TypeOf Obj.Object Is MSForms.CheckBox Then
Obj.Object.Value = False
End If
Next Obj
----- René wrote: -----

How can use a loop to do the following:

Sheet1.CheckBox1.Value = Unchecked
Sheet1.CheckBox2.Value = Unchecked
Sheet1.CheckBox3.Value = Unchecked
Sheet1.CheckBox4.Value = Unchecked
Sheet1.CheckBox5.Value = Unchecked
Sheet1.CheckBox6.Value = Unchecked
Sheet1.CheckBox7.Value = Unchecked
Sheet1.CheckBox8.Value = Unchecked
Sheet1.CheckBox9.Value = Unchecked
Sheet1.CheckBox10.Value = Unchecked
Sheet1.CheckBox11.Value = Unchecked
Sheet1.CheckBox12.Value = Unchecked
Sheet1.CheckBox13.Value = Unchecked
Sheet1.CheckBox14.Value = Unchecked
Sheet1.CheckBox15.Value = Unchecked
Sheet1.CheckBox16.Value = Unchecked
Sheet1.CheckBox17.Value = Unchecked
Sheet1.CheckBox18.Value = Unchecked
Sheet1.CheckBox19.Value = Unchecked
Sheet1.CheckBox20.Value = Unchecked
Sheet1.CheckBox21.Value = Unchecked
Sheet1.CheckBox22.Value = Unchecked
Sheet1.CheckBox23.Value = Unchecked
Sheet1.CheckBox24.Value = Unchecked
Sheet1.CheckBox25.Value = Unchecked
Sheet1.CheckBox26.Value = Unchecked
Sheet1.CheckBox27.Value = Unchecked
Sheet1.CheckBox28.Value = Unchecked
Sheet1.CheckBox29.Value = Unchecked
Sheet1.CheckBox30.Value = Unchecked

Many thanks in advance

Soo Cheon Jheong
Looping
 
Rene,

Dim i As Integer
For i = 1 To 30
ActiveSheet.OLEObjects("CheckBox" & i).Object.Value = False
Next


--
Soo Cheon Jheong
http://excel.hompy.com
Seoul, South Korea
_ _
^¢¯^
--




All times are GMT +1. The time now is 03:12 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com