VBA Excel - 使用相同代码处理多个文本框 [英] VBA Excel - Working with multiple textboxes with the same code

问题描述

所以我是这个领域的新手.我只是想问一下我是否可以尽量减少对下面代码的使用，因为我确实有 13 个具有相同代码的文本框.有没有捷径可以做到这一点?

So I'm new to this area. I just want to ask if how can I minimize the use of the code below since I do have 13 textboxes with the same code. Is there a short way to do this?

这是我正在使用的用户窗体 ->

Here's the UserForm that I'm using ->

这是代码

Private Sub tb_mtb_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    If Not IsNumeric(tb_mtb.Value) Then
        MsgBox "Only numbers allowed!", vbOKOnly + vbCritical, "Title"
        tb_mtb.Value = ""
    End If
End Sub

Private Sub tb_fil_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    If Not IsNumeric(tb_fil.Value) Then
        MsgBox "Only numbers allowed!", vbOKOnly + vbCritical, "Title"
        tb_fil.Value = ""
    End If
End Sub

我尝试了这个解决方案，但我无法让它发挥作用.

I tried this solution but I can't make it work.

推荐答案

正常"避免一遍又一遍地编写相同的事件处理程序代码(或避免为每个类似的控件编写一个存根"处理程序)的方法是使用控件数组".

The "normal" way to avoid writing the same event handler code over and over (or to avoid having to write even a "stub" handler for each like control) is to use a "control array".

这是一个基本示例.

首先是一个小的自定义类clsTxt，可用于从文本框中捕获事件:

First a small custom class clsTxt which can be used to capture events from a text box:

Private WithEvents tb As MSForms.TextBox   'note the "WithEvents"

Sub Init(tbox As Object)
    Set tb = tbox 'assigns the textbox to the "tb" global
End Sub

'Event handler works as in a form (you should get choices for "tb" in the
'  drop-downs at the top of the class module) 
Private Sub tb_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    If KeyAscii >= 48 And KeyAscii <= 57 Then
        Debug.Print tb.Name, "number"
    Else
        Debug.Print tb.Name, "other"
        KeyAscii = 0
    End If
End Sub

然后在您的用户表单中，您可以(例如)抓取框架 frmTest 内的所有文本框，并为每个文本框创建一个 clsTxt 实例，将其存储在 Collection 中(这是全局的，因此在 Activate 事件完成时不会超出范围.

Then in your userform you can (for example) grab all textboxes inside the frame frmTest and create an instance of clsTxt for each one, storing it in a Collection (which is Global and so does not go out of scope when the Activate event completes.

Private colTB As Collection 'holds your class instances
                            ' and keeps them in scope

'This performs the setup
Private Sub UserForm_Activate()
    Dim c As Object
    Set colTB = New Collection
    'loop all controls in the frame
    For Each c In Me.frmTest.Controls
        'look for text boxes
        If TypeName(c) = "TextBox" Then
            Debug.Print "setting up " & c.Name
            colTB.Add TbHandler(c) ' create and store an instance of your class
        End If
    Next c
End Sub

' "factory" method
Private Function TbHandler(tb As Object) As clsTxt
    Dim o As New clsTxt
    o.Init tb
    Set TbHandler = o
End Function

一旦设置完成，每个已连接"的事件就会发生.文本框由类实例处理(如果您需要管理不同的事情，例如 Change 等，您可以向类添加更多事件)，并且添加到框架中的任何新文本框都将自动得到处理，而无需为它写一个处理程序.

Once the setup is complete then events for each "wired up" textbox are handled by the class instances (you can add more events to the class if you need to manage different things like Change etc) and any new textbox added to the frame will automatically get handled without the need to write a handler for it.

这篇关于VBA Excel - 使用相同代码处理多个文本框的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！