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!
 

David Montoya

New Member
Joined
Apr 25, 2018
Messages
49
Here you go:

This is the close:

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

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
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Yongle

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

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

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

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

Watch MrExcel Video

Forum statistics

Threads
1,127,519
Messages
5,625,278
Members
416,086
Latest member
CaptainGD

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