Help setting value for several checkboxes

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Brian - 13 Jun 2007 02:55 GMT

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

Reply to this Message

Rick Rothstein (MVP - VB) - 13 Jun 2007 05:01 GMT

> 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
[quoted text clipped - 24 lines]
>
> 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

Reply to this Message

Rick Rothstein (MVP - VB) - 13 Jun 2007 06:29 GMT

> 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
[quoted text clipped - 16 lines]
> 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

Reply to this Message

Rick Rothstein (MVP - VB) - 13 Jun 2007 09:06 GMT

>> If you are trying to reset EVERY CheckBox (as opposed to just some of
>> them), you can use this construction...
[quoted text clipped - 13 lines]
> 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

Reply to this Message

Brian - 13 Jun 2007 13:07 GMT

>> 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
[quoted text clipped - 47 lines]
>
> 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.

Reply to this Message

Rate this thread: