excel - 如何限制用户仅在 Excel 用户表单中具有相同名称模式的所有文本框/文本框中输入数字

Question

我在 Excel 用户窗体中有 60 个文本框。为了限制用户只能为 TextBox1 到 TextBox50 输入数字(十进制)，我需要编写许多与下面相同的代码。
我的问题:
1.我想创建一个类/函数，就好像我不需要为TextBox1到TextBox50编写相同的代码一样。有简单的解决方案吗？
2.如果我想在用户表单的所有文本框中限制用户的数字。

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
If (KeyAscii > 47 And KeyAscii < 58) Or KeyAscii = 46 Then
KeyAscii = KeyAscii
Else
KeyAscii = 0
End If
End Sub

Private Sub TextBox2_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
'same code
End Sub

    .......
    .......

Answer 1

请尝试下一个方法:

插入一个类模块作为事件包装类并将其命名为“TxtBClass”，然后复制其模块中的下一个代码:

Option Explicit

Public WithEvents txtBEvent As MSForms.TextBox


Private Sub txtBEvent_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
  If (KeyAscii > 47 And KeyAscii < 58) Or KeyAscii = 46 Then
      KeyAscii = KeyAscii
  Else
      KeyAscii = 0
  End If
End Sub

将下一个代码粘贴到标准模块中:

Option Explicit

Private txtB() As New TxtBClass

Sub AssignTxtBoxEvent()
  Dim ws As Worksheet, k As Long, oObj As OLEObject
  
  Set ws = ActiveSheet 'use here your necessary sheet
  ReDim txtB(100) 'maximum text boxes to be processed (can be increased)
  
  For Each oObj In ws.OLEObjects
    If TypeName(oObj.Object) = "TextBox" Then
        'exclude the textboxes you need to be excluded from this common event:
        If (oObj.Name <> "TextBoxX") And (oObj.Name <> "TextBoxX") Then
            Set txtB(k).txtBEvent = oObj.Object: k = k + 1
        End If
    End If
   Next
   ReDim Preserve txtB(k - 1)
End Sub

运行以上Sub为了在工作表上的所有文本框中分配事件。它也可以被事件调用(或更好)。使用Worksheet_Activate事件，例如。请复制保留文本框的工作表中的下一个代码，代码模块:

Option Explicit

Private Sub Worksheet_Activate()
    AssignTxtBoxEvent
End Sub

请测试建议的解决方案并发送一些反馈。

已编辑 :
为了对用户窗体中的文本框使用建议的解决方案，请保持相同的类，但将其事件分配给 UserForm_Initialize 内的相关文本框事件:

Option Explicit

Private txtB() As New TxtBClass
Private Sub UserForm_Initialize()
  Dim k As Long, oObj As Control
  ReDim txtB(100) 'maximum text boxes to be processed (it can be increased)
  
  For Each oObj In Me.Controls
    If TypeName(oObj) = "TextBox" Then
        'exclude the textboxes you need to be excluded from this common event:
        If (oObj.Name <> "TextBoxX") And (oObj.Name <> "TextBoxY") Then
            Set txtB(k).txtBEvent = oObj: k = k + 1
        End If
    End If
   Next
   ReDim Preserve txtB(k - 1)
End Sub

请测试它并发送一些反馈。如果今年，将不胜感激...