Selecting all checkboxes on a form using VB

[LeighAnne]

Using Excel 97, I have set up a form that when a checkbox is ticked (= TRUE), then all other checkboxes on that form also become ticked (= TRUE).

I need to be able to say.....

For each CheckBox in MyForm
CheckBox.value = TRUE

I also don't know whether I need to declare "MyForm", and if I do what to declare it as?????

Help would be very much appreciated.

Leigh

 

[kylua]

This is so easy and I was using the tag function and all sorts before I got here.

Private Sub CheckBox1_Click()

For Each CheckBox In UserForm1.Controls
CheckBox.Value = True
Next

End Sub

If you want to limit this to groups of checkboxes, use the tag. I wouldn't want my idea to go to waste.
(A bit of an eye opener on some way convoluted coding I did way back on a form with about 200 controls. It could have been so much easier. We live and learn.)

Life: Loathe it ignore it, you can't like it.

 

[rmikesmith]

Leigh,

Set up your "master" checkbox's Click event handler as follows:

Private Sub chkMaster_Click()
Dim Ctrl As Control

  With frmCheckboxDemo
    For Each Ctrl In .Controls
      If TypeName(Ctrl) = &quot;CheckBox&quot; And Ctrl.Name <> &quot;chkMaster&quot; Then
        Ctrl.Value = .chkMaster.Value
      End If
    Next Ctrl
  End With
  
End Sub

Change the form and master checkbox references to your own. Whenever the checked state of the master checkbox is changed, all other checkboxes on the form will mirror it.

HTH
Mike

 

[kylua]

If TypeName(Ctrl) = "CheckBox"

So that's how you get the control type!

 

[rmikesmith]

kyula,

Rather sneaky of Microsoft, wouldn't you say?

Oddly, there are no collections of individual control types; i.e. you cant use something like

Dim Chk As CheckBox

  For Each Chk In UserForm1.CheckBoxes

  Next

Therefore, if you have other controls on your Userform (almost certainly) that you don't want to affect (or they don't support a particular property) then this is the only way to differentiate them.

Regards,
Mike

 

[kylua]

Arrgghh. You're right. I really should have tested that. I whacked a label on my form and the program died.
I was making it up as I went along.
I've learnt a bit more. Now shoot my new manager who would rather have me doing admin instead of saving the company 10s of thousands and I'll be able to use it.