excel - 当其他 ComboBox 更改时,Excel VBA 将项目添加到程序化 ComboBox
问题描述
我以编程方式在 Multipage1 中添加一个新页面
Private Sub CommandButton3_Click()
Dim i As Integer
i = MultiPage1.Pages.Count
MultiPage1.Pages.Add.Caption = "Guarantee " & i
然后我想要我的新页面包含 3 个 ComboBoxes,ComboBox1 将在我的工作表名称“LeftTB”上列出表中的项目,这是代码。
For r = 1 To 3
Set myCB = MultiPage1.Pages(i).Controls.Add("Forms.ComboBox.1", "ComboBox" & r, 1)
With myCB
.Width = 150
.Height = 18
Select Case r
Case Is = 1
.Left = 54
.Top = 156
'add item to combobox1
Dim rng, cl As Range
'LeftTB is the name of Table contain Data
Set rng = Range("LeftTB")
For Each cl In rng
.AddItem cl.Value
Next cl
Case Is = 2
.Left = 252
.Top = 156
Case Is = 3
.Left = 54
.Top = 180
End Select
End With
Next r
End Sub
通过此代码在 ComboBox1 中添加值可以正常工作。对于 ComboBox 2 中的项目,它取决于 ComboBox1 的值,如下代码所示。
Private Sub ComboBox1_Change()
Dim rng, cl As Range
'The CenterTB is the table in my worksheet with two columns. The first column (SubD) contains the same data as table "LeftTB" and the next column is the item I would like to add to ComboBox2
Set rng = Range("CenterTB[SubD]")
For i = 1 to me.MultiPage1.Pages.Count
me.MultiPage1.Pages(i).Controls("ComboBox2").Clear
For Each cl In rng
If cl.Value = me.MultiPage1.Pages(i).Controls("ComboBox1").Text Then
me.MultiPage1.Pages(i).Controls("ComboBox2").AddItem cl.Offset(0, 1).Value
End If
Next cl
Next i
End Sub
但是,当 ComboBox1 是编程时,它不起作用。我不知道在编程时检测 Combobox1 的更改过程。
有人可以为我提供解决方案吗?
解决方案
在动态创建控件时,VBA 不会自动创建它们的事件!
有两种方法可以做到这一点。要创建一个事件包装器类,或更简单,如果要添加已知数量的控件(您的情况)以先前以特定方式声明控件:
- 将下一个声明放在表单代码模块的顶部(在声明区域中):
Option Explicit
Private WithEvents ComboBox1 As MSForms.ComboBox
Private WithEvents ComboBox2 As MSForms.ComboBox 'possible to use its events, too
Private WithEvents ComboBox3 As MSForms.ComboBox 'possible to use its events, too
- 然后你应该调整你的代码,将上面声明的变量设置为你想要/需要的。因此,请按以下方式调整您的代码:
Private Sub btCreateCmb_Click()
Dim myCB As MSForms.ComboBox, r As Long
For r = 1 To 3
Set myCB = MultiPage1.Pages(i).Controls.Add("Forms.ComboBox.1", "ComboBox" & r, 1)
With myCB
.Width = 150
.Height = 18
Select Case r
Case Is = 1
Set ComboBox1 = myCB 'added to Set your first combo
.Left = 54
.Top = 156
'add item to combobox1
Dim rng, cl As Range
'LeftTB is the name of Table contain Data
Set rng = Range("LeftTB")
For Each cl In rng
.AddItem cl.Value
Next cl
Case Is = 2
Set ComboBox2 = myCB 'added to Set your second combo
.Left = 252
.Top = 156
Case Is = 3
Set ComboBox3 = myCB 'added to Set your third combo
.Left = 54
.Top = 180
End Select
End With
Next r
End Sub
- 只有现在,才会触发组合事件(如果它们的代码存在于表单代码模块中)。请首先使用下一个短代码示例:
Private Sub ComboBox1_Change()
MsgBox "Changed 1..."
End Sub
如果您完全按照上述建议进行操作,它肯定会被触发!
现在,您可以根据需要放入其事件代码...
请测试它并发送一些反馈。
推荐阅读
- go - 如何为 idtoken.NewClient 设置超时选项
- c# - 如何使用 .contains() 方法检查我的列表中是否有任何单词
- javascript - 选择选择器未显示所选选项
- excel - vba可见总过滤器
- bash - 双while循环中的Bash反向逻辑
- typescript - Nuxtjs:模块解析失败:意外字符'@'
- ios - 使用通用类型时,类型“FetchAPI”不符合协议“可解码/可编码”
- postgresql - 如何在使用 pyodbc 查询 PostgreSQL 9.5 表的 Python 脚本中修复“类型 tid 的无效输入语法”?
- linux - 如何在 Linux 中自动创建目录结构?
- windows - 线程消息泵