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

"Leigh Douglass" wrote:

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

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

--

Regards,
Nigel




"Leigh Douglass" wrote in message
...
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

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

"Nigel" wrote:

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

--

Regards,
Nigel




"Leigh Douglass" wrote in message
...
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

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
--
If this post helps click Yes
---------------
Jacob Skaria


"Leigh Douglass" wrote:

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

Allocate the value to the range rather than display is a MsgBox

For example

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


--

Regards,
Nigel




"Leigh Douglass" wrote in message
...
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

"Nigel" wrote:

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

--

Regards,
Nigel




"Leigh Douglass" wrote in
message
...
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