Array for clearing checkboxes

[Guest]

I have a number of checkboxes on my page, and when one is ticked, i am using
code to clear the others, and if the subject they relate to isn't relevant, i
hide them.
I have used arrays in the past to clear these in bulk.

Private Sub BlankCheckboxes(which)

'routine to specify whether checkboxes have a null value

Dim i As Long
For i = LBound(which, 1) To UBound(which, 1)
Me.Controls("Checkbox" & which(i)).Value = False
Next i
End Sub

Then:

Private Sub CheckBox1_Click()

If CheckBox1.Value = True Then
BlankCheckboxes Array(2, 3, 4)
End If
End Sub

When i try this i get Compile Error, Method or data member not found.

I know it worked before on another spreadsheet, am i missing something
obvious?

Help appreciated!

Richard

 

[Tom Ogilvy]

Your code worked fine for me when I had it all in the userform module.

The checkboxes are on a Userform?

 

[Guest]

Hi Tom, no they are on one of the sheets (the 3rd). Will this still work?
Will i have to reference the sheet (sheet3.checkbox)?
I was trying to avoid using a userform this time.

Richard

 

[Tom Ogilvy]

Try it this way:

Private Sub BlankCheckboxes(which)

'routine to specify whether checkboxes have a null value

Dim i As Long
For i = LBound(which, 1) To UBound(which, 1)
Me.OleObjects("Checkbox" & which(i))Object..Value = False
Next i
End Sub

Then:

Private Sub CheckBox1_Click()

If CheckBox1.Value = True Then
BlankCheckboxes Array(2, 3, 4)
End If
End Sub

With the code in the sheet module.

 

[Guest]

Tom, i'm not sure if i'm doing this correctly, but i'm getting 'Expected End
of Statement' error before Object.Value

I took out one of the full stops between object and value as i assume this
was a typo.

 

[Tom Ogilvy]

Apparently there was a typo. It should be:

Me.OleObjects("Checkbox" & which(i)).Object.Value = False

 

[Guest]

Thanks Tom. All is working well now. Appreciate the help.