简化切换按钮更改背色代码VBA

Question

im在VBA制作中的新事物，因此下面的所有代码仍在工作，但是需要大量代码。即使有人可以简化我的Noob代码来剪一些线条并更令人愉悦，甚至更容易维护？

我的用户形式中有20多个切换按钮

这是我的代码的示例，需要帮助使其更简单

Private Sub tgglC_Result1_Click()
If tgglC_Result1.Value = True Then
    tgglC_Result1.BackColor = &HFF00&
    tgglNC_Result1.Enabled = False
    lblResult1.Caption = Now
    lblResult1.Visible = True
Else
    tgglC_Result1.BackColor = &H8000000F
    tgglNC_Result1.Enabled = True
    lblResult1.Visible = False
End If
End Sub
Private Sub tgglC_Result2_Click()
If tgglC_Result2.Value = True Then
    tgglC_Result2.BackColor = &HFF00&
    tgglNC_Result2.Enabled = False
    lblResult2.Caption = Now
    lblResult2.Visible = True
Else
    tgglC_Result2.BackColor = &H8000000F
    tgglNC_Result2.Enabled = True
    lblResult2.Visible = False
End If
End Sub
Private Sub tgglC_Result3_Click()
If tgglC_Result3.Value = True Then
    tgglC_Result3.BackColor = &HFF00&
    tgglNC_Result3.Enabled = False
    lblResult3.Caption = Now
    lblResult3.Visible = True
Else
    tgglC_Result3.BackColor = &H8000000F
    tgglNC_Result3.Enabled = True
    lblResult3.Visible = False
End If
End Sub
Private Sub tgglC_Result4_Click()
If tgglC_Result4.Value = True Then
    tgglC_Result4.BackColor = &HFF00&
    tgglNC_Result4.Enabled = False
    lblResult4.Caption = Now
    lblResult4.Visible = True
Else
    tgglC_Result4.BackColor = &H8000000F
    tgglNC_Result4.Enabled = True
    lblResult4.Visible = False
End If
End Sub

Answer 1

最佳方法应该使用类

但是，一种更"常规"的方式也可以帮助您减少打字负担：

• 定义独特的切换控制处理子

      Private Sub tgglC_Result_Click()
          Dim NC As Control
          With Me
              Set NC = .Controls(VBA.Replace(.ActiveControl.Name, "tgglC", "tgglNC")) '<--| set the "counter part" toggle button control of the "Active" control (i.e. the one being currently toggled)
              With .ActiveControl
                  .BackColor = IIf(.Value, &HFF00&, &H8000000F)
                  NC.Enabled = Not .Value
              End With
          End With
      End Sub
  

• 从您的任何活动处理程序中调用它

  Private Sub tgglC_Result1_Click()
      tgglC_Result_Click
  End Sub
  Private Sub tgglC_Result2_Click()
      tgglC_Result_Click
  End Sub
  Private Sub tgglC_Result3_Click()
      tgglC_Result_Click
  End Sub
  ...
  

Answer 2

并不是真正的简化解决方案，但这是我需要在访问子图上提供60 控件的逻辑时使用的（与您的任务相似）：

Sub makeCode()
Dim i As Integer    
For i = 1 To 4
        Debug.Print "Private Sub tgglC_Result" & i & "_Click()"
        Debug.Print "tgglC_Result" & i & ".BackColor = &HFF00&"
        Debug.Print "tgglNC_Result2.Enabled = False"
        Debug.Print "lblResult" & i & ".Caption = Now"
        Debug.Print "lblResult" & i & ".Visible = True"
        Debug.Print "End Sub"
        Debug.Print ""
    Next
End Sub

将结果从直接窗口复制到代码编辑器中。也很容易更改所有子例程：只需更改循环主体，运行它并替换旧代码。