gpt4 book ai didi

Excel UserForm 动态 TextBox 控件退出事件

转载 作者:行者123 更新时间:2023-12-04 19:55:51 33 4
gpt4 key购买 nike

更新:在对象浏览器中的进一步研究...看来 MSForms.TextBox 既没有实现 .Name 属性也没有实现_Exit 事件 - 只有 _Change 事件。有没有办法确定哪个特定的 TextBox 生成了更改事件?

或者,是否可以通过这种技术使用 MSForms.ControlControl 对象实现了 .Name 属性和 _Exit 事件。


你能监听 TextBox 退出事件吗?类似于普通的 TextBox 事件如何工作?例如

  Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
'Update a certain label based on the value of the TextBox
End Sub

以下不捕获退出事件。此外,虽然我可以在本地窗口中看到为 MyTextBox 生成事件的 TextBox 的 .Name 属性,但我无法访问该信息来确定要作用于哪个标签。

此类技术改编自 this post , 和 this post , 它捕获了变化事件。

类 clsTextBox:

Private WithEvents MyTextBox As MSForms.TextBox

Public Property Set Control(tb As MSForms.TextBox)
Set MyTextBox = tb
End Property

' Want to handle this event, but it's not caught when exiting the TextBox control
Private Sub MyTextBox_Exit(ByVal Cancel As MSForms.ReturnBoolean)
'Debug.Print me.Control.name
'Update a certain label based on the value of the TextBox
Stop
End Sub

' Catching this event but can't identify the control which triggered it
Private Sub MyTextBox_Change()
Debug.Print MyTextBox.Value ' <--- This prints the correct value
Debug.Print Me.Control.Name ' <--- ERROR here on any variation of Me or MyTextBox
'Update a certain label based on the value of the TextBox
Stop
End Sub

我有一系列需要监听器的动态创建控件。代码如下:

  Option Explicit
Dim tbCollection As Collection

Private Sub UserForm_Initialize()
Dim ctrl As MSForms.Control
Dim obj As clsTextBox
Dim acftNumber As Long
Dim mPage As MSForms.MultiPage ' Control
Dim lbl_acftName As MSForms.Label
Dim lbl_currentHrs As MSForms.Label
Dim lbl_hrsDUE As MSForms.Label
Dim lbl_dateXFRIn As MSForms.Label
Dim lbl_dateXFROut As MSForms.Label
Dim lbl_hrsOnXFROut As MSForms.Label
Dim txb_currentHrs As MSForms.TextBox
Dim txb_hrsDUE As MSForms.TextBox
Dim txb_dateXFRIn As MSForms.TextBox
Dim txb_dateXFROut As MSForms.TextBox
Dim txb_hrsOnXFROut As MSForms.TextBox
Dim i As Double
Dim pgName As String
Dim acftName As String

' Correct for border size calculations bug in Excel 2016
Me.Height = 249.75
Me.Width = 350.25

acftNumber = Range("aircraft").Count 'Unknown value from 3 to 10

Set mPage = Me.multipage_file_week 'set Multipage variable

For i = 1 To acftNumber
'set name/title for new page
pgName = "pg_acft_" & i
acftName = Range("aircraft").Cells(i, 1).Value

'mPage.Pages.Add pgName, pgTitle

With mPage 'add acft tab
' add the aircraft page to the multipage
.Pages.Add pgName, acftName

' Aircraft Name Label
Set lbl_acftName = .Pages(i).Controls.Add("Forms.Label.1", "lbl_acftName_" & i, True)
With lbl_acftName
.Caption = acftName
.Font = "Arial"
.Font.Size = 12
.Font.Bold = True
.Left = 10
.Width = 55
.Top = 0
End With

' Current Hours Label and TextBox
Set lbl_currentHrs = .Pages(i).Controls.Add("Forms.Label.1", "lbl_currentHrs_" & i, True)
With lbl_currentHrs
.Caption = "Current Asset Hours:"
.TextAlign = fmTextAlignRight
.Font = "Arial"
.Font.Size = 10
.Font.Bold = False
.Left = 20
.Width = 120
.Top = 25
End With
Set txb_currentHrs = .Pages(i).Controls.Add("Forms.TextBox.1", "txb_currentHrs_" & i, True)
With txb_currentHrs
.Value = "16004.5"
.Text = "16004.5"
.Font = "Arial"
.Font.Size = 10
.Font.Bold = False
.Left = 150
.Width = 70
.Top = 25
End With


' Hours DUE Label and TextBox
Set lbl_hrsDUE = .Pages(i).Controls.Add("Forms.Label.1", "lbl_hrsDUE_" & i, True)
With lbl_hrsDUE
.Caption = "Hours next HMC DUE:"
.TextAlign = fmTextAlignRight
.Font = "Arial"
.Font.Size = 10
.Font.Bold = False
.Left = 20
.Width = 120
.Top = 50
End With
Set txb_hrsDUE = .Pages(i).Controls.Add("Forms.TextBox.1", "txb_hrsDUE_" & i, True)
With txb_hrsDUE
.Value = "16004.5"
.Text = "16004.5"
.Font = "Arial"
.Font.Size = 10
.Font.Bold = False
.Left = 150
.Width = 70
.Top = 50
End With

