Excel宏用户窗体 - 单个代码处理多个复选框 [英] Excel Macro Userform - single code handling multiple checkboxes

问题描述

我有一个用户表单，看起来像下面的图像，具有命名约定，以标识水平&位置（如上图中的示例）。我还有下面的当前代码，但是我还没有为每个端口复选框添加代码，最后我将根据它们是否将每个复选框特定的值插入到数组中检查/取消检查。

I've got a user form that looks like the image below, with a naming convention to identify levels & positions (like the example above the pic). I have the current code below, as well - but I haven't added code for each of the "Port" checkboxes yet, and eventually I'll be inserting/removing a value specific to each checkbox into arrays based on whether they're checked/un-checked.

我想知道以下内容：

• 是否通过阅读是否检查/取消选中复选框（如下所示），或在更改中勾选或取消勾选复选框的条件？

• Are conditions based on checking or un-checking a checkbox better chosen by reading whether it's checked/un-checked (like I'm doing below), or upon "a change" in ticking?

我可以缩短将表单上任何复选框的操作编号为仅一（或几）个案例的次数或次数，而不是对其中的每一个进行编码（我最终会根据检查（或未检查）分配一个数组（或删除一个））？

Can I shorten the number or times I code an action for any checkbox on the form to just one (or a few) case(s) instead of coding every one of those (I'll eventually be assigning a value to an array (or removing one) based on what's checked (or un-checked))?

我可以采取一个复选框的名称的一部分（如前4或6个字符）作为一个值，以确定如果＃2可以处理什么？

Can I take a portion of the name of a checkbox (like the first 4 or 6 characters) as a value to determine what I'm handling if #2 is possible?

我有b een盯着这么久我有点脑死亡 - 我不知道我在这里问正确的问题，还有什么要问的。基本上，我想避免重复一遍又一遍的事情（像我现在在做的）。

