Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 08-01-2020, 07:21 AM
rekent rekent is offline Get Sub Module Name using VBA and Truncate to Define Variable? Mac OS X Get Sub Module Name using VBA and Truncate to Define Variable? Office for Mac 2011
Novice
Get Sub Module Name using VBA and Truncate to Define Variable?
 
Join Date: May 2014
Posts: 22
rekent is on a distinguished road
Default Get Sub Module Name using VBA and Truncate to Define Variable?

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?
Reply With Quote
  #2  
Old 08-01-2020, 09:37 AM
gmaxey gmaxey is offline Get Sub Module Name using VBA and Truncate to Define Variable? Windows 10 Get Sub Module Name using VBA and Truncate to Define Variable? Office 2016
Expert
 
Join Date: May 2010
Location: Brasstown, NC
Posts: 1,421
gmaxey is a jewel in the roughgmaxey is a jewel in the roughgmaxey is a jewel in the roughgmaxey is a jewel in the rough
Default

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
__________________
Greg Maxey
Please visit my web site at http://www.gregmaxey.com/
Reply With Quote
Reply

Thread Tools
Display Modes


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
Get Sub Module Name using VBA and Truncate to Define Variable? 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

Other Forums: Access Forums

All times are GMT -7. The time now is 05:06 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft