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
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