I've been staring at this so long I'm a little brain-dead - I'm not sure if I'm asking the right questions here, or what else to ask. Essentially, I want to avoid having to repeat what is pretty much the same thing over and over (like I'm doing now).

Private Sub UserForm_Initialize()
Dim chBox As Control
Dim comboBox As Control
Dim arrFreq() As String
Dim i As Long


    'Use the Split function to create two zero based one dimensional arrays.
    arrFreq = Split("Unused|700|850|1900|2100", "|")
    For Each comboBox In ADSinputform.Controls
        If TypeOf comboBox Is MSForms.comboBox Then
            For i = 0 To UBound(arrFreq)
                'Use .List property to write array data to all the comboBoxes
                comboBox.List = arrFreq
            Next i
        End If
    Next
    'Empty NameTextBox
    SiteNameTextBox.Value = siteName


End Sub

Private Sub AlphaSectCheckbox_Click()

    If AlphaSectCheckbox.Value = True Then AlphaAnt_Frame.Visible = True
    If AlphaSectCheckbox.Value = False Then
        AlphaAnt_Frame.Visible = False
        For Each chBox In AlphaAnt_Frame.Controls
            If TypeOf chBox Is MSForms.CheckBox Then chBox.Value = False
        Next
    End If

End Sub

Private Sub BetaSectCheckbox_Click()

    If BetaSectCheckbox.Value = True Then BetaAnt_Frame.Visible = True
    If BetaSectCheckbox.Value = False Then
        BetaAnt_Frame.Visible = False
        For Each chBox In BetaAnt_Frame.Controls
            If TypeOf chBox Is MSForms.CheckBox Then chBox.Value = False
        Next
    End If

End Sub

Private Sub GammaSectCheckbox_Click()

    If GammaSectCheckbox.Value = True Then GammaAnt_Frame.Visible = True
    If GammaSectCheckbox.Value = False Then
        GammaAnt_Frame.Visible = False
        For Each chBox In GammaAnt_Frame.Controls
            If TypeOf chBox Is MSForms.CheckBox Then chBox.Value = False
        Next
    End If

End Sub

Private Sub DeltaSectCheckbox_Click()

    If DeltaSectCheckbox.Value = True Then DeltaAnt_Frame.Visible = True
    If DeltaSectCheckbox.Value = False Then
        DeltaAnt_Frame.Visible = False
        For Each chBox In DeltaAnt_Frame.Controls
            If TypeOf chBox Is MSForms.CheckBox Then chBox.Value = False
        Next
    End If

End Sub

Private Sub A1Checkbox_Click()

    If A1Checkbox.Value = True Then A1Port_Frame.Visible = True
    If A1Checkbox.Value = False Then
        A1Port_Frame.Visible = False
        For Each chBox In A1Port_Frame.Controls
            If TypeOf chBox Is MSForms.CheckBox Then chBox.Value = False
        Next
    End If

End Sub

Private Sub A2Checkbox_Click()

    If A2Checkbox.Value = True Then A2Port_Frame.Visible = True
    If A2Checkbox.Value = False Then
        A2Port_Frame.Visible = False
        For Each chBox In A2Port_Frame.Controls
            If TypeOf chBox Is MSForms.CheckBox Then chBox.Value = False
        Next
    End If

End Sub

Private Sub A3Checkbox_Click()

    If A3Checkbox.Value = True Then A3Port_Frame.Visible = True
    If A3Checkbox.Value = False Then
        A3Port_Frame.Visible = False
        For Each chBox In A3Port_Frame.Controls
            If TypeOf chBox Is MSForms.CheckBox Then chBox.Value = False
        Next
    End If

End Sub

Private Sub A4Checkbox_Click()

    If A4Checkbox.Value = True Then A4Port_Frame.Visible = True
    If A4Checkbox.Value = False Then
        A4Port_Frame.Visible = False
        For Each chBox In A4Port_Frame.Controls
            If TypeOf chBox Is MSForms.CheckBox Then chBox.Value = False
        Next
    End If

End Sub

Private Sub A5Checkbox_Click()

    If A5Checkbox.Value = True Then A5Port_Frame.Visible = True
    If A5Checkbox.Value = False Then
        A5Port_Frame.Visible = False
        For Each chBox In A5Port_Frame.Controls
            If TypeOf chBox Is MSForms.CheckBox Then chBox.Value = False
        Next
    End If

End Sub

Private Sub A6Checkbox_Click()

    If A6Checkbox.Value = True Then A6Port_Frame.Visible = True
    If A6Checkbox.Value = False Then
        A6Port_Frame.Visible = False
        For Each chBox In A6Port_Frame.Controls
            If TypeOf chBox Is MSForms.CheckBox Then chBox.Value = False
        Next
    End If

End Sub

Private Sub B1Checkbox_Click()

    If B1Checkbox.Value = True Then B1Port_Frame.Visible = True
    If B1Checkbox.Value = False Then
        B1Port_Frame.Visible = False
        For Each chBox In B1Port_Frame.Controls
            If TypeOf chBox Is MSForms.CheckBox Then chBox.Value = False
        Next
    End If

End Sub

Private Sub B2Checkbox_Click()

    If B2Checkbox.Value = True Then B2Port_Frame.Visible = True
    If B2Checkbox.Value = False Then
        B2Port_Frame.Visible = False
        For Each chBox In B2Port_Frame.Controls
            If TypeOf chBox Is MSForms.CheckBox Then chBox.Value = False
        Next
    End If

End Sub

Private Sub B3Checkbox_Click()

    If B3Checkbox.Value = True Then B3Port_Frame.Visible = True
    If B3Checkbox.Value = False Then
        B3Port_Frame.Visible = False
        For Each chBox In B3Port_Frame.Controls
            If TypeOf chBox Is MSForms.CheckBox Then chBox.Value = False
        Next
    End If

End Sub

Private Sub B4Checkbox_Click()

    If B4Checkbox.Value = True Then B4Port_Frame.Visible = True
    If B4Checkbox.Value = False Then
        B4Port_Frame.Visible = False
        For Each chBox In B4Port_Frame.Controls
            If TypeOf chBox Is MSForms.CheckBox Then chBox.Value = False
        Next
    End If

End Sub

Private Sub B5Checkbox_Click()

    If B5Checkbox.Value = True Then B5Port_Frame.Visible = True
    If B5Checkbox.Value = False Then
        B5Port_Frame.Visible = False
        For Each chBox In B5Port_Frame.Controls
            If TypeOf chBox Is MSForms.CheckBox Then chBox.Value = False
        Next
    End If

End Sub

Private Sub B6Checkbox_Click()

    If B6Checkbox.Value = True Then B6Port_Frame.Visible = True
    If B6Checkbox.Value = False Then
        B6Port_Frame.Visible = False
        For Each chBox In B6Port_Frame.Controls
            If TypeOf chBox Is MSForms.CheckBox Then chBox.Value = False
        Next
    End If

End Sub

Private Sub C1Checkbox_Click()

    If C1Checkbox.Value = True Then C1Port_Frame.Visible = True
    If C1Checkbox.Value = False Then
        C1Port_Frame.Visible = False
        For Each chBox In C1Port_Frame.Controls
            If TypeOf chBox Is MSForms.CheckBox Then chBox.Value = False
        Next
    End If

End Sub

Private Sub C2Checkbox_Click()

    If C2Checkbox.Value = True Then C2Port_Frame.Visible = True
    If C2Checkbox.Value = False Then
        C2Port_Frame.Visible = False
        For Each chBox In C2Port_Frame.Controls
            If TypeOf chBox Is MSForms.CheckBox Then chBox.Value = False
        Next
    End If

End Sub

Private Sub C3Checkbox_Click()

    If C3Checkbox.Value = True Then C3Port_Frame.Visible = True
    If C3Checkbox.Value = False Then
        C3Port_Frame.Visible = False
        For Each chBox In C3Port_Frame.Controls
            If TypeOf chBox Is MSForms.CheckBox Then chBox.Value = False
        Next
    End If

End Sub

Private Sub C4Checkbox_Click()

    If C4Checkbox.Value = True Then C4Port_Frame.Visible = True
    If C4Checkbox.Value = False Then
        C4Port_Frame.Visible = False
        For Each chBox In C4Port_Frame.Controls
            If TypeOf chBox Is MSForms.CheckBox Then chBox.Value = False
        Next
    End If

End Sub

Private Sub C5Checkbox_Click()

    If C5Checkbox.Value = True Then C5Port_Frame.Visible = True
    If C5Checkbox.Value = False Then
        C5Port_Frame.Visible = False
        For Each chBox In C5Port_Frame.Controls
            If TypeOf chBox Is MSForms.CheckBox Then chBox.Value = False
        Next
    End If

End Sub

Private Sub C6Checkbox_Click()

    If C6Checkbox.Value = True Then C6Port_Frame.Visible = True
    If C6Checkbox.Value = False Then
        C6Port_Frame.Visible = False
        For Each chBox In C6Port_Frame.Controls
            If TypeOf chBox Is MSForms.CheckBox Then chBox.Value = False
        Next
    End If

End Sub

Private Sub D1Checkbox_Click()

    If D1Checkbox.Value = True Then D1Port_Frame.Visible = True
    If D1Checkbox.Value = False Then
        D1Port_Frame.Visible = False
        For Each chBox In D1Port_Frame.Controls
            If TypeOf chBox Is MSForms.CheckBox Then chBox.Value = False
        Next
    End If

End Sub

Private Sub D2Checkbox_Click()

    If D2Checkbox.Value = True Then D2Port_Frame.Visible = True
    If D2Checkbox.Value = False Then
        D2Port_Frame.Visible = False
        For Each chBox In D2Port_Frame.Controls
            If TypeOf chBox Is MSForms.CheckBox Then chBox.Value = False
        Next
    End If

End Sub

Private Sub D3Checkbox_Click()

    If D3Checkbox.Value = True Then D3Port_Frame.Visible = True
    If D3Checkbox.Value = False Then
        D3Port_Frame.Visible = False
        For Each chBox In D3Port_Frame.Controls
            If TypeOf chBox Is MSForms.CheckBox Then chBox.Value = False
        Next
    End If

End Sub

Private Sub D4Checkbox_Click()

    If D4Checkbox.Value = True Then D4Port_Frame.Visible = True
    If D4Checkbox.Value = False Then
        D4Port_Frame.Visible = False
        For Each chBox In D4Port_Frame.Controls
            If TypeOf chBox Is MSForms.CheckBox Then chBox.Value = False
        Next
    End If

End Sub

Private Sub D5Checkbox_Click()

    If D5Checkbox.Value = True Then D5Port_Frame.Visible = True
    If D5Checkbox.Value = False Then
        D5Port_Frame.Visible = False
        For Each chBox In D5Port_Frame.Controls
            If TypeOf chBox Is MSForms.CheckBox Then chBox.Value = False
        Next
    End If

End Sub

Private Sub D6Checkbox_Click()

    If D6Checkbox.Value = True Then D6Port_Frame.Visible = True
    If D6Checkbox.Value = False Then
        D6Port_Frame.Visible = False
        For Each chBox In D6Port_Frame.Controls
            If TypeOf chBox Is MSForms.CheckBox Then chBox.Value = False
        Next
    End If

End Sub

复选框命名约定：

In "Sectors" frame: "AlphaSectCheckbox", "BetaSectCheckbox", "GammaSectCheckbox", "DeltaSectCheckbox" 

In "Antennas" frames: (Alpha) "A1Checkbox", "A2Checkbox", "A3Checkbox", etc / (Beta) "B1Checkbox", "B2Checkbox", "B3Checkbox", etc / (Gamma) "C1Checkbox", "C2Checkbox", "C3Checkbox", etc, etc 

In "Port" frames: (Alpha - Ant 1) "A1P1Checkbox", "A1P2Checkbox", etc / (Alpha - Ant 2) "A2P1Checkbox", "A2P2Checkbox", etc / (Beta - Ant1) "B1P1Checkbox", "B1P2Checkbox", etc / (Beta Ant2) "B2P1Checkbox", "B2P2Checkbox", etc / (Gamma - Ant1) "C1P1Checkbox", "C1P2Checkbox", etc, etc

================= ======编辑1/11/2016
这是我在sous2817答案之后实现的，这个功能很好，我可以从这里继承一小部分代码。这是一个名为clsUFCheckBox的新类模块：

=========================Edited 1/11/2016 Here's what I implemented after sous2817 answer, this works great, and I can carry on from here with a fraction of the code. This is a new Class Module named "clsUFCheckBox":

Option Explicit

Public WithEvents aCheckBox As msforms.CheckBox

Private Sub aCheckBox_Click()
Dim chBox As Control
Dim actFrmStr As String

     MsgBox aCheckBox.Name & " was clicked" & vbCrLf & vbCrLf & _
        "It refers to the frame: " & aCheckBox.Tag & vbCrLf & vbCrLf & _
        "Its Checked State is currently " & aCheckBox.Value, vbInformation + vbOKOnly, _
        aCheckBox.Name & " & State"

    actFrmStr = aCheckBox.Tag

    If aCheckBox.Value = True Then ADSinputform.Controls(actFrmStr).Visible = True
    If aCheckBox.Value = False Then
        ADSinputform.Controls(actFrmStr).Visible = False
        For Each chBox In ADSinputform.Controls(actFrmStr).Controls
            If TypeOf chBox Is msforms.CheckBox Then chBox.Value = False
        Next
    End If

End Sub

推荐答案

这是一个太长的评论，但希望它会让你关闭（或至少开始）一个解决方案。

This is too long for a comment, but hopefully it will get you close (or at least starting towards) a solution.

首先，为您的项目添加一个类。在这个例子中，我调用的是clsUFCheckBox。将以下代码添加到类中：

First, add a class to your project. In this example I called is clsUFCheckBox. Add this following code to the class:

Option Explicit

Public WithEvents aCheckBox As msforms.CheckBox

Private Sub aCheckBox_Click()
     MsgBox aCheckBox.Name & " was clicked" & vbCrLf & vbCrLf & _
        "Its Checked State is currently " & aCheckBox.Value, vbInformation + vbOKOnly, _
        "Check Box # & State"
End Sub

以上内容创建一个点击事件，将触发一个集合中的每个复选框（稍后即将出现）。此测试代码将显示一个消息框，告诉您单击了哪个复选框以及当前状态。显然，你会改变这一点，以适应你需要解决当前问题的任何逻辑，但这将让你开始。

The above creates a click event that will be fired to each checkbox in a collection (that will be coming up shortly). This test code will show you a message box that tells you what check box was clicked and what the current state is. Obviously you'll change this to suit whatever logic you need to solve your current problem, but this will get you started.

现在，在你的用户表单代码中，添加行在顶部（在Option Explicit行下方）

Now, in your user form code, add this line at the top (right under your Option Explicit line)

Dim myCheckBoxes() As clsUFCheckBox

这将创建一个上面创建的类的数组，以将所有复选框保存在表单上。现在在您的Userform_Initialize（）事件中，添加以下代码：

This creates an array of the class you created above to hold all of your checkboxes on your form. Now in your Userform_Initialize() event, add this code:

Dim ctl As Object, pointer As Long
ReDim myCheckBoxes(1 To Me.Controls.Count)

    For Each ctl In Me.Controls
        If TypeName(ctl) = "CheckBox" Then
            pointer = pointer + 1
            Set myCheckBoxes(pointer) = New clsUFCheckBox
            Set myCheckBoxes(pointer).aCheckBox = ctl
        End If
    Next ctl

ReDim Preserve myCheckBoxes(1 To pointer)

此代码将循环遍历用户窗体的所有控件，并将复选框添加到您创建的数组中以上。此外，由于您将所有复选框与所创建的类相关联，所以您将给他们一个常见的点击事件。因此，您点击的每个复选框都会运行相同的点击事件。

This code will loop through all of the controls on your userform and add the checkboxes to the array you created above. Also, since you're associating all of the checkboxes to the class you've created, you're giving them a common click event. So each checkbox that you click, runs the same click event.

所以你去。在开始时创建的数十个复选框，它们都运行相同的_click（）事件。您可以在该单个事件中放置逻辑来处理每个分组。好的是，你只需要编写一次代码，而不必将它添加到每个单独的checkbox_click事件中。

So there you go. The dozens and dozens of checkboxes you created at the start, they all run the same _click() event. You can put logic in that single event to handle each grouping. The nice thing is, you only have to write the code once and you don't have to add it to each individual checkbox_click event.

如果您需要一个测试工作簿，以便您可以看到所有的电子邮件，让我知道，我会看到，如果我能找到一个地方上传一个给你评论。

If you need a test workbook so you can see it all wired up, let me know and I'll see if I can find a place to upload one for you to review.

希望有所帮助。如果你被卡住了，请发回来，我们会继续打击。

Hope that helps. If you get jammed up, please post back and we'll keep hammering at it.

这篇关于Excel宏用户窗体 - 单个代码处理多个复选框的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！