Pass data between event handlers in collection

Question

I have a collection of event handlers of extended textboxes that handle the click event. On a click of any textbox in a column the event handler will unlock every textbox in that column by calling its form name and then sets a variable with an identifing variable in the active handler. Is there a way to set this identify variable inother handler objects from inside the handler active handler. Would i have to pass the collection by reference to each handler object to accomplish this or is there a way that I can set and event to pass the data between the objects?

Here is the code to my event handler object

Option Compare Database
Option Explicit
Private WithEvents TC_txtbox As TextBox
Private m_Day As String
' Set the textbox so that its events will be handled
Public Property Set TextBox(ByVal m_tcTxtBox As access.TextBox)
    Set TC_txtbox = m_tcTxtBox
    TC_txtbox.OnClick = "[Event Procedure]"
    TC_txtbox.Enabled = True
    TC_txtbox.Locked = True
    TC_txtbox.BackColor = 16777215
End Property
' Handle and onClick event of the textboxes
Private Sub TC_txtbox_Click()
    ' Find out the controls that where click
    If Not m_Day = "" Then
        ' Highlight text inside text box
        Form_TimeCard.ActiveControl.SelStart = 0
        Form_TimeCard.ActiveControl.SelLength = Len(Form_TimeCard.ActiveControl.Text)
    Else ' Day has not been set enable text boxes
        m_Day = Form_TimeCard.ActiveControl.Tag

        Dim ctl As Control
        For Each ctl In Form_TimeCard.Controls
            If m_Day = ctl.Tag Then
                ctl.Locked = False
                ctl.BackColor = 65535
            End If
        Next ctl

        ' Save button is now visible
        Form_TimeCard.but_save.Visible = True
        Form_TimeCard.but_save.Top = 3540
        Select Case m_Day
            Case "Sunday"
                Form_TimeCard.but_save.Left = 1440
            Case "Monday"
                Form_TimeCard.but_save.Left = 2640
            Case "Tuesday"
                Form_TimeCard.but_save.Left = 3780
            Case "Wednesday"
                Form_TimeCard.but_save.Left = 4920
            Case "Thursday"
                Form_TimeCard.but_save.Left = 6060
            Case "Friday"
                Form_TimeCard.but_save.Left = 7200
            Case "Saturday"
                Form_TimeCard.but_save.Left = 8340
        End Select
    End If
End Sub
' Get control name
Public Property Get Name() As String
    Name = TC_txtbox.Name
End Property
' Get day of the week
Public Property Get Tag() As String
    Tag = TC_txtbox.Tag
End Property
' Save button was clicked
Public Sub Save()
    Dim ctl As Control
    Debug.Print "Day = " & m_Day
    For Each ctl In Form_TimeCard.Controls
        If m_Day = ctl.Tag Then
            Debug.Print ctl.Name & "=" & ctl.Tag
            ctl.BackColor = 16777215
            ctl.Locked = True
        End If
    Next ctl
    m_Day = ""
End Sub
' Get the data from the textbox
Public Function Value() As Variant
    Value = TC_txtbox.Value
End Function

Here is my main code

Option Compare Database
Option Explicit
' Global Variables
Private clk_inout As Boolean
Private settings As Dictionary
Private weekDict As Dictionary
Private weekOf As Variant
Private curDay As Variant
Private txtBxCollection As Collection

Private Sub but_save_Click()
    Dim ctl As TextBoxEventHandler
    Dim Day As String
    Dim dayDetail(1 To 7) As Variant
    Dim x As Integer
    x = 1

    Select Case Me.ActiveControl.Left
        Case 1440
            Day = "Sunday"
        Case 2640
            Day = "Monday"
        Case 3780
            Day = "Tuesday"
        Case 4920
            Day = "Wednesday"
        Case 6060
            Day = "Thursday"
        Case 7200
            Day = "Friday"
        Case 8340
            Day = "Saturday"
    End Select

    For Each ctl In txtBxCollection
        If ctl.Tag = Day Then
            Debug.Print ctl.Name
            dayDetail(x) = ctl.Value
            x = x + 1
            ctl.Save
        End If
    Next ctl
End Sub

