Get Sub Module Name using VBA and Truncate to Define Variable?

rekent · #1 08-01-2020, 07:21 AM

I have a UserForm with multiple buttons backed up by code for each individual button as follows:

Code:

Private Sub Name_Click()
  strName = "Name"
  Call ModuleName.SubName(strName)
  Unload Me
End Sub

where Name changes for each button. Rather than hardcoding strName = "Name" I would like to reference the name of the module and truncate the _Click() portion of the module name to then get the function for my strName definition. Is this something that is possible within VBA?

gmaxey · #2 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

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
I want to create a bar chart of multiple variable. Then I need to draw trend lin of those variable	shimulsiddiquee	Excel	1	05-16-2017 07:39 AM
Run Time Error '91': Object variable or With block variable not set using Catalogue Mailmerge	Berryblue	Mail Merge	1	11-13-2014 05:36 PM
Run-time error 91 object variable or with block variable not set	JUST ME	Word VBA	4	03-25-2014 06:56 AM
DocVariable formatting and the ability to truncate a string.	MaxInCO	Word	1	12-18-2013 05:35 PM
AZWizard Module - ?hidden module	pcaldwell	Word	1	08-22-2012 01:19 PM