Select All Checkboxes on Excel worksheet

[smma]

Hello! I have an Excel worksheet with 6 checkboxes on them. The first checkbox is supposed to check all checkboxes when it is clicked. I have the code below:

Private Sub Checkbox0_Click()
If CheckBox0.Value = True Then
Dim i As Integer
For i = 1 To 5
CheckBox(i).Value = True
Next i
End If
End Sub

However, on running it in VBA, it gives me a compile error saying "Sub or Function is not defined". What is the right syntax to loop each check box and make them all true?

 

[cadcamguy]

Looks like you are trying to do a control array and vba doesn't support that....

..take a look at this thread

http://www.eng-tips.com/viewthread.cfm?qid=192229&page=1

 

[smma]

Good to know CCG. Since there are only five other checkboxes, my code would only be two lines longer, as follows:

If Checbox0.Value = True Then
Checkbox1.Value = True
Checkbox2.Value = True
Checkbox3.Value = True
Checkbox4.Value = True
Checkbox5.Value = True
End If

Problem solved.

 

[cummings54]

Although impossible to create an a array of controls as SMMA says, it is possible to loop through a collection of controls on a worksheet. For example

Private Sub CheckBox0_Click()
  For Each ct In Me.OLEObjects
    With ct.Object
      If .GroupName = "A" Then .Value = CheckBox0.Value
    End With
  Next ct
End Sub

In the sample I've given each of the checkboxes to be controled by checkbox0 the same groupname, "A"