global click event handler (WithEvents)

Question

I am trying to create a class module that will act as a global handler for when ever someone clicks one of the sixty textboxes I have in my form. Th textboxes represent a timecard for the week displaying information as clock in, clock out, lunch start,end,duration, total daily hours under each fo the seven days of the week. When someone clicks anyone of the boxes under a day all the boxes will unlock and enable so that the user can edit the information in them.

After scouring the web for a solutio of a global click event I found that I could create a class module that would handle the event without create a click event for every single text box that calls a seperate function to handle the event. The problem I am having is that my class module doesn't seem to be handling my event and was wondering if someone could suggest a solution to my problem. FYI, All my textboxes and locked and disabled to prevent data corruption. Below is my code:

''# Class module    
    Option Compare Database
    Option Explicit

    Public WithEvents TC_txtbox As TextBox
    ''# Set the textbox so that its events will be handled
    Public Property Set TextBox(ByVal m_tcTxtBox As TextBox)
        TC_txtbox = m_tcTxtBox
    End Property


    ''# Handle and onClick event of the
    Private Sub TC_txtbox_Click()
        ''# Find out the controls that where clikck
        Debug.Print Form_TimeCard.ActiveControl.Name
        Dim ctl As Control
        For Each ctl In access.Forms.Controls
            Debug.Print ctl.Name
        Next ctl
    End Sub

Form Code

Option Compare Database
Option Explicit
''# Global Variables
Public clk_inout As Boolean
Public settings
Public weekDict
Public weekOf As Variant
Public curDay As Variant
Public txtBxCollection As Collection
''# Event Handler for when the form opens
Private Sub Form_Open(Cancel As Integer)
    ''# Configure varaibles
    Me.TimerInterval = 60000 ''# 10 sec Interval
    weekOf = getFirstDayofWeek(Date)
    curDay = Date
    Set weekDict = CreateObject("Scripting.Dictionary")
    Set settings = CreateObject("Scripting.Dictionary")
    Set txtBxCollection = New Collection

    ''# Load Time Card Data
    Call initSettings
    ''# Debug.Print "Work Day Goal " & settings.Item("Work_day_goal_hrs")
    Call initDict
    Call initTextBoxEventHandler
    Debug.Print "Collection count " & txtBxCollection.Count
    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
    ''# Debug.Print "Work Day Goal " & settings.Item("Salary")
End Sub

Public Sub initTextBoxEventHandler()
    Dim eventHandler As TextBoxEventHandler
    Set eventHandler = New TextBoxEventHandler
    Debug.Print "Collection count " & txtBxCollection.Count
    Set eventHandler.TextBox = Me.txt_F_in
    txtBxCollection.Add eventHandler

    Debug.Print "Collection count " & txtBxCollection.Count
End Sub

Answer 1

I figure out My problem in the class module where I am setting the text box I forgot to add "TC_txtbox.OnClick = "[Event Procedure]"" VBA will not fire the custom even handler in my extended textbox if [Event Procedure] is not declared in the property of the event you would like to handle

Answer 2

Are you missing a Set? The public property set should be

Public Property Set TextBox(ByVal m_tcTxtBox As TextBox)  
  Set TC_txtbox = m_tcTxtBox  ' dont forget the Set! '
End Property