是否要寫30個 Private Sub CheckBox*_Change()才可?

yangjie

請教於各位板主大大:
Private Sub CheckBox1_Change()
    ListBox1.Clear
    For i = 1 To 27
        If Controls("CheckBox" & i).Value = True Then
            x = Controls("CheckBox" & i).Caption
            ListBox1.AddItem x
        End If
    Next
End Sub
以上情形若 CheckBox1 之外尚有CheckBox2 CheckBox3 CheckBox4........Change() 共30個
是否要寫30個 Private Sub CheckBox*_Change()
可否另有 Loop 方式??? 或其他簡化方式
    謝謝指導

yangjie

回復 2# Hsieh


    請教Hsieh版主:
可否教個範例(因從未寫過)

yangjie

回復 2# Hsieh


    請教Hsieh版主:
自己嘗試看看
Option Explicit
Dim newcontrol() As New Class1
Private Sub UserForm_Initialize()
  ReDim newcontrol(1 To 27)
    For I = 1 To 27
        Set newcontrol(I).Comd = Controls("CheckBox" & I)
    Next
End Sub
於類別模組下
Public WithEvents Comd As MSForms.CommandButton
Private Sub Comd_Click()
    ListBox1.Clear
    For I = 1 To 27
        If Controls("CheckBox" & I).Value = True Then
            x = Controls("CheckBox" & I).Caption
            ListBox1.AddItem x
        End If
    Next
End Sub
不知哪裡出差錯???

yangjie

回復 2# Hsieh


謝謝Hsieh版主:      OK 了  做以下修正就OK了
Option Explicit
Dim newcontrol() As New Class1
Private Sub UserForm_Initialize()
  ReDim newcontrol(1 To 27)
    For I = 1 To 27
        Set newcontrol(I).Comd = Controls("CheckBox" & I)
    Next
End Sub
於類別模組下
Public WithEvents Comd As MSForms.CheckBox
Private Sub Comd_Click()
    userform3.ListBox1.Clear
    For I = 1 To 27
        If userform3.Controls("CheckBox" & I).Value = True Then
            x = userform3.Controls("CheckBox" & I).Caption
            userform3.ListBox1.AddItem x
        End If
    Next
End Sub

yangjie

回復 5# yangjie


    亦感謝GBKee 版主曾經指導類別模組VBA
很高興能夠突破此難題