Looping

R

René

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
 
C

chris

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
 
R

Rob Bovey

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 *
 
C

chris

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
 
B

Bernie Deitrick

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
 
B

Bob Phillips

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

End without Next Error 12
Print selection using check boxes 17
Togglebutton to change Checkboxes 3
RPC ERROR - 1ST TIMES WORKS FINE - 2ND TIME ERRORS OUT 0
Multiple CheckBoxes can be selected require additional info if sel 1
Is there an easier way of doing this?? 0
Class Module Problem 2

Top