Clever Geek Handbook

Calculations using check flags

I have some code that copies information from a sheet to another if the text in column E says "Yes". Then it adds a checkbox on each row up to the last row with data in the next column.

I need help with the following: I want to do some calculations on another sheet. If the checkbox is checked, I want the number of rows that have the checkbox checked. Once I have this count, I want to calculate the average (Count divided by the total number of rows), this total number of rows includes those that have been checked and unchecked.

Here's the code that copies the information and adds the checkboxes:

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


source to share


1 answer


At the moment, you are adding a control (that is, defining its properties such as x and y coordinates so that they are visible on the screen, etc. and adding them to the collection of controls), however, you are not creating any event handlers (i.e. .e. "when the control is clicked, take this action", "if the control is disabled, take another action", etc.)



Here is an example of how to add an event handler to StackOverflow at Assign event handlers to controls in a custom form created dynamically in VBA .

+1


source






More articles:


All Articles