组合框更改事件的类模块代码 [英] Class Module codes for Change Event of comboboxes

问题描述

我有一个用户窗体，其中我将10行组合框放在7列上。这意味着我总共有70个组合框。为了简化您的理解，我将第一个组合框称为（row，column）的（1,1）。

I have a userform where I put 10 rows of comboboxes for 7 columns. Which means I got 70 comboboxes altogether. To ease your understanding, I will refer the first combobox as (1,1) for (row,column).

我想做的是，当一个用户在第1行的任何组合框中输入值，我希望将值复制到在第2行的相邻组合框中。

What am I trying to do is, when a user input values on any combobox on Row 1, I want the values to be copied on its adjacent combobox at Row 2.

例如，如果我在（1， 3），相同的值将出现在（2,3）上。第三行和第二行4，第5行和6，依此类推。

For example, if I select value on (1,3), same value will appear on (2,3). The same thing goes to Row 3 & 4, Row 5 & 6, and so on.

这是我的类模块 clsLineCopy 上的代码：

This is the code on my class module clsLineCopy:

Public WithEvents myCbo As msForms.ComboBox

Private Sub myCbo_Change()
    Dim i As Integer

    'MsgBox "Combo Box " & myCbo.Value & " has changed"
    If myCbo.Value <> "" Then
        myCbo.Copy
        myCbo.Paste
    End If
End Sub

这是我在userform_initialize上的代码：

This one is my code on my userform_initialize:

Dim myCommonCbo As New Collection
Dim cbo As clsLineCopy
For i = 1 To 70
    Set cbo = New clsLineCopy
    Set cbo.myCbo = Me.Controls("ComboBox" & i)
    myCommonCbo.Add Item:=cbo
Next i
Set cbo = Nothing

我知道我的类模块中的代码是错误的，因为我对此一无所知。

I know my code in the class module is wrong as I have no idea about it.

谢谢，
Izz。

Thanks, Izz.

推荐答案

在我的演示中，我将 Userform->命名为FormComboGrid

以下是您需要的更改：

• 用户窗体：公共回调方法



• 用户窗体：用于防止级联回调的类级别布尔变量

• Userform: Public CallBack method

• Userform: Class level boolean variable used to prevent cascading CallBacks

必须将myCommonCbo提升为 Class Level Variable 。在 UserForm_Initialize 完成执行之后，这将使引用保持有效。

myCommonCbo has to be elevated to a Class Level Variable. This keeps the references valid after the UserForm_Initialize finishes execution.

• clsLineCopy 应该具有 Init 方法，该方法用于传递对Userform实例和所钩住的组合框的引用。

• clsLineCopy should have an Init method used to pass a reference of the Userform instance and the Combobox that is being hooked.

Option Explicit
Private myCommonCbo  As New Collection
Private ComboBoxEventEnabled As Boolean

Private Sub UserForm_Initialize()
    Dim i As Long
    Dim cbo As clsLineCopy
    For i = 1 To 70
        Set cbo = New clsLineCopy
        cbo.Init Me, Me.Controls("ComboBox" & i)
        myCommonCbo.Add Item:=cbo
       ' Me.Controls("ComboBox" & i).List = Array(1, 2, 3, 4, 5, 6, 7)
    Next i

    ComboBoxEventEnabled = True
End Sub

Public Sub ComboboxChange(cbo As MSForms.ComboBox)
    If Not ComboBoxEventEnabled Then Exit Sub
    ComboBoxEventEnabled = False

    Dim index As Long, r As Long, c As Long
    Dim myCbo As MSForms.ComboBox
    index = Replace(cbo.Name, "ComboBox", "")
    c = index Mod 10
    r = Int(index / 10) + 1
    If r = 7 Then Exit Sub

    index = ((r * 10) + c)
    Set myCbo = Me.Controls("ComboBox" & index)
    myCbo.Value = cbo.Value

    ComboBoxEventEnabled = True
End Sub

clsLineCopy：Class

clsLineCopy:Class

Option Explicit

Private WithEvents myCbo As MSForms.ComboBox
Private mForm As FormComboGrid

Private Sub myCbo_Change()
    mForm.ComboboxChange myCbo
End Sub

Public Sub Init(Form As FormComboGrid, cbo As MSForms.ComboBox)
    Set mForm = Form
    Set myCbo = cbo
End Sub

这篇关于组合框更改事件的类模块代码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！