How can I loop this in userform by change?

Question

I have this nifty string of code that does what I need in one single textbox at a time in a userform... Is there a way to loop it by value change through 24 different text boxes?

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim DateStr As String
With Me.TextBox1
Select Case Len(.Value)
Case 4    ' e.g., 9298 = 2-Sep-1998
    DateStr = Left(.Value, 1) & "/" & _
              Mid(.Value, 2, 1) & "/" & Right(.Value, 2)
Case 5    ' e.g., 11298 = 12-Jan-1998 NOT 2-Nov-1998
    DateStr = Left(.Value, 1) & "/" & _
              Mid(.Value, 2, 2) & "/" & Right(.Value, 2)
Case 6    ' e.g., 090298 = 2-Sep-1998
    DateStr = Left(.Value, 2) & "/" & _
              Mid(.Value, 3, 2) & "/" & Right(.Value, 2)
Case 7    ' e.g., 1231998 = 23-Jan-1998 NOT 3-Dec-1998
    DateStr = Left(.Value, 1) & "/" & _
              Mid(.Value, 2, 2) & "/" & Right(.Value, 4)
Case 8    ' e.g., 09021998 = 2-Sep-1998
    DateStr = Left(.Value, 2) & "/" & _
              Mid(.Value, 3, 2) & "/" & Right(.Value, 4)
Case Else
    Exit Sub
End Select
.Value = DateStr
End With
End Sub

Answer 1

in your code behind your form: (doesnot work on a mac)

Private AllControls() As New CatchEvents

Private Sub UserForm_Initialize()
Dim j As Long
ReDim AllControls(Controls.Count - 1)
    For j = 0 To Controls.Count - 1
    AllControls(j).Item = Controls(j)
    Next
End Sub

Private Sub UserForm_Terminate()
Dim j As Long
  For j = LBound(AllControls) To UBound(AllControls)
          AllControls(j).Clear
      Next j
      Erase AllControls
End Sub

and then copy the code below to notepad and save it as whatever**.cls** After saving, import this file (class-module) to your VBA project. You've now "hooked" the exit-events of all controls and act on TextBox-exit: (this code won't run when pasted directly to the VBA-project because of the Attribute)

VERSION 1.0 CLASS
BEGIN
  MultiUse = -1  'True
END
Attribute VB_Name = "CatchEvents"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = False
Private Type GUID
      Data1 As Long
      Data2 As Integer
      Data3 As Integer
      Data4(0 To 7) As Byte
End Type

#If VBA7 And Win64 Then
      Private Declare PtrSafe Function ConnectToConnectionPoint Lib "shlwapi" Alias "#168" (ByVal punk As stdole.IUnknown, _
              ByRef riidEvent As GUID, ByVal fConnect As Long, ByVal punkTarget As stdole.IUnknown, ByRef pdwCookie As Long, _
              Optional ByVal ppcpOut As LongPtr) As Long
#Else
     Private Declare Function ConnectToConnectionPoint Lib "shlwapi" Alias "#168" (ByVal punk As stdole.IUnknown, ByRef riidEvent As GUID, _
              ByVal fConnect As Long, ByVal punkTarget As stdole.IUnknown, ByRef pdwCookie As Long, Optional ByVal ppcpOut As Long) As Long
#End If

Private EventGuide As GUID
Private Ck As Long
Private ctl As Object
Private CustomProp As String

Public Sub ConnectAllEvents(ByVal Connect As Boolean)
      With EventGuide
          .Data1 = &H20400
          .Data4(0) = &HC0
          .Data4(7) = &H46
      End With
      ConnectToConnectionPoint Me, EventGuide, Connect, ctl, Ck, 0&
End Sub

Public Property Let Item(Ctrl As Object)
      Set ctl = Ctrl
      Call ConnectAllEvents(True)
End Property

Public Sub Clear()
      If (Ck <> 0) Then Call ConnectAllEvents(False)
      Set ctl = Nothing
End Sub

Public Sub CtlExit(ByVal Cancel As MSForms.ReturnBoolean)
Attribute CtlExit.VB_UserMemId = -2147384829
Dim DateStr As String
If TypeName(ctl) = "TextBox" Then 'every exit event is catched, only use TextBox
With ctl
    Select Case Len(.Value)
        Case 4    ' e.g., 9298 = 2-Sep-1998
            DateStr = Left(.Value, 1) & "/" & _
            Mid(.Value, 2, 1) & "/" & Right(.Value, 2)
        Case 5    ' e.g., 11298 = 12-Jan-1998 NOT 2-Nov-1998
            DateStr = Left(.Value, 1) & "/" & _
            Mid(.Value, 2, 2) & "/" & Right(.Value, 2)
        Case 6    ' e.g., 090298 = 2-Sep-1998
            DateStr = Left(.Value, 2) & "/" & _
            Mid(.Value, 3, 2) & "/" & Right(.Value, 2)
        Case 7    ' e.g., 1231998 = 23-Jan-1998 NOT 3-Dec-1998
            DateStr = Left(.Value, 1) & "/" & _
            Mid(.Value, 2, 2) & "/" & Right(.Value, 4)
        Case 8    ' e.g., 09021998 = 2-Sep-1998
            DateStr = Left(.Value, 2) & "/" & _
            Mid(.Value, 3, 2) & "/" & Right(.Value, 4)
        Case Else
            Exit Sub
    End Select
    .Value = DateStr
End With
End If
End Sub