' Event Handler for when the form opens
Private Sub Form_Open(Cancel As Integer)
    ' Configure varaibles
    Me.TimerInterval = 60000 ' 60 sec Interval
    weekOf = getFirstDayofWeek(Date)
    curDay = Date
    Set weekDict = New Dictionary
    Set settings = New Dictionary
    Set txtBxCollection = New Collection

    ' Load Time Card Data
    Call initSettings
    Debug.Print "Week Dict Count=" & weekDict.Count
    'Debug.Print "Work Day Goal " & settings.Item("Work_day_goal_hrs")
    Call initDict
    Call initTextBoxEventHandler
    Call loadDates(Date)
    Call clearDay
    Call selectDay(Date)
    Call loadWeeksData(weekOf)

    Dim ctl As Control
    Set ctl = weekDict.Item(Weekday(curDay)).Item("In")

    If IsDate(ctl.Value) And (Not ctl.Value = "") Then
        Me.but_clk_inout.Caption = "Clock Out"
        Me.but_lunch.Visible = True
        clk_inout = False
    Else
        Me.but_clk_inout.Caption = "Clock In"
        Me.but_lunch.Visible = False
        clk_inout = True
    End If
End Sub
' Regular task to be performed
Private Sub Form_Timer()
    If Not (curDay = Date) Then
        Call clearDay
        Call selectDay(Date)
    End If
    If Not (weekOf = getFirstDayofWeek(Date)) Then
        weekOf = getFirstDayofWeek(Date)
        Call loadDates(Date)
        Call loadWeeksData(Date)
    End If
    'Debug.Print "sal=" & settings.Item(1)
End Sub
' On Close of Form Insert/Update Time Card
Private Sub Form_Close()
    ' dump time card into database
    Set weekDict = Nothing
    Set settings = Nothing
End Sub
' Code for minimizing the window to the system tray
Private Sub Form_Load()
'    Debug.Print "HWND: " & Hex(GetWindowHWnd("OMain"))
'    Debug.Print "MENU: " & Hex(GetWindowSysMenu("OMain"))
'    AddToTray GetWindowHWnd("OMain"), GetWindowSysMenu("OMain")
'    SetTrayTip "Hollo World"
'    CreatePopup
'    ShowWindow GetWindowHWnd("OMain"), SW_HIDE
End Sub
Private Sub Form_Unload(Cancel As Integer)
'    RemoveFromTray GetWindowHWnd("OMain")
'    ShowWindow GetWindowHWnd("OMain"), SW_SHOW Or SW_RESTORE
'    DestroyMenu hMenu
End Sub
' Pop up form for configuring your settings
Private Sub but_settings_Click()
On Error GoTo EH
    Dim results As Variant
    Dim args As String
    args = "Settings|" & settings.Item("Salary") & "|" & _
                         settings.Item("Dental") & "|" & _
                         settings.Item("401k") & "|" & _
                         settings.Item("Federal") & "|" & _
                         settings.Item("Social_Security") & "|" & _
                         settings.Item("CT_tax") & "|" & _
                         settings.Item("Work_day_goal_hrs")

    results = settingsInputBox("Settings", settings)

    If Not results = vbNullString Then
        Debug.Print "Salary " & results(0)
        Debug.Print "Dental " & results(1)
        Debug.Print "Medical " & results(2)
        Debug.Print "401K " & results(3)
        Debug.Print "Federal Tax " & results(4)
    Else
        Debug.Print "Cancel button clicked"
    End If
EndIt:
    Exit Sub
EH:
    MsgBox Err.Number & ": " & Err.Description
    Resume EndIt
