"Application.Run" Problem

[TFCJamieFay]

Hi Guys,

I'm trying to call a sub called "ValidateApp", stored in a module called "ValidateApp" with the following code...

Private Sub SubmitBtn_Click()

    msg = MsgBox("Are you sure you want to submit this application?", vbYesNo + vbExclamation, _
        "Warning!")
    
    If msg = vbNo Then
        TrackerForm.MainNameTBx.SetFocus
    Else
        Application.Run "ValidateApp"
    End If

End Sub

...the problem is however that I get an error on the line...

Application.Run "ValidateApp"

...saying that the macro cannot be found. The "Private Sub SubmitBtn_Click" is ran from a user form with a command button.

I have recently moved the code to a new module, which is probably the cause but what can I change so it now knows where to look? If it's any help the old module was called "SecondaryCode".

Many thanks,

Jay

 

[RoryA]

Do not ever give a sub the same name as a module - it will cause you all sorts of problems. I suggest you rename one or the other.

 

[Datsmart]

It is never wise to name different items or objects by the same name. It not only confuses the user, it can confuse Excel.

Did the code work when you had the code in the module "SecondaryCode"?

 

[TFCJamieFay]

Thanks Rorya, I'll re-name them and see if that works.

Jay

 

[RoryA]

Additionally, it is safer to include the workbook name in the Run statement so that you can be sure it is the macro you are expecting that actually runs!

 

[TFCJamieFay]

I've re-named all my modules now, and everything works fine. I thought I was being clever and keeping my project easy to navigate.

One final thing, how would I write the "Application.Run "ValidateApp"" code with the workbook name if the WB is called "Book4.xls" please?

Thanks again for your help!

Jay

 

[Kenneth Hobson]

If you qualify it properly, it will work.

e.g.

  Application.Run "Run!Run.Run"

This will run the Sub Run from the Module named Run from any workbook as long as Run.xls is open. Talk about running...

We discussed it in a recent thread as well. http://www.mrexcel.com/forum/showthread.php?t=377928

Book4.xls is probably not a good name. Try not to use any Book names. They are fine for temporary workbooks.

 

[GTO]

Hi Jay,

I am probably missing something, but reference the procedure you are calling - I get that it's in a new module, but the module is in another workbook than the userform is?

Mark

 

[TFCJamieFay]

Hi Kenneth,

I wonder if you can help? This code sits inside upto 20 different workbooks (each member of staff has their own copy) so the workbook name must be dynamic. I've tried the following code but only the one which isn't commented out works. Do you have any ideas for getting around this?

Many thanks,

Jay

Sub RunTest()

    Dim MyFileName As String
    
    MyFileName = Mid(ThisWorkbook.Name, 1, Len(ThisWorkbook.Name) - 4)

    Application.Run "Tracker_Jamie_Fay!TestModule.Test"
    'Application.Run "MyFileName!TestModule.Test"

End Sub

GTO - The Module and Userform are in the same workbook.

 

[GTO]

Ehhh... I'm going to use the excuse that I'm a bit under the weather. Why are we using Run? Why not:

Private Sub SubmitBtn_Click()

    msg = MsgBox("Are you sure you want to submit this application?", vbYesNo + vbExclamation, _
        "Warning!")
    
    If msg = vbNo Then
        TrackerForm.MainNameTBx.SetFocus
    Else
        Call ValidateApp
    End If

End Sub

Mark