vba - 从旋转按钮更新表单组件可见性的更好方法？

Question

我想知道是否有人可以阐明一种更简化的方式来执行以下操作？

基本上，当 Spin 按钮发生变化时，它会跳转到标签并更新可见的行代码工作得很好，但我想知道是否有一种不那么笨重的方式将其编码到表单中？

    Private Sub Meds_Change()
    meds_txt.Text = Meds.value
End Sub


Private Sub meds_txt_Change()
    If meds_txt.value > 1 Then
        If meds_txt.value = 2 Then GoTo ShowTwo
        If meds_txt.value = 3 Then GoTo ShowThree
        If meds_txt.value = 4 Then GoTo ShowFour
        If meds_txt.value = 5 Then GoTo ShowFive
        If meds_txt.value = 6 Then GoTo ShowSix
        If meds_txt.value = 7 Then GoTo ShowSeven
    Else: GoTo ShowOne
    End If


Exit Sub

ShowOne:

    labelTwo.Visible = False: MNameTwo.Visible = False
    MDoseTwo.Visible = False: MFrequencyTwo.Visible = False
    labelThree.Visible = False: MNameThree.Visible = False
    MDoseThree.Visible = False: MFrequencyThree.Visible = False
    labelFour.Visible = False: MNameFour.Visible = False
    MDoseFour.Visible = False: MFrequencyFour.Visible = False
    labelFive.Visible = False: MNameFive.Visible = False
    MDoseFive.Visible = False: MFrequencyFive.Visible = False
    labelSix.Visible = False: MNameSix.Visible = False
    MDoseSix.Visible = False: MFrequencySix.Visible = False
    labelSeven.Visible = False: MNameSeven.Visible = False
    MDoseSeven.Visible = False: MFrequencySeven.Visible = False
    GoTo Finish
ShowTwo:
    labelTwo.Visible = True: MNameTwo.Visible = True
    MDoseTwo.Visible = True: MFrequencyTwo.Visible = True
    labelThree.Visible = False: MNameThree.Visible = False
    MDoseThree.Visible = False: MFrequencyThree.Visible = False
    labelFour.Visible = False: MNameFour.Visible = False
    MDoseFour.Visible = False: MFrequencyFour.Visible = False
    labelFive.Visible = False: MNameFive.Visible = False
    MDoseFive.Visible = False: MFrequencyFive.Visible = False
    labelSix.Visible = False: MNameSix.Visible = False
    MDoseSix.Visible = False: MFrequencySix.Visible = False
    labelSeven.Visible = False: MNameSeven.Visible = False
    MDoseSeven.Visible = False: MFrequencySeven.Visible = False
    GoTo Finish
ShowThree:
    labelTwo.Visible = True: MNameTwo.Visible = True
    MDoseTwo.Visible = True: MFrequencyTwo.Visible = True
    labelThree.Visible = True: MNameThree.Visible = True
    MDoseThree.Visible = True: MFrequencyThree.Visible = True
    labelFour.Visible = False: MNameFour.Visible = False
    MDoseFour.Visible = False: MFrequencyFour.Visible = False
    labelFive.Visible = False: MNameFive.Visible = False
    MDoseFive.Visible = False: MFrequencyFive.Visible = False
    labelSix.Visible = False: MNameSix.Visible = False
    MDoseSix.Visible = False: MFrequencySix.Visible = False
    labelSeven.Visible = False: MNameSeven.Visible = False
    MDoseSeven.Visible = False: MFrequencySeven.Visible = False
    GoTo Finish
ShowFour:
    labelTwo.Visible = True: MNameTwo.Visible = True
    MDoseTwo.Visible = True: MFrequencyTwo.Visible = True
    labelThree.Visible = True: MNameThree.Visible = True
    MDoseThree.Visible = True: MFrequencyThree.Visible = True
    labelFour.Visible = True: MNameFour.Visible = True
    MDoseFour.Visible = True: MFrequencyFour.Visible = True
    labelFive.Visible = False: MNameFive.Visible = False
    MDoseFive.Visible = False: MFrequencyFive.Visible = False
    labelSix.Visible = False: MNameSix.Visible = False
    MDoseSix.Visible = False: MFrequencySix.Visible = False
    labelSeven.Visible = False: MNameSeven.Visible = False
    MDoseSeven.Visible = False: MFrequencySeven.Visible = False
    GoTo Finish