End Sub
' End system tray minimize code
' Clock inout button has been pressed
Private Sub but_clk_inout_Click()
    Dim inputTime As Variant
    Dim goal As Integer

    Select Case Me.but_clk_inout.Caption
        Case "Clock In"
            inputTime = InputBox("Enter Time", "Clock In")
            If (Not inputTime = "") And IsDate(inputTime) Then
                If Not dateExists(curDay) Then
                    'goal = settings.Item("Work_day_goal_hrs") ' Problem settings dictionary is no longer set
                    Me.txt_outGoal.Value = DateAdd("h", settings.Item("Work_day_goal_hrs"), "#" & inputTime & "#")
                    Me.but_clk_inout.Caption = "Clock Out"
                    Me.but_lunch.Visible = True
                    Me.lbl_outGoal.Visible = True
                    Me.txt_outGoal.Visible = True
                    Call logClkTime(True, inputTime)
                    clk_inout = True
                Else
                    MsgBox ("You already clocked in")
                    Exit Sub
                End If
            Else
                MsgBox ("Please enter a valid time")
                Exit Sub
            End If
        Case "Clock Out"
            inputTime = InputBox("Enter Time", "Clock Out")
            If Not inputTime = "" And IsDate(inputTime) Then
                Me.but_clk_inout.Caption = "Clock In"
                Me.but_lunch.Visible = False
                Call logClkTime(False, inputTime)
                clk_inout = False
            Else
                Exit Sub
            End If
        Case "Reset"
            If clk_inout Then
                Me.but_clk_inout.Caption = "Clock In"
            Else
                Me.but_clk_inout.Caption = "Clock Out"
            End If
            Call loadDates(Date)
            Call clearDay
            Call selectDay(Date)
            Call loadWeeksData(weekOf)
    End Select
End Sub
' Close Form
Private Sub but_close_Click()
    DoCmd.Close acForm, "TimeCard", acSaveYes
End Sub
' Find a time card already
Private Sub but_search_Click()
    Dim strSQL As String
    Dim dateInput As Variant
    Dim rs As DAO.Recordset
    Dim dbs As DAO.Database
    Set dbs = CurrentDb()
    dateInput = Format(InputBox("Date XX/XX/XXXX", "Search by week"), "mm/dd/yyyy")

    If dateInput = "" Then
        MsgBox ("Please enter a valid date")
        Exit Sub
    ElseIf IsDate(dateInput) Then
        strSQL = "SELECT Week_Of FROM Time_Clock " & _
                 "WHERE (Labor_date=#" & dateInput & "# " & _
                 "OR Week_Of=#" & dateInput & "#);"

        Set rs = dbs.OpenRecordset(strSQL)

        If Not (rs.BOF And rs.EOF) Then
            rs.MoveLast
            rs.MoveFirst
            Dim pastWeek As Variant
            pastWeek = rs![Week_Of]
            Call loadDates(pastWeek)
            Call loadWeeksData(pastWeek)
            Call clearDay
            Me.but_clk_inout.Caption = "Reset"
        Else
            MsgBox ("Date does not exist")
        End If

        rs.Close
    Else
        MsgBox ("Invalid Date")
    End If
End Sub
' Data into week table
Public Sub loadWeeksData(wkDate As Variant)
    Dim weeklyHrs As Single
    Dim strSQL As String
    Dim dbs As DAO.Database
    Dim rs As Recordset
    Set dbs = CurrentDb()
    Dim ctl As Control

    weeklyHrs = 0
    strSQL = "SELECT * FROM Time_Clock " & _
             "WHERE Week_Of=" & Format("#" & wkDate & "#", "mm/dd/yyyy") & ";"
    Set rs = dbs.OpenRecordset(strSQL)

    If Not (rs.BOF And rs.EOF) Then
        Call clearTimeChart
        Do While Not rs.EOF
            weekDict(Weekday(rs![Labor_date]))("In").Value = Format(rs![Clock_In], "hh:mm ampm")
            'Set ctl = weekDict(Weekday(rs![Labor_date]))("In")
            'ctl.Value = Format(rs![Clock_In], "hh:mm ampm")
            weekDict(Weekday(rs![Labor_date]))("In").Value = Format(rs![Clock_In], "hh:mm ampm")
            weekDict(Weekday(rs![Labor_date]))("Lunch_Out").Value = rs![Lunch_out]
            weekDict(Weekday(rs![Labor_date]))("Lunch_in").Value = rs![Lunch_in]
            weekDict(Weekday(rs![Labor_date]))("Lunch_total").Value = rs![Lunch_total]

            If rs![Clock_Out] = #12:01:00 AM# And Not rs![Clock_In] = #12:00:00 AM# Then
                weekDict(Weekday(rs![Labor_date]))("Out").Value = rs![Clock_Out]
            Else
                weekDict(Weekday(rs![Labor_date]))("Out").Value = ""
            End If

            Debug.Print "(" & rs![Labor_date] & ") IN/OUT "; rs![Clock_In] & " / " & rs![Clock_Out]

            weekDict(Weekday(rs![Labor_date]))("Day_total").Value = rs![Total_Hours_Worked]
            weekDict(Weekday(rs![Labor_date]))("Paid_OT").Value = rs![Paid_OT]
            weeklyHrs = weeklyHrs + rs![Total_Hours_Worked]
            rs.MoveNext
        Loop
        Debug.Print "Weekly Hours " & weeklyHrs
    End If
