VBA Excel - checkbox property for worksheet class

[reginaldmerritt]

Hi,

Not used vb in excel before. Trying to get a button to set a lot of checkboxes to false.

For i = 15 To 103
    ActiveSheet.CheckBoxes("Check Box " & i).Value = False
Next
 

I get the following message when i try to run the code.

Unbale to get the Checkbox property for worksheet class.

I searched online but don't really know what i'm looking for and all the solutions to errors for worksheet class seemed to centre around naming of sheets, but i haven't refered to any sheets in the code.

Any help would be great, thanks.

[reginaldmerritt]

Thanks ADezii,

I've managed to get round it by selecting each realted cell and setting that to false so i'm using

Range("E24").Value = False
Range("E25").Value = False
Range("E26").Value = False
Range("E27").Value = False
Range("E28").Value = False
Range("E29").Value = False
 

and so on for each related cell. Be intrested if there is a better way.

[ADezii]

Dim ole As OLEObject
Dim intCtr As Integer
 
'Assuming your Check Boxes are name CheckBox15, CheckBox16,...CheckBox103
'You cannot have Spaces in Control Names, such as: 'Check Box 99'
For intCtr = 15 To 103
  ActiveSheet.OLEObjects("CheckBox" & CStr(intCtr)).Object.Value = False
Next

[reginaldmerritt]

Really, Excel just created the Check Boxes names with spaces.

Thanks very much ADezii, very much appreciated.

I'll give that a go. I think i'll stick to Access programming.

[reginaldmerritt]

Comes up with the same message but this time for the OLEObjects.

Unable to get the OLEObjects property for Worksheet class

Oh well, thanks anyway. I can just use my other method for now.

[ADezii]

It's really puzzling to me as to why the Code is not working, so I created a Demo with three slightly different approaches in the hope that one may work for you. Download the Attachment, then see what happens from there.

[reginaldmerritt]

Thats great, many thanks for your time.

Sorry for not replying sooner, i've been away in Dublin on Holiday over the last few weeks.

The workbook you uploaded works fine but can't get the code to work in my workbook.

Active Sheet method gives me the same error message:
Unable to get the OLEObjects property for Worksheet class

Explicit Reference method does nothing at all

Loop and Filter method gives me the following error:
Method 'OLEObjects' of object '_Worksheet' failed

I'm also unable to save the worksheet as a normal excel document and have to save it as a macro enabled workbook , even though I'm not using any macros??; otherwise all the code is removed.

Must be something wrong with my workbook.

[ADezii]

@reginaldmerritt - I have a Theory, but first see if the following works (converted to Excel 2003 Format).

[reginaldmerritt]

Many thanks ADezii.

The new button and check boxes you have put in have worked.

What did you do ????

[ADezii]

The Original Check Boxes were created from the Forms Toolbar and not from the Controls Toolbox Toolbar. The Check Boxes from the Controls Toolbox are true ActiveX Controls, unlike those from the Forms Toolbar. I'm assuming that that was the difference in some manner.

[reginaldmerritt]

That was my first thought but when i tried to create ActiveX contorls on my own version of the workbook i still got the same error, but it works on your version.

hmmm., So back to the drawing board, i turned my version back to Excel 2003 and deleted all the checkboxs and started from scratch with new ActiveX chcekboxes. Started with just 5 and ran the code and hey presto it worked.

Turns out that i must have had missing checkboxs from the sequence with in the loop. i.e. in a for next loop
For i = x to y, y was to high, no checkbox existed with that number and this would cause the same error as trying to use the code with some Form Control checkboxes.

I now have all the checkboxes as ActiveX controls and in sequence as well. Everything working well.

Much appreciation for all your help and time ADezii

[ADezii]

The pleasure was all mine, glad it all worked out for you.