Help setting value for several checkboxes

[Brian]

Hi everyone,

I am new to VBA as well as being new to this newsgroup. I am having a
problem trying to set a group of checkboxes to a common value (on clicking a
button). I expected the following code to work:

Private Sub CLRALL_Click()
Dim i As Integer
For i = 1 To 7
CheckBox(i).Value = 0
Next i

End Sub

However, it brings up the following Error Message:
Compile Error: Sub or Function not defined.

The Help Menu is very unhelpful and I don't understand it. I have only been
able to get my code to work this way:

Private Sub CLRALL_Click()
CheckBox1.Value = 0
CheckBox2.Value = 0
CheckBox3.Value = 0
CheckBox4.Value = 0
CheckBox5.Value = 0
CheckBox6.Value = 0
CheckBox7.Value = 0

Can anyone please steer me in the right direction here?

Best regards,
Brian

 

[Rick Rothstein \(MVP - VB\)]

I am new to VBA as well as being new to this newsgroup. I am having a

problem trying to set a group of checkboxes to a common value (on clicking
a button). I expected the following code to work:

Private Sub CLRALL_Click()
Dim i As Integer
For i = 1 To 7
CheckBox(i).Value = 0
Next i

End Sub

However, it brings up the following Error Message:
Compile Error: Sub or Function not defined.

The Help Menu is very unhelpful and I don't understand it. I have only
been able to get my code to work this way:

Private Sub CLRALL_Click()
CheckBox1.Value = 0
CheckBox2.Value = 0
CheckBox3.Value = 0
CheckBox4.Value = 0
CheckBox5.Value = 0
CheckBox6.Value = 0
CheckBox7.Value = 0

Can anyone please steer me in the right direction here?

The syntax you tried... CheckBox(i)... failed because the CheckBoxes are not
an array and the parentheses notation is how an array is addressed. To do
what you originally tried, you can use this construction...

Private Sub CLRALL_Click()
Dim X As Long
For X = 1 To 7
Me.Controls("CheckBox" & Format$(X)).Value = 0
Next
End Sub

If you are trying to reset EVERY CheckBox (as opposed to just some of them),
you can use this construction...

Private Sub CLRALL_Click()
Dim TB As Control
For Each TB In Me.Controls
TB.Value = 0
Next
End Sub

Rick

 

[Rick Rothstein \(MVP - VB\)]

The syntax you tried... CheckBox(i)... failed because the CheckBoxes are

not an array and the parentheses notation is how an array is addressed. To
do what you originally tried, you can use this construction...

Private Sub CLRALL_Click()
Dim X As Long
For X = 1 To 7
Me.Controls("CheckBox" & Format$(X)).Value = 0
Next
End Sub

If you are trying to reset EVERY CheckBox (as opposed to just some of
them), you can use this construction...

Private Sub CLRALL_Click()
Dim TB As Control
For Each TB In Me.Controls
TB.Value = 0
Next
End Sub

I guess I should expand this answer a little bit. The above code works if
your CheckBoxes and CommandButton are on a UserForm. If, however, you have
placed the CheckBoxes directly on the worksheet instead of on a UserForm,
then the above two code examples must be modified as follows

' For the seven specific CheckBoxes on the worksheet
Private Sub CLRALL_Click()
Dim x As Long
For x = 1 To 7
OLEObjects("CheckBox" & Format$(x)).Object.Value = False
Next
End Sub

' For every CheckBox on the worksheet
Private Sub CLRALL_Click()
Dim O As Object
For Each O In Me.OLEObjects
O.Object.Value = False
Next
End Sub


Rick

 

[Rick Rothstein \(MVP - VB\)]

If you are trying to reset EVERY CheckBox (as opposed to just some of

' For every CheckBox on the worksheet
Private Sub CLRALL_Click()
Dim O As Object
For Each O In Me.OLEObjects
O.Object.Value = False
Next
End Sub

Whoops! I overlooked something... the above two procedures need slight
modifications...

' For every CheckBox on a UserForm
Private Sub CLRALL_Click()
Dim CB As Control
For Each CB In Me.Controls
If TypeName(CB) = "CheckBox" Then CB.Value = 0
Next
End Sub

' For every CheckBox on the worksheet
Private Sub CLRALL_Click()
Dim O As Object
For Each O In Me.OLEObjects
If TypeName(O.Object) = "CheckBox" Then O.Object.Value = False
Next
End Sub

The other procedures (where the name of the controls are constructed) both
work fine as originally posted.

Rick

 

[Brian]

The syntax you tried... CheckBox(i)... failed because the CheckBoxes are
not an array and the parentheses notation is how an array is addressed. To
do what you originally tried, you can use this construction...

Private Sub CLRALL_Click()
Dim X As Long
For X = 1 To 7
Me.Controls("CheckBox" & Format$(X)).Value = 0
Next
End Sub

If you are trying to reset EVERY CheckBox (as opposed to just some of
them), you can use this construction...

Private Sub CLRALL_Click()
Dim TB As Control
For Each TB In Me.Controls
TB.Value = 0
Next
End Sub

Rick

Hi Rick,

thank you very much for your help, it is most appreciated!! I had placed my
check boxes directly onto a worksheet this time around and your appropriate
code works really well for me. I had actually placed the check boxes on a
pop-up form previously, so knowing how to code that next time is most
helpful for me too. I guess I need to study your code carefully now to
understand how it works.

Thanks again,
Brian.