End Sub
' Take Lunch Button Pressed
Private Sub but_lunch_Click()
    Dim strSQL As String
    If dateExists(curDay) Then
        strSQL = "UPDATE Time_Clock " & _
                 "SET Lunch_out=#11:30#, Lunch_in=12:00, Lunch_total=30 " & _
                 "WHERE Labor_date=#" & curDay & "#;"
        weekDict(Weekday(curDay))("Lunch_Out").Value = "11:30 AM"
        weekDict(Weekday(curDay))("Lunch_in").Value = "12:00 PM"
        weekDict(Weekday(curDay))("Lunch_total").Value = "30 mins"
    Else
        MsgBox ("Please clock in first before you take your lunch")
    End If
End Sub
' Load dates in each column
Public Sub loadDates(today As Variant)
    Me.txt_week_begin.Value = today - (Weekday(today) - 1)
    Me.txt_Sun_date.Value = today - (Weekday(today) - 1)
    Me.txt_M_date.Value = today - (Weekday(today) - 2)
    Me.txt_T_date.Value = today - (Weekday(today) - 3)
    Me.txt_W_date.Value = today - (Weekday(today) - 4)
    Me.txt_R_date.Value = today - (Weekday(today) - 5)
    Me.txt_F_date.Value = today - (Weekday(today) - 6)
    Me.txt_Sat_date.Value = today - (Weekday(today) - 7)
End Sub
Public Sub loadSettings()

End Sub
' Insert clock time into database
Public Sub logClkTime(inout As Boolean, clkTime As Variant)
    Dim strSQL As String
    Dim dbs As DAO.Database
    Dim rs As DAO.Recordset
    Set dbs = CurrentDb()

    Select Case inout
        Case True  ' clock in
            strSQL = "INSERT INTO Time_Clock (Week_Of, Labor_date, Clock_In) " & _
                     "VALUES (#" & weekOf & "#, #" & curDay & "#, #" & Format(clkTime, "hh:mm") & "#);"
        Case False ' clock out
            Dim worked, worked_strt As Single
            Dim clkOut As Variant
            Dim clkIn As Variant
            clkOut = clkTime
            clkIn = weekDict.Item(Weekday(curDay)).Item("In").Value
            worked = DateDiff("h", clkIn, clkOut) + (DateDiff("n", clkIn, clkOut) - _
                    (DateDiff("h", clkIn, clkOut) * 60)) / 60
            worked_strt = Floor(worked)

            strSQL = "UPDATE Time_Clock (Clock_Out, Total_Hours_Worked, Straight_hours) " & _
                     "SET Clock_Out=#" & Format(clkTime, "hh:mm") & "#, Total_Hours_Worked=" & worked & ", Straight_hours=" & worked_strt & " " & _
                     "WHERE Labor_date=#" & curDay & "#;"
    End Select
    Debug.Print strSQL
    dbs.Execute (strSQL)
    dbs.Close
End Sub
' Has this date been logged into the database
Public Function dateExists(clkdDate As Variant) As Boolean
    Dim dbs As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSQL As String
    Set dbs = CurrentDb()

    If IsDate(clkdDate) Then
        strSQL = "SELECT ID FROM Time_Clock WHERE Labor_date=#" & clkdDate & "#;"
        Set rs = dbs.OpenRecordset(strSQL)
        If Not (rs.BOF And rs.EOF) Then
            If rs.RecordCount <> 0 Then
                Debug.Print "Date Exists"
                dateExists = True
            End If
        Else
            Debug.Print "Date Does Not Exists"
            dateExists = False
        End If
        rs.Close
    End If
    dbs.Close
