Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2004
    St. John's, NFLD

    Question Unanswered: Excel-VBA sub routine to calculate AVG


    I had developed a sub routine that sums a range of cells M12:M40 that depended on whether or not a checkbox value was true for that particular cell.

    I.E. There are 29 cells between M12 and M40 and there is a Checkbox associated with each cell labeled CheckBox1 to CheckBox29.

    What I would like to be able to do is take that sum and then get an average based on those checkboxes that are checked.

    Here is my code:

    Private Sub SumCheckBoxes_Click()

    Dim obj As OLEObject
    Dim dblValue As Double

    For Each obj In ActiveSheet.OLEObjects
    If obj.ProgId = "Forms.CheckBox.1" And obj.Object.Value Then
    dblValue = dblValue + Range _
    ("M" & VBA.Replace(obj.Name, "CheckBox", "") + 11).Value
    End If
    Next obj

    'MsgBox dblValue
    Sheets("Step 5").Range("M43") = dblValue

    End Sub

    What I need is some way to count the number of cells that have their checkbox value set to true then divide that number by the sum from the code above.

    How could I go about this? Any ideas?

    Thanks for any help I can get

  2. #2
    Join Date
    Mar 2004
    Fort Worth, Texas, USA

    You're killing me with your insistence on coding VBA, instead of relying on the awesome power of Excel itself. Your checkboxes have the ability to link to a cell--use this to create a column range of 29 TRUE/FALSE values (perhaps N12:N40). You'll have to type in each cell reference in the properties of each checkbox.

    After this is done, type =SUM(M12:M40*N12:N40)/SUM(N12:N40) into cell M43. Then press CTRL+SHFT+ENTER

    See example attached
    Attached Files Attached Files

Posting Permissions

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