Active Workbook Name To Call a Macro

[alexaronson]

Hello and thank you for your help

I have the following code that calls a macro for me.

Application.Run "Test.xlsm'!NamShortList"

One of the problems I am runnign into is that my users are changing the file name. So I thought about changing the file name to a variable with the following code

Dim WbName As Variant
WbName = ActiveWorkbook.Name

I am trying to replace the Test.xlsm piece of the original code with WbName. However I can't seem to get the macro to fire off.

Any suggestions?
Thanks

 

[VoG]

I may be missing something but can't you just use

Application.Run "NamShortList"

 

[alexaronson]

VoG,

I tried that originally when I first built my sheet, but it would not work. Maybe because it in a Prive Sub Worksheet_Change(ByVal Target As Range) macro.

 

[VoG]

I just tried it with NamShortList in a regular module. It fired when called from a WorkSheet_Change event.

 

[kunal_shrivastava]

Hi Alex,

Application.Run "'" & Application.ActiveWorkbook.Name & "'!NamShortList"

The above code runs at my end, can you please share the error message?

Regards,
Kunal Shrivastava

 

[alexaronson]

VoG,

It is still not working for me.

Here is my code in full. Maybe something will stand out.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False

Select Case Target.Address
    'Nam Changes triggers macro to create a list of brands for the nam
    Case "$A$2"
        Application.Run "!NamShortList"
        'Once the brand is selected, this macro creates the data to display
    Case "$A$4"
         Application.Run "Test.xlsm'!CreateData"
    'When changed, this macrco recreates the variance between TPM and the
    'Key Figure selected
    Case "$E$2"
        Application.Run "Test.xlsm'!CalVariance"
    
    Case "$C$2"
        If Range("C2") = "Exception" Then Application.Run "Test.xlsm'!CalVariance"
        Range("A4") = "Select Your Brand"

    Case "$C$4"
        Range("A4") = "Select Your Brand"
End Select
Application.EnableEvents = True

End Sub

 

[VoG]

You need to delete the !

Application.Run "NamShortList"

 

[alexaronson]

Still not working. I copied and pasted your last post.

 

[VoG]

Is NamShortList in a regular module?

Does this work?

Call NamShortList

 

[Norie]

Are you sure events are enabled?

They might have been disabled, then for whatever reason not re-enabled.

That's probably more likely to happen if you are branching off to different subs.