Checking Checkboxes loop not working

I have a section of code which i must be missing something on.  I have 7 activex checkboxes on a sheet called Datasheets which are grouped together for easy of moving them around.  I would like to run a loop to test their state but I get an error "Object doesnt support this propert or method"  I have tried Me!thischkbox instead but got an "Invalid use error"
For i = 1 To 7
thischkbox = "Checkbox" & i
If Sheets("Datasheet").thischkbox = True Then
 MsgBox ("Checked")    'Code here for when box IS CHECKED
 Else
 MsgBox ("UnChecked")    'Code here for when box IS NOT CHECKED
End If
Next i

Open in new window

LVL 1
simonwaitAsked:
Who is Participating?
 
TommySzalapskiCommented:
I used this code and it worked just fine.
If Sheets("Datasheet").OLEObjects(thischkbox).Object.Value = True Then
Maybe you need to tell it thischkbox is a string? Is it possible you spelled thischkbox wrong when you tested it? Try it like this
If Sheets("Datasheet").OLEObjects("Checkbox" & i).Object.Value = True Then

timesheetTest.xls
0
 
TommySzalapskiCommented:
thischkbox = "Checkbox" & i
It is creating a string that contains "Checkbox1" etc. You can't do what you are trying to do.
You need something like
If Sheets("Datasheet").Shapes(thischkbox).Value = True
0
 
TommySzalapskiCommented:
The Shapes object lets you index based on strings which is why that works. You can't use strings to reference variable names.
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
TommySzalapskiCommented:
Okay, I didn't test it and it's not quite right. You need this:
If Sheets("Datasheet").OLEObjects(thischkbox).Object.Value = True Then
0
 
simonwaitAuthor Commented:
Cheers Tommy

That gives.  Unable to get the OLEObjects property of the worksheet class.  As a test I replaced the thischkbox with CheckBox1 but still got the same result
0
 
TommySzalapskiCommented:
Hmm. It works for me. What version of Excel do you have? How did you create the checkbox? Can you post the spreadsheet?
0
 
TommySzalapskiCommented:
Does this work? One works for the Forms checkbox and the other for the ActiveX checkbox.
If Sheets("Datasheet").Shapes(thischkbox).Value = lxOn Then
0
 
TommySzalapskiCommented:
That should be True not xlOn
0
 
simonwaitAuthor Commented:
I get "Object doesnt support property or method".  I am using Excel 2007.  Created checkbox using the ActiveX control in the developer tab.  So when I click on the 1st checkbox I get a name of CheckBox1 and EMBED("Forms.CheckBox.1","") in the formula bar.

The workbook actually links to a database with login screens and stuff so will try to create an EE version without any sensitive data
0
 
simonwaitAuthor Commented:
0
 
simonwaitAuthor Commented:
Sorry forgot to put that the code in question is in the sub read() in the SQL module
0
 
simonwaitAuthor Commented:
No it isnt its in the Send sub!  Sorry
0
 
nick-pecoraroCommented:
Try using the .Controls object.

Sheets("Datasheet").Controls(thischkbox)

This should allow you to reference objects by name.

For i = 1 To 7
thischkbox = "Checkbox" & i
If Sheets("Datasheet").Controls(thischkbox) = True Then
 MsgBox ("Checked")    'Code here for when box IS CHECKED
 Else
 MsgBox ("UnChecked")    'Code here for when box IS NOT CHECKED
End If
Next i

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.