At least one checkbox must be checked - "OLEObjects error"

Dear friends,

From this very forum I vicariously acquired the following vba which could ensure that at least one checkbox had been checked in a range. For the sake of simplicity, I am trialing this in a new excel sheet(2007) with just 5 check-boxes - "check box 1", "check box 2", etc.

Please Login or Register  to view this content.

The error I am getting is as follows: Method oleobjects of object _worksheet failed

The debugger signals that the line If .OLEObjects("Checkbox" & i).Object.Value = "True" Then is the problem.
So it seems to me that this line is not properly referencing my boxes .

Could anybody please advise how I should adapt the code to make it work? Additionally, what does oleobject mean?



N.B, seeing as the code was for a private macro and i didn't want that, I also changed the first line to

Please Login or Register  to view this content.

Hopefully this hasn't caused any issues.

I suspect you have used Forms controls rather than ActiveX ones, in which case you can refer to the Checkboxes collection instead:

Please Login or Register  to view this content.

If you have in fact used Activex, then the names are incorrect in your code.

Hi,

First check that the exact spelling of the Check Boxes. i.e. are they "Checkbox1", "Check Box 1", etc and make sure your code uses the correct spelling with any embedded spaces.

I suspect you are using the Forms Control Check Box object, and in which case you should use

Please Login or Register  to view this content.

Your code would have worked had you used the Activex Check Box control. These Activex controls seem more robust so unless you have reasons otherwise I'd stick to those.

An oleobject is simply any external object (like an Excel control), picture image, Word document, Excel spreadsheet, etc..

Originally Posted by Richard Buttrey

These Activex controls seem more robust so unless you have reasons otherwise I'd stick to those.

I couldn't disagree more.

@Romperstomper

... all in the eye of the beholder

Perhaps 'flexible' would have been a better adjective...

Hi,

Exactly what does your CheckBox say in the Name box when you select it?
By default it presumably says "Check Box 1"
I don't think the case matters but try Box rather than box.

Upload your workbook if it's still a problem.

Originally Posted by Richard Buttrey

Perhaps 'flexible' would have been a better adjective...

or "unstable"

This code:

Please Login or Register  to view this content.

works for me with your sample workbook.

@romperstomper

Moi aussi!

@ Henry

There was no code in the workbook attached, but the code that you show in the post has

Formula:

Please Login or Register  to view this content.

yet your checkboxes have the default names of "Check Box 1" etc. i.e with a space either side of 'Box.' As mentioned you'll need to ensure your code says

Please Login or Register  to view this content.

or romper stompers equivalent

Please Login or Register  to view this content.

If you only have two questions, then you could just add another loop:

Please Login or Register  to view this content.

For multiple questions I would use a separate validation function, such as this:

Please Login or Register  to view this content.