End Function
' Draw a red box around the current day
Public Sub selectDay(today As Variant)
    Select Case Weekday(today)
        Case 1
            Me.rec_Sun.Visible = True
        Case 2
            Me.rec_M.Visible = True
        Case 3
            Me.rec_T.Visible = True
        Case 4
            Me.rec_W.Visible = True
        Case 5
            Me.rec_R.Visible = True
        Case 6
            Me.rec_F.Visible = True
        Case 7
            Me.rec_Sat.Visible = True
    End Select
End Sub
' Clear the current day selection box
Public Sub clearDay()
    Me.rec_Sun.Visible = False
    Me.rec_Sat.Visible = False
    Me.rec_M.Visible = False
    Me.rec_T.Visible = False
    Me.rec_W.Visible = False
    Me.rec_R.Visible = False
    Me.rec_F.Visible = False
End Sub
' Clear data from time Chart
Public Sub clearTimeChart()
    Dim arr As Variant
    arr = Array("In", "Lunch_Out", "Lunch_in", "Lunch_total", "Out", "Day_total", "Paid_OT")
    Dim x, y As Integer
    For x = 1 To 7
        For y = 0 To 6
            weekDict.Item(x).Item(arr(y)).Value = ""
        Next
    Next
End Sub
' Initialize the weekly time chart control dictionary
Public Sub initDict()
    Dim dayDict

    Set dayDict = CreateObject("Scripting.Dictionary")
    dayDict.Add "In", Me.txt_Sun_in
    dayDict.Add "Lunch_Out", Me.txt_Sun_LO
    dayDict.Add "Lunch_in", Me.txt_Sun_LI
    dayDict.Add "Lunch_total", Me.txt_Sun_LT
    dayDict.Add "Out", Me.txt_Sun_out
    dayDict.Add "Day_total", Me.txt_Sun_out
    dayDict.Add "Paid_OT", Me.txt_Sun_OT
    weekDict.Add 1, dayDict
    Set dayDict = Nothing

    Set dayDict = CreateObject("Scripting.Dictionary")
    dayDict.Add "In", Me.txt_M_in
    dayDict.Add "Lunch_Out", Me.txt_M_LO
    dayDict.Add "Lunch_in", Me.txt_M_LI
    dayDict.Add "Lunch_total", Me.txt_M_LT
    dayDict.Add "Out", Me.txt_M_out
    dayDict.Add "Day_total", Me.txt_M_out
    dayDict.Add "Paid_OT", Me.txt_M_OT
    weekDict.Add 2, dayDict
    Set dayDict = Nothing

    Set dayDict = CreateObject("Scripting.Dictionary")
    dayDict.Add "In", Me.txt_T_in
    dayDict.Add "Lunch_Out", Me.txt_T_LO
    dayDict.Add "Lunch_in", Me.txt_T_LI
    dayDict.Add "Lunch_total", Me.txt_T_LT
    dayDict.Add "Out", Me.txt_T_out
    dayDict.Add "Day_total", Me.txt_T_out
    dayDict.Add "Paid_OT", Me.txt_T_OT
    weekDict.Add 3, dayDict
    Set dayDict = Nothing

    Set dayDict = CreateObject("Scripting.Dictionary")
    dayDict.Add "In", Me.txt_W_in
    dayDict.Add "Lunch_Out", Me.txt_W_LO
    dayDict.Add "Lunch_in", Me.txt_W_LI
    dayDict.Add "Lunch_total", Me.txt_W_LT
    dayDict.Add "Out", Me.txt_W_out
    dayDict.Add "Day_total", Me.txt_W_out
    dayDict.Add "Paid_OT", Me.txt_W_OT
    weekDict.Add 4, dayDict
    Set dayDict = Nothing

    Set dayDict = CreateObject("Scripting.Dictionary")
    dayDict.Add "In", Me.txt_R_in
    dayDict.Add "Lunch_Out", Me.txt_R_LO
    dayDict.Add "Lunch_in", Me.txt_R_LI
    dayDict.Add "Lunch_total", Me.txt_R_LT
    dayDict.Add "Out", Me.txt_R_out
    dayDict.Add "Day_total", Me.txt_R_out
    dayDict.Add "Paid_OT", Me.txt_R_OT
    weekDict.Add 5, dayDict
    Set dayDict = Nothing

    Set dayDict = CreateObject("Scripting.Dictionary")
    dayDict.Add "In", Me.txt_F_in
    dayDict.Add "Lunch_Out", Me.txt_F_LO
    dayDict.Add "Lunch_in", Me.txt_F_LI
    dayDict.Add "Lunch_total", Me.txt_F_LT
    dayDict.Add "Out", Me.txt_F_out
    dayDict.Add "Day_total", Me.txt_F_out
    dayDict.Add "Paid_OT", Me.txt_F_OT
    weekDict.Add 6, dayDict
    Set dayDict = Nothing

    Set dayDict = CreateObject("Scripting.Dictionary")
    dayDict.Add "In", Me.txt_Sat_in
    dayDict.Add "Lunch_Out", Me.txt_Sat_LO
    dayDict.Add "Lunch_in", Me.txt_Sat_LI
    dayDict.Add "Lunch_total", Me.txt_Sat_LT
    dayDict.Add "Out", Me.txt_Sat_out
    dayDict.Add "Day_total", Me.txt_Sat_out
    dayDict.Add "Paid_OT", Me.txt_Sat_OT
    weekDict.Add 7, dayDict
    Set dayDict = Nothing
