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?

 

[JLGWhiz]

Here is one from HalfAce that maybe you can adapt:

Public Sub TickAllCheckBoxes()
Dim ChkBx As OLEObject
    For Each ChkBx In ActiveSheet.OLEObjects
        If TypeName(ChkBx.Object) = "CheckBox" Then
            ChkBx.Object = True
        End If
    Next ChkBx
End Sub

 

[linuxgeek]

Here is one from HalfAce that maybe you can adapt:

Public Sub TickAllCheckBoxes()
Dim ChkBx As OLEObject
    For Each ChkBx In ActiveSheet.OLEObjects
        If TypeName(ChkBx.Object) = "CheckBox" Then
            ChkBx.Object = True
        End If
    Next ChkBx
End Sub

Sure I've seen that but it won't actually work how I want i.e. if I change it to only look for a certain "Name" property, it will only add/minus it once for the entire IF statement, so it's no better than adding a separate "click" sub to each checkbox...

i.e. something like this

Public Sub TickAllCheckBoxes()Dim ChkBx As OLEObject
For Each ChkBx In ActiveSheet.OLEObjects
  If TypeName(ChkBx.Object) = "CheckBox" _
    And ChkBx.Object = True _
    And _
    ChkBx.Name = "Checkbox1" Or ChkBx.Name = "Checkbox2" Or ChkBx.Name = "Checkbox3" _
    Then
    Range("A1").Value = Range("A1").Value + 1
  End If
Next ChkBx
End Sub

 

[AlphaFrog]

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

You could link each checkbox to a cell. Perhaps the cell under each checkbox. Maybe change the text color to white to hide the result.

Then use a formula to count the TRUE values in the linked cells e.g.;
=COUNTIF(B1:B24,TRUE)

 

[linuxgeek]

@AlphaFrog

I've actually done it that way already but wondering if there was a VBA method of doing things!

Bear in mind that doesn't with with ActiveX-style checkboxes...only "form" ones

Maybe sometimes it's just easier to do it with formulas!

 

[AlphaFrog]

It does work with Active-X checkboxes as they have a LinkedCell property.

Your VBA method references the Checkbox correctly but ...

• Their values are TRUE\FALSE which is not the same as 1\0
• The code doesn't count the TRUE values. It counts the ratio of TRUE to FALSE e.g. if half = TRUE then the count would be 0. Is that what you want?
• Checkboxes don't trigger the worksheet change event.

This will count the TRUE checkboxes, but I'm not sure how you want to trigger it.

    [color=darkblue]Dim[/color] k [color=darkblue]As[/color] [color=darkblue]Integer[/color], count [color=darkblue]As[/color] [color=darkblue]Integer[/color]
    [color=darkblue]For[/color] k = 1 [color=darkblue]To[/color] 24
        [color=darkblue]If[/color] ActiveSheet.OLEObjects("Checkbox" & k).Object.Value [color=darkblue]Then[/color]
            count = count + 1
        [color=darkblue]End[/color] [color=darkblue]If[/color]
    [color=darkblue]Next[/color] k
    Range("A1").Value = count

 

[linuxgeek]

@AlphaFrog

Not quite...

Put simply...


Look at each checkbox named Checkbox1..Checkbox24
If it is ticked, I want to add 1 to cell A1
If it is not ticked, I want to minus 1 to cell A1

So A1 can never be more than in this case 24 and never less than 0

 

[AlphaFrog]

@AlphaFrog

Not quite...

Put simply...


Look at each checkbox named Checkbox1..Checkbox24
If it is ticked, I want to add 1 to cell A1
If it is not ticked, I want to minus 1 to cell A1

So A1 can never be more than in this case 24 and never less than 0

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

 

[Marcelo Branco]

@AlphaFrog

Not quite...

Put simply...


Look at each checkbox named Checkbox1..Checkbox24
If it is ticked, I want to add 1 to cell A1
If it is not ticked, I want to minus 1 to cell A1

So A1 can never be more than in this case 24 and never less than 0

See if this is ok for you

Public Sub CountCkBoxON()
    Dim i As Long
    
    With ActiveSheet
        .Range("A1") = 0
        For i = 1 To 24
            If .OLEObjects("Checkbox" & i).Object.Value Then .Range("A1") = .Range("A1") + 1
        Next i
    End With
    
End Sub

M.

 

[Marcelo Branco]

oops...

You can simply use the formula suggested by AlphaFrog in #4. No VBA needed

Or the code also suggested by AlphaFrog in #6

M.