One Event Class, Many Labels

[swalsh84]

I have about 20 different labels on a form. I want to be able to write my code once for the event instead of having to copy and paste the same code into each sub if something ever changes. I found this: http://www.vbaexpress.com/forum/showthread.php?t=31316 , but I'm not having any luck translating it to MS Access 2007 in a working way.



Any thoughts?

[pbaldy]

Is this what you're looking for?

http://www.access-programmers.co.uk/...d.php?t=166799

[swalsh84]

Thanks! This is sort of what I need; however, it does not work. In both cases, I'm wanting to slim down my code and apply one event handler for many labels. Labels are a bit funny when it comes to click events (or so has been my experience).

I'm using an array to tell the loop which labels to assign the new function to, but when I click or double click nothing happens. Not really sure what I'm doing wrong.

In the form:

Private Sub Form_Open(ByRef Cancel As Integer)
    If Not bDev Then On Error GoTo PROC_ERR
    PushCallStack "fsubLossTypeDetail - Form_Open"
    Dim i As Long
    Dim strControlName As String
    Dim lblArray() As String
    ReDim lblArray(1 To 15)
 
    lblArray(1) = "lblLossType"
    lblArray(2) = "lblLossSubType"
    lblArray(3) = "lblAmtDue"
    lblArray(4) = "lblLossDate"
    lblArray(5) = "lblClaimed"
    lblArray(6) = "lblRecoverable"
    lblArray(7) = "lblResponsibleParty"
    lblArray(8) = "lblBillBackTo"
    lblArray(9) = "lblNotificationDate"
    lblArray(10) = "lblOrigInvoiceNum"
    lblArray(11) = "lblOrigInvoiceDate"
    lblArray(12) = "lblOrigInvoiceAmt"
    lblArray(13) = "lblDupInvoiceNum"
    lblArray(14) = "lblDupInvoiceDate"
    lblArray(15) = "lblDupInvoiceAmt"
'    lblArray(16) = ""
'    lblArray(17) = ""
'    lblArray(18) = ""
'    lblArray(19) = ""
'    lblArray(20) = ""
 
    For i = 1 To UBound(lblArray)
        strControlName = lblArray(i)
        Me(strControlName).OnClick = MakeFunctionCall("HandleOnClick", strControlName)
        Me(strControlName).OnDblClick = MakeFunctionCall("HandleOnDblClick", strControlName)
'        Me(strControlName).OnMouseMove = MakeFunctionCall("HandleOnMouseMove", strControlName)
    Next i
 
    Form_frmMain.Painting = False
    gCLATrackID = 0
    LoadDropDown "LossType", cboLossType
    LoadDropDown "RespParty", cboResponsibleParty
PROC_EXIT:
    PopCallStack
    Exit Sub
PROC_ERR:
    GlobalErrHandler Err.Number, Err.Description, True
    Resume PROC_EXIT
End Sub
 
Private Function HandleOnClick(ByVal strThisControlName As String)
 
    MsgBox strThisControlName
 
End Function
Private Function HandleOnDblClick(ByVal strThisControlName As String)
 
    MsgBox strThisControlName
 
End Function

In a separate module:

Public Function MakeFunctionCall(ByVal strFunctionName As String, _
                            ParamArray vntArgList() As Variant) As String
 
    Dim lngElement  As Long
    Dim strFunction As String
 
    ' The first argument is NOT optional.
    ' Add Function name and opening bracket.
    strFunction = "=" & strFunctionName & "("
 
    ' All the remaining arguments are optional.
    ' Loop through argument range, if passed.
    For lngElement = LBound(vntArgList) To UBound(vntArgList)
        strFunction = strFunction & Chr$(34) & vntArgList(lngElement) & Chr$(34) & ", "
    Next lngElement
 
    ' Did we receive any arguments?
    ' If so, trim off trailing ", ".
    If Right$(strFunction, 2) = ", " Then
        strFunction = Left$(strFunction, Len(strFunction) - 2)
    End If
 
    ' Set return valve and closing bracket.
    MakeFunctionCall = strFunction & ")"
 
End Function

[boblarson]

You need to change this

Me(strControlName).OnClick = MakeFunctionCall("HandleOnClick", strControlName)

to this

Me(strControlName).OnClick = "=MakeFunctionCall('HandleOnClick', " & strControlName & ")"

(like that for the double click and mouse move events too)

[swalsh84]

Well, I figured it out. All of the labels I'm wanting to use are associated with various text boxes, combos, and list boxes. For some reason that nixes the ability to assign a click event to a label. Wish there was an override so I don't have to recreate all of my labels, but lesson learned.

BTW, Bob, your code does not work. Just an FYI. The original is the correct solution. Thanks for the effort, though.
Thanks for the help, all.

[boblarson]

BTW, Bob, your code does not work. Just an FYI. The original is the correct solution.

News to me since I use that frequently and it works fine.