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!

 

[Yongle]

Why make the user enter name? Could automate

instead of

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

try one of these as appropriate

    Sheets("Employee").Range("A2").Value = Application.UserName     'Excel user name
    Sheets("Employee").Range("A2").Value = Environ("UserName")      'Windows user name

I would expect this to work ..

Private Sub Workbook_Open()
    Sheets("Employee").Range("A2").Value = Application.UserName     'Excel user name
    Call MasterDB
    Call RunSchedule
End Sub

You have not mentioned getting an error message so that suggests the 2nd macro is not being triggered or else not doing what you expect
Insert this as the first lines of RunSchedule to prove that macro is being triggered

MsgBox "RunSchedule Trigerred"

... and then: test values various points whilst the macro runs to determine where it is failing and then work out why

 

[David Montoya]

Dear Jongle,

I have done the suggested Application.Username, and it works fine for what I need.

The remaining problem I have is with the RunShedule. I have entered the message RunSchedule.Triggered; it does show at the workbook opening. I have done the test on four different computers: two of them with Win-7 / Office 2010, which it works without problems; however, under Win-10 / Office 2016, it does not run.

Please let me know if there is anything I can do to force the automatic schedule to run.

Thanks a lot.
Thank for your help

 

[Yongle]

Please let me know if there is anything I can do to force the automatic schedule to run.

It is running - otherwise the message box would not appear
So now we need to find out why it is not giving the expected outcome under Excel 2106
Please post the full code l for RunSchedule

 

[David Montoya]

Yonglo,

Here is the code:

Sub RunSchedule()
MsgBox "RunSchedule Trigerred"
    If Application.UserName = "username1" Then
    Application.OnTime TimeValue("16:40:00"), "Append_and_PDFs"
    ElseIf Application.UserName = "username2" Then
    Application.OnTime TimeValue("16:35:00"), "Append_and_PDFs"
    ElseIf Application.UserName = "username3" Then
    Application.OnTime TimeValue("16:30:00"), "Append_and_PDFs"
    End If
End Sub

Thank you

 

[Yongle]

RunSchedule is calling Append_and_PDFs
- check if Append_and_PDFs being triggered

Add a message box in as first line of that macro and confirm back here that the macro is being triggered

MsgBox "Append_and_PDFs Triggered"

Please post the full code of Append_and_PDFs

 

[David Montoya]

Yongle,

Append_and_PDFs, is a procedure I have in my application also at workbook closing, to make sure that our master database always capture the appends and PDF; additionally, I have it as a manual process, where the user can trigger the macro with a "button". This procedure has been working fine for about two years. It gets triggered with no problem.

By the way the Append_and_PDFs procedure works automatically and perfect, at closing even if under Windows 2010 and Office 2016.

The exception, as I have mentioned is with the RunSchedule, when it is processed under the new operating system and new office 2016 version.

I am wondering if the RunShechule could be done using a different code/macro to make it to run efficiently under the new environment?

 

[Yongle]

I am running Excel 2016 and have tested this line and it works
- it triggers the macro (with UserName constraint and from Worksheet_Open)

Application.OnTime TimeValue("16:30:00"), "Append_and_PDFs"

You are making an assumption that you know where the problem is
A scatter-gun approach is unreliable - let's work logically through the problem and prove it
Amending RunSchedule will not make any difference if Append_and_PDFs is actually being triggered

We must determine which statement is true
- Append_and_PDFs is not being triggered
- Append_and_PDFs is not doing what is expected

If it is not being triggered then the answer lies in RunSchedule
If it is being triggered, then the answer lies in Append_and_PDFs and the fact that you are able to run it manually is irrelevant

Please let me know if the macro is being triggered

 

[David Montoya]

I did the following:

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

And I did not got the message.

When I do it manually or at close, which is automatic it does show the trigger message.

 

[Yongle]

When I do it manually or at close, which is automatic it does show the trigger message

please post the code for at close macro