Private Subs to run at opening a workbook

David Montoya

New Member
Joined
Apr 25, 2018
Messages
49
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:
Code:
    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.
Code:
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.
Code:
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!
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
Why make the user enter name? Could automate

instead of
Code:
    Sheets("Employee").Select
    If Range("A2").Value = "" Then
        MsgBox ("Please input your user name")
    End If
    Exit Sub
try one of these as appropriate
Code:
    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 ..
Code:
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
Code:
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

New Member
Joined
Apr 25, 2018
Messages
49
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

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Apr 25, 2018
Messages
49

ADVERTISEMENT

Yonglo,

Here is the code:

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
 
Last edited:

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
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
Code:
MsgBox "Append_and_PDFs Triggered"

Please post the full code of Append_and_PDFs
 

David Montoya

New Member
Joined
Apr 25, 2018
Messages
49

ADVERTISEMENT

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?
 
Last edited:

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
I am running Excel 2016 and have tested this line and it works
- it triggers the macro (with UserName constraint and from Worksheet_Open)
Code:
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
 
Last edited:

David Montoya

New Member
Joined
Apr 25, 2018
Messages
49
I did the following:

Code:
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

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,130,015
Messages
5,639,558
Members
417,098
Latest member
steverob

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top