Cycle through checkboxes

[Guest]

Hello
I'm running into some trouble when trying to use checkboxes
Here's my problem which I try to solve
Students hand in their reports from the lab, teaching assistants enter the results (yes/no) into an excel worksheet. Now, this worksheet must be done in a way that it is easy to use (such as cliciking checkboxes) and does not require the macros to be enabled (thus no userform works)
After these worksheets are collected, I want to be able run a simple macro which will

1. check the answers vs. a databas
(The database would be something like this with checkboxe
1. YNNYY
2. NNYYNY...

2. create an output where it shows the student's answers and below if they are correct or not

However, right now I'm stuck on #1. I couldn't find a way to cycle through the checkboxes, find out if they are checked or not. If I refer to them as members of the Shapes collection I can cycle, but cannot check the state. If I refer to them as objects on the worksheet (worksheets(1).checkbox1...) then I cannot cycle it. Does anybody have any suggestions? Thanx.

 

[Doug Glancy]

Zsolt,

This took me way longer to figure out than it should - had to look through
numerous Google Group postings to find the answer. There was an extra
"Object" in the syntax.
Anyways, this lets you set a constant for the number of checkboxes and then
cycles through, assuming they're all named CheckBox1, CheckBox2, etc, and
put the result in the student_answers array:

Sub test()

Const NUM_ANSWERS = 3
Dim student_answers(1 To NUM_ANSWERS) As Boolean
Dim i As Long

For i = 1 To NUM_ANSWERS
student_answers(i) = Sheet1.OLEObjects("CheckBox" & i).Object.Value
Debug.Print student_answers(i)
Next i

End Sub


hth,

Doug

Hello,
I'm running into some trouble when trying to use checkboxes.
Here's my problem which I try to solve:
Students hand in their reports from the lab, teaching assistants enter the

results (yes/no) into an excel worksheet. Now, this worksheet must be done
in a way that it is easy to use (such as cliciking checkboxes) and does not
require the macros to be enabled (thus no userform works).

After these worksheets are collected, I want to be able run a simple macro which will:

1. check the answers vs. a database
(The database would be something like this with checkboxes
1. YNNYYN
2. NNYYNY...)

2. create an output where it shows the student's answers and below if they are correct or not.

However, right now I'm stuck on #1. I couldn't find a way to cycle through

the checkboxes, find out if they are checked or not. If I refer to them as
members of the Shapes collection I can cycle, but cannot check the state. If
I refer to them as objects on the worksheet (worksheets(1).checkbox1...)
then I cannot cycle it. Does anybody have any suggestions? Thanx.

 

[NickHK]

Zsolt,
You do not need to cycle through the check boxes if you set their "Linked
Cell" property. You can then read that cell's value and process as you wish.

NickHK

Hello,
I'm running into some trouble when trying to use checkboxes.
Here's my problem which I try to solve:
Students hand in their reports from the lab, teaching assistants enter the

results (yes/no) into an excel worksheet. Now, this worksheet must be done
in a way that it is easy to use (such as cliciking checkboxes) and does not
require the macros to be enabled (thus no userform works).

After these worksheets are collected, I want to be able run a simple macro which will:

1. check the answers vs. a database
(The database would be something like this with checkboxes
1. YNNYYN
2. NNYYNY...)

2. create an output where it shows the student's answers and below if they are correct or not.

However, right now I'm stuck on #1. I couldn't find a way to cycle through

the checkboxes, find out if they are checked or not. If I refer to them as
members of the Shapes collection I can cycle, but cannot check the state. If
I refer to them as objects on the worksheet (worksheets(1).checkbox1...)
then I cannot cycle it. Does anybody have any suggestions? Thanx.

 

[Frank Kabel]

Hi
as a starting point to cycle through your checkboxes:
Sub foo()
Dim checks As OLEObject
For Each checks In ActiveSheet.OLEObjects
MsgBox checks.Name & " " & checks.Object.Value
Next
End Sub

 

[Bob Phillips]

Hi ZSolt,

Here's some generic code that should show you how to get what you want

Sub Checkboxes()
Dim i As Long

For i = 1 To ActiveSheet.OLEObjects.Count
If TypeName(ActiveSheet.OLEObjects(i).Object) = "CheckBox" Then
If ActiveSheet.OLEObjects(i).Object.Value = True Then
MsgBox ActiveSheet.OLEObjects(i).Object.Caption & " is set"
End If
End If
Next i

End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Hello,
I'm running into some trouble when trying to use checkboxes.
Here's my problem which I try to solve:
Students hand in their reports from the lab, teaching assistants enter the

results (yes/no) into an excel worksheet. Now, this worksheet must be done
in a way that it is easy to use (such as cliciking checkboxes) and does not
require the macros to be enabled (thus no userform works).

After these worksheets are collected, I want to be able run a simple macro which will:

1. check the answers vs. a database
(The database would be something like this with checkboxes
1. YNNYYN
2. NNYYNY...)

2. create an output where it shows the student's answers and below if they are correct or not.

However, right now I'm stuck on #1. I couldn't find a way to cycle through

the checkboxes, find out if they are checked or not. If I refer to them as
members of the Shapes collection I can cycle, but cannot check the state. If
I refer to them as objects on the worksheet (worksheets(1).checkbox1...)
then I cannot cycle it. Does anybody have any suggestions? Thanx.

 

[Guest]

Guys
thanks for the fast answer. I'll try your ideas and see which one works as I need it. Or the fastest on large data sets

Zsolt

 

[Bob Phillips]

The size of the dataset should not be significant, but the number of
controls.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Guys,
thanks for the fast answer. I'll try your ideas and see which one works

as I need it. Or the fastest on large data sets.