469,304 Members | 2,018 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,304 developers. It's quick & easy.

VBA Excel - checkbox property for worksheet class

reginaldmerritt
201 100+
Hi,

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

Expand|Select|Wrap|Line Numbers
  1. For i = 15 To 103
  2.     ActiveSheet.CheckBoxes("Check Box " & i).Value = False
  3. Next
  4.  
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.
Mar 11 '11 #1

✓ answered by 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.

11 15519
reginaldmerritt
201 100+
Thanks ADezii,

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

Expand|Select|Wrap|Line Numbers
  1. Range("E24").Value = False
  2. Range("E25").Value = False
  3. Range("E26").Value = False
  4. Range("E27").Value = False
  5. Range("E28").Value = False
  6. Range("E29").Value = False
  7.  
and so on for each related cell. Be intrested if there is a better way.
Mar 11 '11 #2
ADezii
8,800 Expert 8TB
Expand|Select|Wrap|Line Numbers
  1. Dim ole As OLEObject
  2. Dim intCtr As Integer
  3.  
  4. 'Assuming your Check Boxes are name CheckBox15, CheckBox16,...CheckBox103
  5. 'You cannot have Spaces in Control Names, such as: 'Check Box 99'
  6. For intCtr = 15 To 103
  7.   ActiveSheet.OLEObjects("CheckBox" & CStr(intCtr)).Object.Value = False
  8. Next
Mar 11 '11 #3
reginaldmerritt
201 100+
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.
Mar 11 '11 #4
reginaldmerritt
201 100+
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.
Mar 11 '11 #5
ADezii
8,800 Expert 8TB
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.
Attached Files
File Type: zip Test.zip (23.1 KB, 342 views)
Mar 12 '11 #6
reginaldmerritt
201 100+
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.
Attached Files
File Type: zip DiplomaUnitSelectionTool(QCF).zip (72.3 KB, 172 views)
Mar 21 '11 #7
ADezii
8,800 Expert 8TB
@reginaldmerritt - I have a Theory, but first see if the following works (converted to Excel 2003 Format).
Attached Files
File Type: zip DiplomaUnitSelectionTool.zip (85.0 KB, 271 views)
Mar 21 '11 #8
reginaldmerritt
201 100+
Many thanks ADezii.

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

What did you do ????
Mar 30 '11 #9
ADezii
8,800 Expert 8TB
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.
Mar 30 '11 #10
reginaldmerritt
201 100+
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
Mar 31 '11 #11
ADezii
8,800 Expert 8TB
The pleasure was all mine, glad it all worked out for you.
Apr 1 '11 #12

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

reply views Thread by Steve Menard | last post: by
reply views Thread by Bert | last post: by
9 posts views Thread by tirrell payton | last post: by
6 posts views Thread by Ang | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by harlem98 | last post: by
1 post views Thread by Geralt96 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.