同一用户表单上的多个文本框的相同宏 excel vba [英] Same macro for multiple textboxes on the same userform excel vba

问题描述

我目前正在制作一个用户表单，其中有多个文本框.所以现在我总共有 15 个文本框，每个文本框应该只包含数值.我现在为每个 TextBox 得到的代码是:

I am currently making an userform in which I got multiple textboxes. So for now I got a total of 15 textboxes and each of them should only contain numerical values. The code I got now for each TextBox is:

    Private Sub TextBox1_Change()
     If TypeName(Me.ActiveControl) = "TextBox" Then

            With Me.ActiveControl

                If Not IsNumeric(.Value) And .Value <> vbNullString Then

                    MsgBox "Sorry, only numbers allowed"

                    .Value = vbNullString

                End If

            End With

        End If
    End Sub

    Private Sub TextBox2_Change()
     If TypeName(Me.ActiveControl) = "TextBox" Then

            With Me.ActiveControl

                If Not IsNumeric(.Value) And .Value <> vbNullString Then

                    MsgBox "Sorry, only numbers allowed"

                    .Value = vbNullString

                End If

            End With

        End If
    End Sub
.
.
.

Private Sub TextBox15_Change()
 If TypeName(Me.ActiveControl) = "TextBox" Then

        With Me.ActiveControl

            If Not IsNumeric(.Value) And .Value <> vbNullString Then

                MsgBox "Sorry, only numbers allowed"

                .Value = vbNullString

            End If

        End With

    End If
End Sub

我现在的做法有点草率，因为我为每个文本框复制了相同的代码.我的问题是因此是否可以合并这些例程，以便我只需要一个代码来处理所有文本框?

The way I am doing it now feels kind of sloppy since I am copying the same code for each textbox. My question is therefor whether it is possible to consolidate these routines so that I will only require one code for all off the TextBoxes?

在此致以亲切的问候和感谢，

Kind regards and thanks in advance,

莫里斯

推荐答案

简单例子:

向您的项目添加一个新的类模块并将其重命名为 NumericTextbox.将此代码粘贴到其中:

Add a new class module to your project and rename it NumericTextbox. Paste this code into it:

Option Explicit
Private WithEvents tb As MSForms.TextBox
Public Property Set TextControl(t As MSForms.TextBox)
    Set tb = t
End Property
Private Sub tb_Change()

    With tb

        If Not IsNumeric(.Value) And .Value <> vbNullString Then

            MsgBox "Sorry, only numbers allowed"

            .Value = vbNullString

        End If

    End With

End Sub

现在在您的用户表单中，添加以下代码:

Now in your userform, add this code:

Option Explicit
Private colTBs                As Collection
Private Sub UserForm_Initialize()
    Dim ctl                   As MSForms.Control
    Dim oHandler              As NumericTextbox
    Set colTBs = New Collection

    For Each ctl In Me.Controls
        If TypeOf ctl Is MSForms.TextBox Then
            Set oHandler = New NumericTextbox
            Set oHandler.TextControl = ctl
            colTBs.Add oHandler
        End If
    Next ctl
End Sub

就这样.

这篇关于同一用户表单上的多个文本框的相同宏 excel vba的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！