Hello,

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

Excel-erate2004,

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