# Thread: Multiple check boxes (Excel 2002)

1. ## Multiple check boxes (Excel 2002)

I need to place 4 check boxes in each of 9 columns on a worksheet. Each check box represents a fixed dollar amount. Depending on an entry in another cell, the user may select none, some or all of them in each column. I need a relatively easy way to determine which boxes have been checked so that I can determine the dollar amounts. I spent the day in If...Elseif...Then land, and need a better (and hopefully easier) way to do this. Thanks in advance.

2. ## Re: Multiple check boxes (Excel 2002)

If you use check boxes from the Control Toolbox, you can give them meaningful names in the Properties window, and you can refer to them directly in code like this:

Sheet1.CheckBox1

The value of such a check box can be False = 0 or True = -1. You can use this, for example in a calculation like this:

TotalAmount = -(Sheet1.CheckBox1 * 100 + Sheet1.CheckBox2 * 200 + Sheet1.CheckBox3 * 400 + Sheet1.CheckBox4 * 800)

(The minus sign is because True corresponds to -1)

3. ## Re: Multiple check boxes (Excel 2002)

If you give the checkboxes useful names, such as Checkbox93 for the one in row 9 column 3 of your set of boxes, you can automate the references:
<pre>Sub tryit()
Dim sh As New Worksheet
Dim iRow As Integer, iCol As Integer
Dim ChkBoxValue As Boolean
Dim totalDollars As Double

Set sh = Worksheets("Sheet1")
totalDollars = 0
'Run through the checkboxes by rows and columns
For iRow = 1 To 9
For iCol = 1 To 4
' Get the value of the checkbox in row iRow, column iCol
ChkBoxValue = sh.OLEObjects("Checkbox" & iRow & iCol).Object.Value
' Add up the values where the boxes are checked
If ChkBoxValue Then
totalDollars = totalDollars + sh.Range("a1").Offset(iRow - 1, iCol - 1)
End If
Next iCol
Next iRow
MsgBox totalDollars
End Sub
</pre>

This makes it easier to be sure you're getting the right entries

Ian.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•