' Date XFR In Label and TextBox
Set lbl_dateXFRIn = .Pages(i).Controls.Add("Forms.Label.1", "lbl_dateXFRIn_" & i, True)
With lbl_dateXFRIn
.Caption = "Estimated arrival date:"
.TextAlign = fmTextAlignRight
.Font = "Arial"
.Font.Size = 10
.Font.Bold = False
.Left = 20
.Width = 120
.Top = 75
End With

Set txb_dateXFRIn = .Pages(i).Controls.Add("Forms.TextBox.1", "txb_hrsDUE_" & i, True)
With txb_dateXFRIn
.Value = "4/16/2019"
.Text = "4/16/2019"
.Font = "Arial"
.Font.Size = 10
.Font.Bold = False
.Left = 150
.Width = 70
.Top = 75
End With


' Date XFR Out Label and TextBox
Set lbl_dateXFROut = .Pages(i).Controls.Add("Forms.Label.1", "lbl_dateXFROut_" & i, True)
With lbl_dateXFROut
.Caption = "Estimated departure date:"
.TextAlign = fmTextAlignRight
.Font = "Arial"
.Font.Size = 10
.Font.Bold = False
.Left = 20
.Width = 120
.Top = 100
End With
Set txb_dateXFROut = .Pages(i).Controls.Add("Forms.TextBox.1", "txb_hrsDUE_" & i, True)
With txb_dateXFROut
.Value = "4/16/2019"
.Text = "4/16/2019"
.Font = "Arial"
.Font.Size = 10
.Font.Bold = False
.Left = 150
.Width = 70
.Top = 100
End With

' Hours on XFR Out Label and TextBox
Set lbl_hrsOnXFROut = .Pages(i).Controls.Add("Forms.Label.1", "lbl_hrsOnXFROut_" & i, True)
With lbl_hrsOnXFROut
.Caption = "Desired hours remaining on departure:"
.TextAlign = fmTextAlignLeft
.Font = "Arial"
.Font.Size = 10
.Font.Bold = False
.Left = 20
.Width = 170
.Top = 125
End With
Set txb_hrsOnXFROut = .Pages(i).Controls.Add("Forms.TextBox.1", "txb_hrsDUE_" & i, True)
With txb_hrsOnXFROut
.Value = "35"
.Text = "35"
.Font = "Arial"
.Font.Size = 10
.Font.Bold = False
.Left = 200
.Width = 35
.Top = 125
End With
End With

'Debug
Debug.Print Me.multipage_file_week.Pages(i).Name & ":"
For Each ctrl In Me.multipage_file_week.Pages(i).Controls
Debug.Print " - " & ctrl.Name
Next ctrl

Next i
mPage.Value = 0
Me.Caption = FILE_WEEK_FORM_TITLE

Set tbCollection = New Collection
For Each ctrl In Me.Controls
If TypeOf ctrl Is MSForms.TextBox Then
Set obj = New clsTextBox
Set obj.Control = ctrl
tbCollection.Add obj
End If
Next ctrl
Set obj = Nothing
End Sub

最佳答案

MSForms.Control 定义了 EnterExit 事件:如果您需要处理 TextBox.Change,那么你需要两个 WithEvents 变量:

Private WithEvents TextBoxEvents As MSForms.TextBox
Private WithEvents ControlEvents As MSForms.Control

Public Property Set Control(ByVal tb As Object)
Set TextBoxEvents = tb
Set ControlEvents = tb
End Property

MSForms.Control 也是您访问NameTopLeft 等属性的界面>、可见

提示:切勿手动键入事件处理程序过程签名。从代码 Pane 左上角的下拉列表中选择源接口(interface),然后从右上角的下拉列表中选择要处理的事件;让 VBE 生成具有正确签名的成员。如果您在处理程序中并且左上角的下拉菜单显示“(一般)”,则您不在事件处理程序中。


编辑

虽然上面的代码可以正常编译并且 MSForms.Control 接口(interface)确实公开了我们要处理的事件...

?TypeOf tb Is MSForms.Control
True
?TypeOf tb Is MSForms.TextBox
True

...在幕后进行了一些 COM 黑客攻击;有足够的烟雾和镜子让 VBA 成功编译上面的内容,但是,基本上,你正在寻找 Matrix 中的一个小故障(Rubberduck 的解析器与 MSForms 控件有类似的“不”问题):没有任何明显的方法来获得VBA 将动态控件对象绑定(bind)到它的 MSForms.Control 事件。

关于Excel UserForm 动态 TextBox 控件退出事件,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/55737979/

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