End Sub
' Load settings into dictionary
Public Sub initSettings()
    Dim strSQL As String
    Dim dbs As DAO.Database
    Dim rs As DAO.Recordset
    Set dbs = CurrentDb()

    strSQL = "SELECT * FROM Settings WHERE ID=1;"
    Set rs = dbs.OpenRecordset(strSQL)
    If Not (rs.BOF And rs.EOF) Then
        If rs.RecordCount <> 0 Then
            settings.Add 1, rs![Salary]
            settings.Add 2, rs![Dental]
            settings.Add 3, rs![401K]
            settings.Add 4, rs![Federal]
            settings.Add 5, rs![Social_Security]
            settings.Add 6, rs![Medicare]
            settings.Add 7, rs![CT_tax]
            settings.Add 8, rs![Work_day_goal_hrs]
        End If
    Else
        Debug.Print "No Settings!"
    End If
End Sub
' Get the control by object name
Public Function getControl(ByVal ctlName As String) As Control
    Dim ctl As Control
    For Each ctl In Me.Controls
        If ctl.Name = ctlName Then
            Set getControl = ctl
            Exit Function
        End If
    Next ctl
End Function

Public Sub initTextBoxEventHandler()
    Dim eventHandler As TextBoxEventHandler
    Set eventHandler = New TextBoxEventHandler

    Set eventHandler.TextBox = Me.txt_Sun_in
    txtBxCollection.Add eventHandler
    Set eventHandler = Nothing
    Set eventHandler = New TextBoxEventHandler
    Set eventHandler.TextBox = Me.txt_Sun_LO
    txtBxCollection.Add eventHandler
    Set eventHandler = Nothing
    Set eventHandler = New TextBoxEventHandler
    Set eventHandler.TextBox = Me.txt_Sun_LI
    txtBxCollection.Add eventHandler
    Set eventHandler = Nothing
    Set eventHandler = New TextBoxEventHandler
    Set eventHandler.TextBox = Me.txt_Sun_out
    txtBxCollection.Add eventHandler
    Set eventHandler = Nothing
    Set eventHandler = New TextBoxEventHandler

    Set eventHandler.TextBox = Me.txt_M_in
    txtBxCollection.Add eventHandler
    Set eventHandler = Nothing
    Set eventHandler = New TextBoxEventHandler
    Set eventHandler.TextBox = Me.txt_M_LO
    txtBxCollection.Add eventHandler
    Set eventHandler = Nothing
    Set eventHandler = New TextBoxEventHandler
    Set eventHandler.TextBox = Me.txt_M_LI
    txtBxCollection.Add eventHandler
    Set eventHandler = Nothing
    Set eventHandler = New TextBoxEventHandler
    Set eventHandler.TextBox = Me.txt_M_out
    txtBxCollection.Add eventHandler
    Set eventHandler = Nothing
    Set eventHandler = New TextBoxEventHandler

    Set eventHandler.TextBox = Me.txt_T_in
    txtBxCollection.Add eventHandler
    Set eventHandler = Nothing
    Set eventHandler = New TextBoxEventHandler
    Set eventHandler.TextBox = Me.txt_T_LO
    txtBxCollection.Add eventHandler
    Set eventHandler = Nothing
    Set eventHandler = New TextBoxEventHandler
    Set eventHandler.TextBox = Me.txt_T_LI
    txtBxCollection.Add eventHandler
    Set eventHandler = Nothing
    Set eventHandler = New TextBoxEventHandler
    Set eventHandler.TextBox = Me.txt_T_out
    txtBxCollection.Add eventHandler
    Set eventHandler = Nothing
    Set eventHandler = New TextBoxEventHandler

    Set eventHandler.TextBox = Me.txt_W_in
    txtBxCollection.Add eventHandler
    Set eventHandler = Nothing
    Set eventHandler = New TextBoxEventHandler
    Set eventHandler.TextBox = Me.txt_W_LO
    txtBxCollection.Add eventHandler
    Set eventHandler = Nothing
    Set eventHandler = New TextBoxEventHandler
    Set eventHandler.TextBox = Me.txt_W_LI
    txtBxCollection.Add eventHandler
    Set eventHandler = Nothing
    Set eventHandler = New TextBoxEventHandler
    Set eventHandler.TextBox = Me.txt_W_out
    txtBxCollection.Add eventHandler
    Set eventHandler = Nothing
    Set eventHandler = New TextBoxEventHandler

    Set eventHandler.TextBox = Me.txt_R_in
    txtBxCollection.Add eventHandler
    Set eventHandler = Nothing
    Set eventHandler = New TextBoxEventHandler
    Set eventHandler.TextBox = Me.txt_R_LO
    txtBxCollection.Add eventHandler
    Set eventHandler = Nothing
    Set eventHandler = New TextBoxEventHandler
    Set eventHandler.TextBox = Me.txt_R_LI
    txtBxCollection.Add eventHandler
    Set eventHandler = Nothing
    Set eventHandler = New TextBoxEventHandler
    Set eventHandler.TextBox = Me.txt_R_out
    txtBxCollection.Add eventHandler
    Set eventHandler = Nothing
    Set eventHandler = New TextBoxEventHandler

    Set eventHandler.TextBox = Me.txt_F_in
    txtBxCollection.Add eventHandler
    Set eventHandler = Nothing
    Set eventHandler = New TextBoxEventHandler
    Set eventHandler.TextBox = Me.txt_F_LO
    txtBxCollection.Add eventHandler
    Set eventHandler = Nothing
    Set eventHandler = New TextBoxEventHandler
    Set eventHandler.TextBox = Me.txt_F_LI
    txtBxCollection.Add eventHandler
    Set eventHandler = Nothing
    Set eventHandler = New TextBoxEventHandler
    Set eventHandler.TextBox = Me.txt_F_out
    txtBxCollection.Add eventHandler
    Set eventHandler = Nothing
    Set eventHandler = New TextBoxEventHandler

    Set eventHandler.TextBox = Me.txt_Sat_in
    txtBxCollection.Add eventHandler
    Set eventHandler = Nothing
    Set eventHandler = New TextBoxEventHandler
    Set eventHandler.TextBox = Me.txt_Sat_LO
    txtBxCollection.Add eventHandler
    Set eventHandler = Nothing
    Set eventHandler = New TextBoxEventHandler
    Set eventHandler.TextBox = Me.txt_Sat_LI
    txtBxCollection.Add eventHandler
    Set eventHandler = Nothing
    Set eventHandler = New TextBoxEventHandler
    Set eventHandler.TextBox = Me.txt_Sat_out
    txtBxCollection.Add eventHandler
    Set eventHandler = Nothing
    Set eventHandler = New TextBoxEventHandler
    Set eventHandler = Nothing

End Sub

Answer 1

Hallo,

It was nice to see your all project - I agree with MarkJ - who said that - in VBA there is no possibility to use events in arrays and also in collection - with collections and events I don't have too much experience - but I read about it - about arrays - I spent whole night - and discovered that VBA is not C++ :( . Try to show all project. Norbert