Count check boxes set to true

[Barry]

I have a form which makes check boxes visible depending on a value selected
by the user as a start and finish value. the check boxes are split into
colums from A-C and in rows from 0-30. This works fine but what i now need to
do is count all the check boxes ticked in column A and a count of all the
check boxes ticked in column B etc..

can anyone please please help me with this??

Here is the code i have used to make the check boxes visible

Dim a As Integer
Dim b As Integer
Dim c As Integer
Dim CheckA As String
Dim CheckB As String
Dim CheckC As String

StartLevel = Me.txtStartLevel.Value
FinishLevel = StartLevel + Me.txtNoOfLevels.Value - 1

For a = StartLevel To FinishLevel
CheckA = "chkLevela" & Format$(a)
Me(CheckA).Enabled = False
Me(CheckA).Visible = False
Next a

For b = StartLevel To FinishLevel
CheckB = "chkLevelb" & Format$(b)
Me(CheckB).Enabled = False
Me(CheckB).Visible = False
Next b

For c = StartLevel To FinishLevel
CheckC = "chkLevelc" & Format$(c)
Me(CheckC).Enabled = False
Me(CheckC).Visible = False
Next c

 

[Douglas J. Steele]

You need essentially the same code to check them afterwords:

Dim a As Integer
Dim b As Integer
Dim c As Integer
Dim TotalA As Integer
Dim TotalB As Integer
Dim TotalC As Integer
Dim CheckA As String
Dim CheckB As String
Dim CheckC As String

StartLevel = Me.txtStartLevel.Value
FinishLevel = StartLevel + Me.txtNoOfLevels.Value - 1

For a = StartLevel To FinishLevel
CheckA = "chkLevela" & Format$(a)
If Me(CheckA) Then
TotalA = TotalA + 1
End If
Next a

For b = StartLevel To FinishLevel
CheckB = "chkLevelb" & Format$(b)

If Me(CheckB) Then
TotalB = TotalB + 1
End If
Next b

For c = StartLevel To FinishLevel
CheckC = "chkLevelc" & Format$(c)

If Me(CheckC) Then
TotalC = TotalC + 1
End If
Next c

Note that you are making it a little more difficult that it need be. The
following could replace what you already have:

Dim ctlCurr As Control
Dim intLoop As Integer
Dim StartLevel As Integer
Dim FinishLevel As Integer

StartLevel = Me.txtStartLevel.Value
FinishLevel = StartLevel + Me.txtNoOfLevels.Value - 1

For intLoop = StartLevel To FinishLevel
Set ctlCurr = Me.Controls("chkLevela" & Format$(intLoop))
ctlCurr.Enabled = False
ctlCurr.Visible = False
Set ctlCurr = Me.Controls("chkLevelb" & Format$(intLoop))
ctlCurr.Enabled = False
ctlCurr.Visible = False
Set ctlCurr = Me.Controls("chkLevelc" & Format$(intLoop))
ctlCurr.Enabled = False
ctlCurr.Visible = False
Next intLoop

and what I suggested could be

Dim intLoop As Integer
Dim intTotalA As Integer
Dim intTotalB As Integer
Dim intTotalC As Integer
Dim StartLevel As Integer
Dim FinishLevel As Integer

StartLevel = Me.txtStartLevel.Value
FinishLevel = StartLevel + Me.txtNoOfLevels.Value - 1

For intLoop = StartLevel To FinishLevel
intTotalA = intTotalA + IIf(Me.Controls("chkLevela" & Format$(intLoop)),
1, 0)
intTotalB = intTotalB + IIf(Me.Controls("chkLevelb" & Format$(intLoop)),
1, 0)
intTotalC = intTotalC + IIf(Me.Controls("chkLevelc" & Format$(intLoop)),
1, 0)
Next intLoop

 

[Barry]

Hi Douglas, this works perfect. Thanks for taking the time to answer my
question and my appologies for not cross-posting this question

 

[Barry]

the first procedure seems to work for me but when i try the second it is
giving ctlCurr a value of Null

 

[Douglas J Steele]

What line, specifically, is giving you a value of Null?

 

[Barry]

When i use the following code i get a null value for very occurance of Set
ctlCurr. This does not happen when i use the line CheckA = "chkLevela" &
Format$(a), is there a reson for this

For intLoop = StartLevel To FinishLevel
Set ctlCurr = Me.Controls("chkLevela" & Format$(intLoop))
ctlCurr.Enabled = False
ctlCurr.Visible = False
Set ctlCurr = Me.Controls("chkLevelb" & Format$(intLoop))
ctlCurr.Enabled = False
ctlCurr.Visible = False
Set ctlCurr = Me.Controls("chkLevelc" & Format$(intLoop))
ctlCurr.Enabled = False
ctlCurr.Visible = False
Next intLoop

 

[Douglas J Steele]

No reason that I can think of.

Might there be a typo somewhere?

Do you have Option Explicit at the top of the module? If not, put it there,
then Compile your module. That should help identify any problems with
variable names being mistyped somewhere.