Solved: Checkbox control(s)........looping

[sconly]

I have a worksheet with numerous (control toolbox) checkboxes on it.

What I am trying to achieve is by clicking one checkbox (lets call it the master) i want to enable/disable the other checkboxes (whos name starts with 'chkDam').

My code (see below) doesn't seem to want to work.......Any advice?

Dim ctlC As Control

For Each ctlC In Controls
If chkNoInjury.Value = True Then
MsgBox ctlC.Name
If TypeOf ctlC Is CheckBox Then
If Left(ctlC.Name, 6) = "chkDam" Then
ctlC.Enabled = False
End If
End If
Else
If TypeOf ctlC Is CheckBox Then
If ctlC.Name Like "chkDam*" Then
ctlC.Enabled = True
End If
End If
End If
Next ctlC


Thanks

PS. I get the following error 'Run-time error 424 Object required'

[JKwan]

I think this will help you out. Since your controls are on the spreadsheet

http://www.rondebruin.nl/controlsobjectsworksheet.htm

[GTO]

I have never used TypeOf, but that is interesting. As to the rest, a bit of a stretch for me, but I do not think you can define is as a 'Control' when used on a sheet.

See if this helps at all, and I'll certainly be reading the nice link JKwan provided as well :-)


Sub exa()
Dim ctlC As OLEObject

For Each ctlC In Me.OLEObjects
'// CHange name here //
If Me.CheckBox1.Value = True Then
MsgBox ctlC.Name
If ctlC.progID = "Forms.CheckBox.1" Then
If Left(ctlC.Name, 6) = "chkDam" Then
ctlC.Enabled = False
End If
End If
Else
If ctlC.progID = "Forms.CheckBox.1" Then
If ctlC.Name Like "chkDam*" Then
ctlC.Enabled = True
End If
End If
End If
Next ctlC
End Sub

Hope that helps a little,

Mark

[lucas]

example using Mark's code and changing the name of the first checkbox

see attached.

[sconly]

Thanks for the replies peeps, much appreciated.

I have just one more little question. How can i get the value/checked state of the ones with 'chkDam' in the name? I only ask because I've tried ctlC.Value but vba doesn't like it.

Ta!

[GTO]

Try this at the bottom of the loop, right above Next.

MsgBox ctlC.Name & Chr(32) & "=" & Chr(32) & ctlC.Object.Value

Mark

[sconly]

Nice one........Thanks!!

[lucas]

be sure to mark your thread solved using the thread tools at the top of the page.