Slow running Workbook Open macros

[si_1970]

Hi All

I have inherited a workbook to sort out which with my limited knowledge of Excel is causing me a bit of a headache. Upon opening the workbook updates a list of names from an external source (another workbook) and then asks for the user to enter their payroll number which it validates against the list. The rest of the workbook appears pretty straight forward and i have sorted the majority of issues, but for some reason there are so many checks to go through when opening the workbook.

I have got everything running however I am still stuck with a spinning wheel for 20 - 30 seconds before the input box appears.

Any help that you can give to simplify the below will be appreciated.

Private Sub Workbook_Open()
    Application.ScreenUpdating = False
    Dim myValue As Variant
    Worksheets("engineers").Activate
    Range("A3").Select
    'Updates list of names from an external source
    Selection.ListObject.TableObject.Refresh
    On Error GoTo Procend


    'If sheet already contains data k22 will be greater than 1 and no macros are to run
    If Sheets("Header").Range("k22").Value >= 1 Then
        Sheets("Header").Protect "Password1"    'for some reason the header sheet always opens unprotected
        Sheets("Header").Select
        Range("c11").Select
        ActiveWindow.ScrollRow = 1
        ActiveWindow.ScrollColumn = 1
        GoTo Procend


    Else
        'If sheet1 is visible no macros will run. Sheet one contains folder link information populated at first use and used in later macros.
        If Sheets("Sheet1").Visible = True Then
            Sheets("Sheet1").Select
            GoTo Procend


        Else
            'An input box is enabled for the user of the workbook to enter their payroll number. If nothing is entered then the workbook closes
            myValue = InputBox("ADVISORS - Enter your Payroll Number" & vbNewLine & vbNewLine & "CAUTION - ZERO INPUT OR PRESSING CANCEL WILL CLOSE THE TOOL", "PAYROLL NUMBER VERIFICATION")
            If myValue = vbNullString Then
                MsgBox "The tool will now close", vbCritical, "YOU WERE WARNED"
                'macro to clear all user fields of data
                Run "startclear"
                Application.DisplayAlerts = False
                ThisWorkbook.Close


            Else
                'if the user enters a payroll number which is not found then a userform runs to allow data capture and then the main macro runs
                Sheets("List Data").Range("A1").Value = myValue
                If Sheets("List Data").Range("a3").Value = 0 Then
                    UserForm1.Show
                    Run "EngCheck"


                Else
                    'if the user enters a valid payroll number the main macro runs
                    Run "EngCheck"
                End If
            End If
        End If
    End If
    Application.ScreenUpdating = True
Procend:     Exit Sub


End Sub

 

[mole999]

consider
If Sheets("Header").Range("k22").Value >= 1 Then ExitSub as the first check if you really want no macros to run