VBA Cycle through Checkboxes

[linuxgeek]

Hi

I want to check if a group of ActiveX-style checkboxes "named" Checkbox1 - Checkbox24 consecutively are checked and then add or minus 1 in a cell

I'd really like to do this on worksheet_change so each time a checkbox is checked/unchecked it will update

However the code I have is currently linked to a button

Sub AddBoxes()

Dim k As Integer
For k = 1 To 24


If ActiveSheet.OLEObjects("Checkbox" & k).Object.Value = 0 Then
Range("A1").Value = Range("A1").Value - 1
End If


If ActiveSheet.OLEObjects("Checkbox" & k).Object.Value = 1 Then
Range("A1").Value = Range("A1").Value + 1
End If


Next

End Sub

It doesn't work however,
"Runtime error 1004 Unable to get the OLEobjects property of the Worksheet Class"


I could of course repeat the above code for each "click" event, which would work but is very messy if I want to, for example change the cell I am adding or deleting a value from?

 

[linuxgeek]

Say there was just three checkboxes for example and A1 = 0
You check only the 1st checkbox and run your code to loop through the three checkboxes where True = +1 and False = -1
There is one True and two False checkboxes. Result: A1= -1

If you Uncheck the 1st checkbox and run the code again. Three False checkboxes = -3 added to the -1 in A1 = -4

Okay, I see what you're saying now, my logic is wrong!

I was thinking more of the fact that if there is a *change* in the checkbox status, it will add 1 or minus 1 so if there is no change, it won't do anything.

 

[linuxgeek]

I seem to have it all working now, thanks for the help there!!

I can't find a better way to present the questions than in a userform though, so I'm in the process of putting each part into separate forms.
It needs to be visually very similar to the actual spreadsheet and work in exactly the same way if possible, with a few additions..

If anybody fancies helping me out with most of the work that would be amazeballs (as they say!)

One important thing is that it needs to have a nice printable output at the end.

I think it's only possible to print one form, so it should I suppose have a separate "form" for the summary output.


Form 1 - Competency Question 1 (needs to tick 4 boxes to pass and "pass" should be indicated on the form, but you can continue to next form, 16 options)
Form 2 - Competency Question 2 (needs to tick 4 boxes to pass and "pass" should be indicated on the form, but you can continue to next form, 16 options)
Form 3 - Competency Question 3 (needs to tick 4 boxes to pass and "pass" should be indicated on the form, but you can continue to next form, 16 options)
Form 4 - Questions 4, 5 or 6 (one box each, not a pass/fail, just a total; answer to question is typed)
Form 5 - Listening (3 tickboxes only, no pass/fail rule) , Experience1, Experience2 (one box each, no pass/fail rule; answer to each question is typed)
Form 6 - Opinion1, Opinion2, Opinion3 (can be 1, 2 or 3, no pass/fail rule but must be filled out)
Display Total of above with pass/fail outcome. Total number of checkboxes must be > 23 for a pass
and you must have 4 in each "competency question" -- this is the last chance to make any changes, so
would change dynamically to a total pass/fail as you tick or untick

Form 7 - Print summary - display all answers exactly as they are in the forms, e.g. should display the answers not ticked also

Also
-Each form 1-6 should give you the option to enter/change the opinion values in (form 6) and they do need to be entered to continue

File is here: Test