Microsoft Office Forums - View Single Post - Get Sub Module Name using VBA and Truncate to Define Variable?

gmaxey · 08-01-2020, 09:37 AM

You might possible use a Public WithEvents declaration in a collection class. Add a class module (name it clsCmdButtons) in the project and paste the following code:

Code:

Option Explicit
Public WithEvents evtCmdButton  As msforms.CommandButton
Private Sub evtCmdButton_Click()
  modName.SubName evtCmdButton.Name
End Sub

In your userform paste the following code. Note: be sure to exclude the command button you might use to close the the form.

Code:

Dim colCmdButtons As Collection
Private Sub UserForm_Initialize()
Dim oControl As Control, oAdd As Object
  Set colCmdButtons = New Collection
  For Each oControl In Controls
    Select Case TypeName(oControl)
      Case "CommandButton"
        If Not oControl.Name = "cmdExit" Then
          Set oAdd = New clsCmdButton
          Set oAdd.evtCmdButton = oControl
          colCmdButtons.Add oAdd, oAdd.evtCmdButton.Name
        End If
     End Select
  Next
lbl_Exit:
  Exit Sub
End Sub
Private Sub UserForm_Terminate()
  Set colCmdButtons = Nothing
End Sub
Private Sub cmdExit_Click()
  Hide
lbl_Exit:
  Exit Sub
End Sub