Excel VBA: Action logging on label click (like Audit Trail)

Question

I wrote a code where every time a label is clicked a line is logged that label XY was clicked. However, I have the problem that when I open UserForm5 8 lines are automatically filled with Label X was clicked, Label Y was licked. But all I want is that I click label, that will be logged. How can I modify the code so that it works?

Attached is my code, this code is in my Module:

`Option Explicit

Public Sub LogAction(action As String)
    Dim ws As Worksheet
    Dim lastRow As Long


   
    Dim userName As String
    userName = Environ("USERNAME")

    
    Dim timestamp As Date
    timestamp = Now

    
    Set ws = ThisWorkbook.Sheets("Tabelle1")

    
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row + 1

    
    ws.Cells(lastRow, 1).Value = userName
    ws.Cells(lastRow, 2).Value = action
    ws.Cells(lastRow, 3).Value = timestamp
End Sub

Sub AssignLabelClickHandlers()

    Dim ctrl As Control
    For Each ctrl In UserForm5.Controls
        If TypeName(ctrl) = "Label" Then
            LogAction "label" & ctrl.Caption
        End If
    Next ctrl
End Sub
`

And this one on my UserForm5:

`UserForm_Initialize()
    AssignLabelClickHandlers
End Sub`

Answer 1

1. Please, delete your existing procedure LogAction and the actual content of UserForm_Initialize event

2. Please, insert a class module and name it "ClassLabelsEvents", then copy the next code inside its code module:

Option Explicit

Public WithEvents myLabel As MSForms.Label

Private Sub myLabel_Click()
    Dim ws As Worksheet, lastRow As Long
    Set ws = ThisWorkbook.Sheets("Tabelle1")
    
    lastRow = ws.cells(ws.rows.count, "A").End(xlUp).row + 1

    'It is faster to place all three necessary values at once, from an array:
    ws.cells(lastRow, 1).Resize(, 3).value = Array(Environ("USERNAME"), myLabel.Caption, Now)
End Sub

3. Declare the next variable on top of the user form code modulel (in the declarations area):

Option Explicit

Private lblClass() As New ClassLabelsEvents

If there are already other declared variables in that area, just place another code line containing the above declaration. 4. Copy the next code in the UserForm_Initialize event:

Private Sub UserForm_Initialize()
  Dim ctrl As MSForms.Control, k As Long

  ReDim lblClass(Me.Controls.count) 'redim the array to be able to keep the whole number of existing controls

  For Each ctrl In Me.Controls 'iterate between the user form controls:
      If TypeName(ctrl) = "Label" Then 'for label controls type only:
        Set lblClass(k).myLabel = ctrl: k = k + 1 'add them in the class array
      End If
  Next

  ReDim Preserve lblClass(k - 1) ' keep only loaded label controls in the array
End Sub

Now, activate "Tabelle1" sheet (not necessary for the code to run, only to see the logging result as it is done) and play with clicking labels...

I tried commenting all lines which could not be understood. If something not clear enough, do not hesitate to ask for clarifications.

Please, send some feedback after testing the above suggested solution.