Private Subs to run at opening a workbook

[David Montoya]

Dear, I need to run automatically three events upon opening the workbook; it seems that when I put them together within the same sub, only one runs and by-passes the others.

The first sub I need to run is to enter a user name, it should be enforced, otherwise to close the workbook. At the mentioned cell, I have already a validation verification to make sure that the entered user is within the table:

    Sheets("Employee").Select
    If Range("A2").Value = "" Then
    MsgBox ("Please input your user name")
    End If
    Exit Sub

The second event is to call a separate macro ("MasterDB"). This macro normally works by itself without a glitch; however, if I entered the above within the same sub, it only displays the message "Please input your user name" without enforcing the name input, and proceed with the "MasterDB" macro, which runs fine.

Call MasterDB

The third event is to call another separate macro (RunSchedule". When having the "MasterDB" and "RunSchedule" under the same sub, and used with Excel 2010 these two together run fine; however, under Excel 2016 the "MasterDB" runs, but "RunSchedule" do not.

RunSchedule

The question is how to put all three events on a private sub, and to make sure that they run without skipping any one.

Thank you!

 

[David Montoya]

Here you go:

This is the close:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
      
Call Append_and_PDFs


    Application.ScreenUpdating = True
    Application.DisplayAlerts = True


End Sub

This is the manual:

Sub Append_and_PDFs()


MsgBox "Append_and_PDFs Triggered"


Application.Visible = False
    
Application.DisplayAlerts = False
Application.ScreenUpdating = False
        
Call Move_TEMP_3311
Call Move_TEMP_TSCA
Call Move_TEMP_ImpDecl
Call Move_TEMP_FDA_2877
Call Move_TEMP_Corrected_CI
Call DailyAppends


Application.ScreenUpdating = True
Application.DisplayAlerts = True


Application.Visible = True
End Sub

 

[Yongle]

Your macros work as expected
- I have tested and proven that the code calls every macro

But I cannot test your UserName check
- if that test fails your macro is not called
- test that on its own like this with valid user names

Sub UserNameTest()
    If Application.UserName = "username1" Then
        MsgBox 1
    ElseIf Application.UserName = "username2" Then
        MsgBox 2
    ElseIf Application.UserName = "username3" Then
        MsgBox 3
    End If
End Sub

 

[David Montoya]

Yogle,

Ok, so to batter you so much...

All of a sudden, the RunSchedule start working under the new environment. Only one issue, after runs automatically it creates a blank ghost Excel page behind the actual workbook; if I closed the ghost page the complete excel application gets closed.

 

[Yongle]

Suggest you start a new thread for that problem
- it's a totally different question and hopefully someone will be able to help lay your ghost to rest.

 

[David Montoya]

Yongle,

Thank you for all your support