Call a Macro from a User Form where the macro name is build

[Anton_C]

Hi MrExcel,

I created a generic userform which can work with multiple worksheets.

I have a macro in a module that will execute some code to export the sheet to another workbook. (i.e. macro name = Export_BOM_CostSummary() , or Export_BOM_PcsPc() etc.)


The sheet that I want to export has a portion of the macro name. (i.e. sheet name = BOM_CostSummary, or BOM_PcsPc etc.)


From my user form, when I click the Export command button the following should happen: (as I understand it)


Private Sub cmdExport_Click()
'*****************************<wbr>******************************<wbr>******************
'Load Active Workbook Sheet Name
'*****************************<wbr>******************************<wbr>******************


Dim AWS As String 'Active Workbook Sheet Name (AWS)

ActiveSheet.Select
AWS = ActiveSheet.Name ' BOM_CostSummary or BOM_PcsPc


'*****************************<wbr>******************************<wbr>******************
'Create Macro Name to be used
'*****************************<wbr>******************************<wbr>******************


Dim expMacro As String 'Export Macro


expMacro = "Export_" + AWS ' This creates the macro name to be called, (i.e. Export_BOM_CostSummary or Export_BOM_PcsPc)


'*****************************<wbr>******************************<wbr>******************


The expMacro returns the string value as: Export_BOM_CostSummary or Export_BOM_PcsPc



But I do not know what to do further to call the macro with the same name.

 

[ranman256]

to run a macro , just call it:

Private Sub cmdExport_Click()
 Export_BOM_CostSummary
end sub

if you want dynamic, use params , but 1 macro
Export_BOM_CostSummary 1
or
Export_BOM_CostSummary 6



sub Export_BOM_CostSummary(byval pvParam)
select case pvParam
case 1
msgbox "1"
case 6
msgbox "6"
end select
end sub

 

[Anton_C]

Hi Ranman,

This code is on a command button from a userform:

Private Sub cmdExport_Click()

Dim AWS As String 'Active Workbook Sheet Name (AWS)
Dim expMacro As String 'Export Macro

ActiveSheet.Select
AWS = ActiveSheet.Name ' BOM_CostSummary
expMacro = "Export_" + AWS ' This creates the macro name to be called, (i.e. Export_BOM_CostSummary)


I need the command to CALL or RUN the string (expMacro). This is a Macro that is found in a module:
Module name is: M10_Export_SchList
Macro name is: Sub BOM_CostSummary()

This is where I am stuck.

 

[Fluff]

If the sheet name & the sub name are the same, then simply use

Call AWS

 

[Anton_C]

The names are not exactly the same, I am concatenating to get the same name;
AWS = ActiveSheet.Name ' BOM_CostSummary
expMacro = "Export_" + AWS ' This creates the macro name to be called, (i.e. Export_BOM_CostSummary)

 

[Fluff]

In post#3 you said

Macro name is: Sub BOM_CostSummary()

If that is the case then you do not need to add the prefix "Export_"