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

 

[alexaronson]

Norie,

The last line of code is

Application.EnableEvents = True

 

[Norie]

I know it is, but is it definitely being executed?

 

[alexaronson]

VoG
NamShortList is in a regular Module.

Originally I had

 Call NamShortList

line in here until a few months ago when users who were upgraded to Office 2010 started reporting issues that the worksheet was not longer working. Switching to

 Application.Run "'!Test.xlsm'!NamShortList"

took care of that issue.

Norie,

How can I tell if the EnableEvents is being executed?

Thanks

 

[VoG]

In the code window press CTRL + G, type in

Application.EnableEvents=True

then see if the code triggers.

 

[alexaronson]

VoG,

I did the Ctrl+G function, typed in the code that and now it works fine. Including the Application.Run "NamShortList" code.

I not sure what that did, but I'll take it.

VoG and Norie thank you both so much for your help. Your both a valuable asset to the Mr. Excel community.

Thanks Again,
-Alex

 

[Norie]

Alex

Glad you got it working.

You should try and find out why it happened in the first place, perhaps it's avoidable.

 

[VoG]

What happened was this. The first line of your event code disables events. When your code failed it happened with events turned off so they stayed off.

 

[alexaronson]

So as a result of the event code disabling the events, no error message was given?

 

[VoG]

No, because with events disabled your Worksheet_Change sub would not fire when you made a change to the worksheet.

 

[VoG]

You can avoid this problem like this

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ExitNicely
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 "CreateData"
    'When changed, this macrco recreates the variance between TPM and the
    'Key Figure selected
    Case "$E$2"
        Application.Run "CalVariance"
    
    Case "$C$2"
        If Range("C2") = "Exception" Then Application.Run "CalVariance"
        Range("A4") = "Select Your Brand"

    Case "$C$4"
        Range("A4") = "Select Your Brand"
End Select
ExitNicely:
If Err <> 0 Then MsgBox "Error occurred: " & Err.Description, vbExclamation
Application.EnableEvents = True

End Sub