用于说明用户表单中所有复选框的代码

Question

我有一个用户表单上的代码，该表单包含几个复选框和几个DTPicker。

代码看起来是这样的：

Private Sub CheckBox11_Click()
If CheckBox11.Value = True Then
DTPicker22.Enabled = True
Else
DTPicker22.Enabled = False
End If
End Sub
Private Sub CheckBox12_Click()
If CheckBox12.Value = True Then
DTPicker24.Enabled = True
Else
DTPicker24.Enabled = False
End If
End Sub 

Userform包含许多复选框，这些复选框旁边有子句。完成后，DTPicker将允许输入完成日期。

虽然这是我想要的，但当复选框被选中时，它只启用一个DTPicker。必须有一些方法来实现这一点，这样我就不需要为每个复选框单击事件创建不同的专用子。

你能告诉我把它放在哪里吗，比如在什么活动中？

Answer 1

A"控制阵列"；是类似情况的典型方法。

请参阅：http://www.siddharthrout.com/index.php/2018/01/15/vba-control-arrays/

例如：

类模块clsEvents

Option Explicit
'Handle events for a checkbox and a date control, associated with a worksheet cell
Private WithEvents m_CB As MSForms.CheckBox
Private WithEvents m_DP As DTPicker
Private m_dateCell As Range
'set up the controls and the cell
Public Sub Init(cb As MSForms.CheckBox, dp As DTPicker, rng As Range)

Set m_CB = cb
Set m_DP = dp
Set m_dateCell = rng

If rng.Value > 0 Then
cb.Value = True
m_DP.Value = rng.Value
Else
cb.Value = False
End If

m_DP.CustomFormat = "dd/MM/yyyy"

End Sub
Private Sub m_CB_Change()
m_DP.Enabled = (m_CB.Value = True)
End Sub
Private Sub m_DP_Change()
m_dateCell.Value = m_DP.Value 'update the cell
End Sub

用户形式：

Option Explicit
Dim colObj As Collection 'needs to be a Global to stay in scope
Private Sub UserForm_Activate()
Dim obj As clsEvents, i As Long, ws As Worksheet

Set ws = ThisWorkbook.Worksheets("Sheet1")

Set colObj = New Collection
'loop over controls and create a class object for each set
' 3 pairs of controls on my test form...
For i = 1 To 3
Set obj = New clsEvents
obj.Init Me.Controls("CheckBox" & i), _
Me.Controls("DTPicker" & i), _
ws.Cells(i, "B")
colObj.Add obj
Next i
End Sub

Answer 2

我建议的第一件事是遵循正确的命名约定"复选框11"；以及"；DTPciker1"；非常模糊，一旦深入代码，就会忘记哪个控件是哪个控件。我建议给它们命名一些将两个控件联系在一起的东西，比如"；firstDate"；以及"；firstDateDTP"；。我下面的备选答案使用了这种方法。

您可以创建一个公共函数，根据复选框的值启用DTPicker。

Public Function EnableDTPicker(myPicker as String, enableBool as Boolean)
UserFormName.Controls(myPicker).Enabled = enableBool
End Function

然后，您可以调用CheckBox123_Click((子系统中的函数，如下所示：

Private Sub CheckBox123_Click()
EnableDTPicker("thePickerName", CheckBox123.Value)
End Sub

或者，您可以创建一个运行x秒的计时器事件，该事件只在控件中循环并根据需要执行检查。请参阅本页，了解如何设置计时器。使用所示链接中的代码，您可以按照以下行做一些事情：

'Put this in Workbook events
Private Sub Workbook_Open()
alertTime = Now + TimeValue("00:00:01")
Application.OnTime alertTime, "EventMacro"
UserForm1.Show
End Sub
'Put this in a Module
Public Sub EventMacro()
With UserForm1
For each ctrl in .Controls
If TypeName(ctrl) = "CheckBox" Then
'The code below assumes the naming convention outlined above is followed
.Controls(ctrl.Name & "DTP").Enabled = ctrl.Value
End If
Next ctrl
End With
alertTime = Now + TimeValue("00:00:01")
Application.OnTime alertTime, "EventMacro"
End Sub