Do Calculations using Check Box checked values

Question

I have a code that copies the information from a sheet to another if in Column E the text says "Yes". Then it adds a check box in every row until the last row with data in the following Column.

I need help with the following: I want to do some calculations in another sheet. If the Check box is checked, I want to have the count of the rows that have the Check box checked. After I have this count I want to calculate the average of (Count divided by the Total number of rows) this total number of rows includes the ones checked and not checked.

Here is the code that copies the information and adds the check boxes:

Sub Copysheet_And_CheckBox2() '<-- you run this
 CopySheet '<-- you call this first
 Dim i As Integer
Dim LastRow As Integer
Dim Search As String
Dim Column As Integer

  Sheets("Audit Items").Activate
   Sheets("Audit Items").Range("A1").Select
  'Sets an Autofilter to sort out only your Yes rows.
   Selection.AutoFilter
   'Change Field:=5 to the number of the column with your Y/N.
    Sheets("Audit Items").Range("$A$1:$G$2000").AutoFilter Field:=4,   Criteria1:="Yes"

'Finds the last row
LastRow = Sheets("Audit Items").Cells(Sheets("Audit Items").Rows.Count, "A").End(xlUp).Row

i = 1
'Change the 3 to the number of columns you got in Sheet2
 Do While i <= 3
  Search = Sheets("Form").Cells(1, i).Value
  Sheets("Audit Items").Activate
  'Update the Range to cover all your Columns in Sheet1.
  If IsError(Application.Match(Search, Sheets("Audit Items").Range("A1:G1"), 0))  Then
      'nothing
   Else
    Column = Application.Match(Search, Sheets("Audit Items").Range("A1:G1"), 0)
    Sheets("Audit Items").Cells(2, Column).Resize(LastRow, 1).Select
    Selection.Copy
    Sheets("Form").Activate
    Sheets("Form").Cells(2, i).Select
    ActiveSheet.Paste
    End If
    i = i + 1
    Loop

   CheckBox '<-- and this second into the same call
   Dim ToRow As Long
   Dim LastRow2 As Long
   Dim MyLeft As Double
   Dim MyTop As Double
   Dim MyHeight As Double
   Dim MyWidth As Double

    LastRow2 = Range("A65536").End(xlUp).Row
    For ToRow = 2 To LastRow2
    If Not IsEmpty(Cells(ToRow, "A")) Then
    Sheets("Form").Activate
        MyLeft = Cells(ToRow, "C").Left
        MyTop = Cells(ToRow, "C").Top
        MyHeight = Cells(ToRow, "C").Height
        MyWidth = MyHeight = Cells(ToRow, "C").Width
        '-
        ActiveSheet.CheckBoxes.Add(MyLeft, MyTop, MyWidth, MyHeight).Select
         With Selection
            .Caption = "Yes"
            .Value = xlOff
            .LinkedCell = "E" & ToRow
            .Display3DShading = False
            If CheckBoxes.Value = True Then
            CheckBoxes.Value = "1"

        End With
        End If
       Next

     End Sub

score 1 · Answer 1 · edited May 23 '17 at 12:14

Right now you're adding the control (i.e. defining their properties such as x & y co-ordinates, that they should be visible on screen etc. and adding this into the controls collection), however you're not creating any event handlers (i.e. "when the control is clicked, do this action", "if the control is disabled, do this other action" etc.)

There's a sample of how to add an event handler here on StackOverflow at Assign event handlers to controls on user form created dynamically in VBA

Do Calculations using Check Box checked values

1 Answers1