VB Code for summing control check boxes

  • Thread starter Thread starter Leigh Douglass
  • Start date Start date
L

Leigh Douglass

HI

I have a sheet with 4 tick box controls on it, i would like a peice of vb
code that would add them up if they have a check in them. I have no idea
where to start.

Thanks in advance for the help.

Leigh
 
Hi,

Right click yoor sheet tab, view code and paste this in and run it.


Sub Checked()
Dim Obj As OLEObject
For Each Obj In ActiveSheet.OLEObjects
If TypeOf Obj.Object Is MSForms.CheckBox Then
If Obj.Object.Value = True Then Count = Count + 1
End If
Next
Set Obj = Nothing
MsgBox Count
End Sub

Mike
 
Preumably when you say 'add them up' you mean count how many are checked or
encode them in someway.

I also assume they are ActiveX controls rather than Form controls?

An example option below, for each control the event click is set up, which
calls a common process to evaluate their status, place code behind the
relevant worksheet where the controls are located.

Private Sub CheckBox1_Click()
GetResult
End Sub

Private Sub CheckBox2_Click()
GetResult
End Sub

Private Sub CheckBox3_Click()
GetResult
End Sub

Private Sub CheckBox4_Click()
GetResult
End Sub

Private Sub GetResult()
' counter
MsgBox -1 * (CheckBox1 + CheckBox2 + CheckBox3 + CheckBox4)
' binary encoded
MsgBox -1 * (CheckBox1 + CheckBox2 * 2 + CheckBox3 * 4 + CheckBox4 * 8)
End Sub
 
Nigel

Thanks very much for that peice of code, it works a treat. Any chance you
could tell me how i get the result to display in a given cell on the same
sheet as the check boxes?

Regards
Leigh
 
I assume the checkbox names are checkbox1, checkbox2 and so on..Launch VBE
using Alt+F11. Insert a module and paste the below code..

In worksheet the below formula will return the value
=GetCheckBoxValue()

Function GetCheckBoxValue() As Long
For intTemp = 1 To 4
If ActiveSheet.OLEObjects("CheckBox" & intTemp).Object.Value = True Then
GetCheckBoxValue = GetCheckBoxValue + 1
End If
Next
End Function
 
Allocate the value to the range rather than display is a MsgBox

For example

Range("A10") = -1 * (CheckBox1 + CheckBox2 + CheckBox3 + CheckBox4)


--

Regards,
Nigel
(e-mail address removed)
 
Back
Top