Loop through checkBox on worksheet

  • Thread starter Thread starter bman
  • Start date Start date
B

bman

I have created some user forms that gather information into a worksheet.
The worksheet has checkboxes that gets set based on the answers from
the user form. I used the checkboxes from the control toolbox. I have
mutiple checkboxes in some cells.
Question: How can I loop through the checkboxes in the worksheet and
set the enable property to false. I know how you can do this on a user
form with controls but not sure how on a worksheet.
Thanks in advance for any help!
 
Try this

Sub test2()
For Each obj In ActiveSheet.OLEObjects
If TypeOf obj.Object Is MSForms.CheckBox Then
obj.Object.Value = True
End If
Next
End Sub
 
Oops

Change it to False

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)




Ron de Bruin said:
Try this

Sub test2()
For Each obj In ActiveSheet.OLEObjects
If TypeOf obj.Object Is MSForms.CheckBox Then
obj.Object.Value = True
End If
Next
End Sub
Click to expand...
 
Thanks for the info. I just made a few changes to accomodate the enabl
part.
Sub test2()
Dim mycheck As MSForms.CheckBox
For Each obj In ActiveSheet.OLEObjects
If TypeOf obj.Object Is MSForms.CheckBox Then
Set mycheck = obj.Object
mycheck.Enabled = False
End If
Next
End Sub

Appreciate your help
 
Back
Top