Multiple check boxes (Excel 2002)

Topic

New Reply

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.

Reply | Quote

Replies

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)

Reply | Quote

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:

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

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

Ian.

Reply | Quote