Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Apr 2004
    Location
    Jacksonville, Florida, USA
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #3
    Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
  •