Fetching a VBA Macro's Name During Execution

  • Thread starter Thread starter SidBord
  • Start date Start date
S

SidBord

In all the macros I write, I put the name of the macro in
the title bar of all Msgbox and Inputbox msgs I generate.
Is there a VBA statement that will fetch the name of the
macro that is executing so I don't have to hard code the
macro name into my msgs? I want something like the code
that fetches the worksheet name that is currently active.
 
Perhaps this:

Sub GetProcedureName()
Dim Msg As String, Style As Integer, Title As String

'If calling from a commandbar control:
Title = Application.CommandBars.ActionControl.OnAction
'If calling from a worksheet based control (Forms toolbar):
'Title = ActiveSheet.Shapes(Application.Caller).OnAction
Title = Right(Title, Len(Title) - InStr(1, Title, "!"))
Msg = "The rain in Spain falls mainly on the plain. "
Style = vbInformation
MsgBox Msg, Style, Title

End Sub

Regards,
Greg
 
Alternatively:

Sub TestGetProcName()
Dim Msg As String, Style As Integer
Msg = "The rain in Spain falls mainly on the plain. "
Style = vbInformation
MsgBox Msg, Style, GetProcName
End Sub

Function GetProcName()
Dim Txt As String
On Error Resume Next
With Application
Txt = .CommandBars.ActionControl.OnAction
Txt = ActiveSheet.Shapes(.Caller).OnAction
Txt = Right(Txt, Len(Txt) - InStr(1, Txt, "!"))
End With
On Error GoTo 0
GetProcName = Txt
End Function

Regards,
Greg
 
Back
Top