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.
如果在E列中文本显示“是”,我有一个代码可以将信息从工作表复制到另一个工作表。然后,它会在每一行中添加一个复选框,直到最后一行包含以下列中的数据。
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
1 个解决方案
#1
1
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.)
现在你正在添加控件(即定义它们的属性,例如x和y坐标,它们应该在屏幕上可见等等,并将其添加到控件集合中),但是你没有创建任何事件处理程序(即“当点击控件时,执行此操作”,“如果控件被禁用,请执行此其他操作”等。)
There's a sample of how to add an event handler here on * at Assign event handlers to controls on user form created dynamically in VBA
这里有一个如何在*上将事件处理程序添加到Assign事件处理程序到VBA中动态创建的用户表单上的控件的示例
#1
1
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.)
现在你正在添加控件(即定义它们的属性,例如x和y坐标,它们应该在屏幕上可见等等,并将其添加到控件集合中),但是你没有创建任何事件处理程序(即“当点击控件时,执行此操作”,“如果控件被禁用,请执行此其他操作”等。)
There's a sample of how to add an event handler here on * at Assign event handlers to controls on user form created dynamically in VBA
这里有一个如何在*上将事件处理程序添加到Assign事件处理程序到VBA中动态创建的用户表单上的控件的示例