gpt4 book ai didi

VBA - 在动态创建的文本框上捕获事件

转载 作者:行者123 更新时间:2023-12-04 20:42:05 28 4
gpt4 key购买 nike

我正在用 Excel 编写一个 VBA 应用程序。我有一个用户表单,它根据其中一个工作表中包含的数据动态构建自身。
创建各种组合框、文本框和标签的所有代码都在工作。
我创建了一个类模块来捕获组合框的 OnChange 事件,并且再次按预期工作。
现在我需要为某些文本框捕获 OnChange 事件,因此我创建了一个新的类模块,以组合框为模型来捕获事件。

Public WithEvents tbx As MSForms.TextBox

Sub SetTextBox(ctl As MSForms.TextBox)
Set tbx = ctl
End Sub

Public Sub tbx_Change()
Dim LblName As String

MsgBox "You clicked on " & tbx.Name, vbOKOnly

End Sub

消息框只是为了让我可以在进一步操作之前确认它是否有效。
我遇到的问题出在 UserForm 代码模块中:
Dim TBox As TextBox
Dim tbx As c_TextBoxes

'[...]

Set TBox = lbl
Set tbx = New c_TextBoxes
tbx.SetTextBox lbl
pTextBoxes.Add tbx

这会在 Set TBox=lbl 处引发类型不匹配错误。 .这是 准确 相同的代码适用于 ComboBox,只是使用给定适当名称的变量。我已经盯着这个看了2个小时。
有人有什么想法吗?感谢您的任何指示。

编辑 - 这是我遇到问题的完整用户表单模块:
Private Sub AddLines(FrameName As String, PageName As String)
Dim Counter As Integer, Column As Integer
Dim obj As Object
Dim CBox As ComboBox
Dim cbx As c_ComboBox
Dim TBox As TextBox
Dim tbx As c_TextBoxes
Dim lbl As Control

Set obj = Me.MultiPage1.Pages(PageName).Controls(FrameName)
If pComboBoxes Is Nothing Then Set pComboBoxes = New Collection
If pTextBoxes Is Nothing Then Set pTextBoxes = New Collection

For Counter = LBound(Vehicles) To UBound(Vehicles)
For Column = 1 To 8
Select Case Column
Case 1
Set lbl = obj.Add("Forms.Label.1", "LblMachine" & FrameName & Counter, True)
Case 2
Set lbl = obj.Add("Forms.Label.1", "LblFleetNo" & FrameName & Counter, True)
Case 3
Set lbl = obj.Add("Forms.Label.1", "LblRate" & FrameName & Counter, True)
Case 4
Set lbl = obj.Add("Forms.Label.1", "LblUnit" & FrameName & Counter, True)
Case 5
Set lbl = obj.Add("Forms.ComboBox.1", "CBDriver" & FrameName & Counter, True)
Case 6
Set lbl = obj.Add("Forms.Label.1", "LblDriverRate" & FrameName & Counter, True)
Case 7
Set lbltbx = obj.Add("Forms.TextBox.1", "TBBookHours" & FrameName & Counter, True)
Case 8
Set lbl = obj.Add("Forms.Label.1", "LblCost" & FrameName & Counter, True)
End Select
With lbl
Select Case Column
Case 1
.Left = 1
.Width = 60
.Top = 10 + (Counter) * 20
.Caption = Vehicles(Counter).VType
Case 2
.Left = 65
.Width = 40
.Top = 10 + (Counter) * 20
.Caption = Vehicles(Counter).VFleetNo
Case 3
.Left = 119
.Width = 50
.Top = 10 + (Counter) * 20
.Caption = Vehicles(Counter).VRate
Case 4
.Left = 163
.Width = 30
.Top = 10 + (Counter) * 20
.Caption = Vehicles(Counter).VUnit
Case 5
.Left = 197
.Width = 130
.Top = 10 + (Counter) * 20
Set CBox = lbl 'WORKS OK
Call CBDriver_Fill(Counter, CBox)
Set cbx = New c_ComboBox
cbx.SetCombobox CBox
pComboBoxes.Add cbx
Case 6
.Left = 331
.Width = 30
.Top = 10 + (Counter) * 20
Case 7
.Left = 365
.Width = 30
.Top = 10 + (Counter) * 20
Set TBox = lbl 'Results in Type Mismatch
Set tbx = New c_TextBoxes
tbx.SetTextBox TBox
pTextBoxes.Add tbx
Case 8
.Left = 400
.Width = 30
.Top = 10 + (Counter) * 20
End Select
End With
Next
Next
obj.ScrollHeight = (Counter * 20) + 20
obj.ScrollBars = 2

End Sub

这是 c_Combobox 类模块:
Public WithEvents cbx As MSForms.ComboBox

Sub SetCombobox(ctl As MSForms.ComboBox)
Set cbx = ctl
End Sub

Public Sub cbx_Change()
Dim LblName As String
Dim LblDriverRate As Control
Dim i As Integer


'MsgBox "You clicked on " & cbx.Name, vbOKOnly
LblName = "LblDriverRate" & Right(cbx.Name, Len(cbx.Name) - 8)
'MsgBox "This is " & LblName, vbOKOnly

'Set obj = Me.MultiPage1.Pages(PageName).Controls(FrameName)
Set LblDriverRate = UFBookMachines.Controls(LblName)
For i = LBound(Drivers) To UBound(Drivers)
If Drivers(i).Name = cbx.Value Then LblDriverRate.Caption = Drivers(i).Rate
Next
End Sub

最后,这是 c_TextBoxes 类模块:
Public WithEvents tbx As MSForms.TextBox

Sub SetTextBox(ctl As MSForms.TextBox)
Set tbx = ctl
End Sub

Public Sub tbx_Change()
Dim LblName As String
'Does nothing useful yet, message box for testing
MsgBox "You clicked on " & tbx.Name, vbOKOnly

End Sub

最佳答案

经过一些快速测试,如果我声明 TBox as TextBox,我能够重现您的错误。 .如果我声明 TBox as MSForms.TextBox,我不会收到错误消息.我建议您声明所有的 TextBox带有 MSForms 的变量限定符。

测试码 位于与您的相似。我有一个 MultiPageFrame我在哪里添加 Control .

Private Sub CommandButton1_Click()

Dim obj As Object
Set obj = Me.MultiPage1.Pages(0).Controls("Frame1")

Dim lbl As Control
Set lbl = obj.Add("Forms.TextBox.1", "txt", True)

If TypeOf lbl Is TextBox Then
Debug.Print "textbox found1" 'does not execute
End If

If TypeOf lbl Is MSForms.TextBox Then
Debug.Print "textbox found2"

Dim txt1 As MSForms.TextBox
Set txt1 = lbl 'no error
End If

If TypeOf lbl Is MSForms.TextBox Then
Debug.Print "textbox found3"

Dim txt As TextBox
Set txt = lbl 'throws an error
End If

End Sub

我不确定为什么 TextBox 需要限定符而不是 ComboBox .正如你在上面看到的,一个很好的测试是 If TypeOf ... Is ... Then测试哪些对象是哪些类型。我包含了第一个 block 来显示 lbl不是“裸” TextBox ,但是,我也不知道为什么会这样。也许还有另一种类型 TextBox那里会覆盖默认声明?

关于VBA - 在动态创建的文本框上捕获事件,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/30899487/

28 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com