ShowFive:
    labelTwo.Visible = True: MNameTwo.Visible = True
    MDoseTwo.Visible = True: MFrequencyTwo.Visible = True
    labelThree.Visible = True: MNameThree.Visible = True
    MDoseThree.Visible = True: MFrequencyThree.Visible = True
    labelFour.Visible = True: MNameFour.Visible = True
    MDoseFour.Visible = True: MFrequencyFour.Visible = True
    labelFive.Visible = True: MNameFive.Visible = True
    MDoseFive.Visible = True: MFrequencyFive.Visible = True
    labelSix.Visible = False: MNameSix.Visible = False
    MDoseSix.Visible = False: MFrequencySix.Visible = False
    labelSeven.Visible = False: MNameSeven.Visible = False
    MDoseSeven.Visible = False: MFrequencySeven.Visible = False
    GoTo Finish
ShowSix:
    labelTwo.Visible = True: MNameTwo.Visible = True
    MDoseTwo.Visible = True: MFrequencyTwo.Visible = True
    labelThree.Visible = True: MNameThree.Visible = True
    MDoseThree.Visible = True: MFrequencyThree.Visible = True
    labelFour.Visible = True: MNameFour.Visible = True
    MDoseFour.Visible = True: MFrequencyFour.Visible = True
    labelFive.Visible = True: MNameFive.Visible = True
    MDoseFive.Visible = True: MFrequencyFive.Visible = True
    labelSix.Visible = True: MNameSix.Visible = True
    MDoseSix.Visible = True: MFrequencySix.Visible = True
    labelSeven.Visible = False: MNameSeven.Visible = False
    MDoseSeven.Visible = False: MFrequencySeven.Visible = False
    GoTo Finish
ShowSeven:
    labelTwo.Visible = True: MNameTwo.Visible = True
    MDoseTwo.Visible = True: MFrequencyTwo.Visible = True
    labelThree.Visible = True: MNameThree.Visible = True
    MDoseThree.Visible = True: MFrequencyThree.Visible = True
    labelFour.Visible = True: MNameFour.Visible = True
    MDoseFour.Visible = True: MFrequencyFour.Visible = True
    labelFive.Visible = True: MNameFive.Visible = True
    MDoseFive.Visible = True: MFrequencyFive.Visible = True
    labelSix.Visible = True: MNameSix.Visible = True
    MDoseSix.Visible = True: MFrequencySix.Visible = True
    labelSeven.Visible = True: MNameSeven.Visible = True
    MDoseSeven.Visible = True: MFrequencySeven.Visible = True
    GoTo Finish

Finish:
End Sub

Answer 1

从概念上讲，我会将所有控件加载到一维数组中，并使它们在循环中可见......就像这样(这是在表单的代码空间中):

Dim MyCtls(6) As Control

Private Sub SpinButton1_Change()
    ' let the index not go outside the array dimension
    If Me.SpinButton1 < 1 Then Me.SpinButton1 = 1
    If Me.SpinButton1 > 7 Then Me.SpinButton1 = 7

    Me.SpinText = Me.SpinButton1
End Sub

Private Sub SpinText_Change()
Dim Idx As Integer

    For Idx = 1 To 7
        ' controls with indices <= spinbutton value are visible, higher ones are dark
        If Idx <= Me.SpinText Then
            MyCtls(Idx - 1).Visible = True
        Else
            MyCtls(Idx - 1).Visible = False
        End If
    Next Idx

End Sub

Private Sub UserForm_Initialize()
Dim Idx As Integer

    ' load TB1..7 controls into array (index 0-6)
    For Idx = 1 To 7
        Set MyCtls(Idx - 1) = Me.Controls("TB" & Idx)
    Next Idx
End Sub

有7个文本框 TB1至 TB7它们被加载到一维数组中...您可以使用 4 个数组 - 每个字段类型 1 个...重要的是可以通过循环计数器 Idx 以某种方式构造名称...其余的应该是不言自明的。