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屋！