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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

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