Simple for next

cmparnell

New Member
Joined
Jan 21, 2009
Messages
27
Just a lil help.

Checkbox1, checkbox2, checkbox3

I want a for next loop to call each checkbox. How do i go about that?

Code:
        For i = 1 To 3
            CheckBox(i).Enabled = False
        Next i

but i dont know how to reference the checkbox properly. Anyone know how?
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Hi,

Where are the checkboxes located - on a userform, on a sheet?

If they're on a sheet, did you add them from the control toolbox or from the forms toolbar?
 
Upvote 0
Hi,

In that case give this a whirl:
Code:
Sub example()
     Dim oleObj As OLEObject
     
     For Each oleObj In Sheet1.OLEObjects
          If TypeOf oleObj.Object Is MSForms.CheckBox Then
               oleObj.Enabled = False
          End If
     Next oleObj
     
End Sub

A reference to the MSForms 2.0 Object Library is required (it should already be referenced since you have these controls embedded on your sheet).

Hope that helps...
 
Upvote 0
It most certainly does. For interest sake though if i only wanted to disable certain boxes say from 1 to 5 how could you limit it? If its not an easy answer dont waste your time on a solution...

Thanks again.
 
Upvote 0
Hi,

Glad to help.

There are plenty of tricks we can use to selectively disable particular checkboxes. You could check the checkbox name. Or, before runtime, you could give the textboxes which are to be disabled the same groupname (see groupname in the properties window by going into design mode, right clicking on a checkbox and choosing properties), and then we can check the groupname in the code, eg.
Code:
Sub example()
     Dim oleObj As OLEObject
     
     For Each oleObj In Sheet1.OLEObjects
     
          With oleObj.Object
               If .GroupName = "ToDisable" Then .Enabled = False
          End With
          
     Next oleObj
     
End Sub

Hope that helps...
 
Upvote 0
Hi, I have the same question, but then for checkboxes on a userform. would love to see an example of that.

thanks !
 
Upvote 0
Hi Sean,

Welcome to the forum. :)

If they're on a userform then you can use the userform's controls collection:
Rich (BB code):
    Dim ctl As MSForms.Control
    
    For Each ctl In UserForm1.Controls
        If TypeOf ctl Is MSForms.CheckBox Then
            ctl.Enabled = False
        End If
    Next ctl

Hope that helps...
 
Upvote 0

Forum statistics

Threads
1,203,435
Messages
6,055,353
Members
444,781
Latest member
rishivar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top