I have an application that all of a suddend seems to take forever loading into excel. As a matter of fact there is even a fairly good size delay before the program prompts to allow or dissallow macros. How can I find out how to speed up the load time. I do have quite a bit of on workbook open processing but I don't think it is so much that it could be loading down the system. Any ideas would be appreciated. Here is a sample of what I am doing on workbook open:Private Sub Workbook_Open()
Dim test
Dim test2
Dim x
ActiveWorkbook.Unprotect Password:="2rsuccess"
setvars
Utl_BuildCmdBar "invtool", msoBarTop, Workbooks(fname).Sheets("menusheet").Range("cbdata"), fname
Application.ScreenUpdating = False
If Worksheets("invoice").Range("N:IV").EntireColumn.Hidden = False Then
Worksheets("invoice").Range("N:IV").EntireColumn.Hidden = True
End If
If Worksheets("invoice").Range("49:65536").EntireRow.Hidden = False Then
Worksheets("invoice").Range("49:65536").EntireRow.Hidden = True
End If
If Sheets("setup").Visible = True Then
Sheets("setup").Select
Sheets("setup").Visible = False
Sheets("setup").Visible = xlVeryHidden
End If
If Sheets("MenuSheet").Visible = True Then
Sheets("MenuSheet").Select
Sheets("MenuSheet").Visible = False
Sheets("MenuSheet").Visible = xlVeryHidden
End If
If Sheets("temp").Visible = True Then
Sheets("temp").Select
Sheets("temp").Visible = False
Sheets("temp").Visible = xlVeryHidden
End If
If Sheets("Nutritional Products").Visible = True Then
Sheets("Nutritional Products").Select
Sheets("Nutritional Products").Visible = False
Sheets("Nutritional Products").Visible = xlVeryHidden
End If
If Sheets("Personal Care").Visible = True Then
Sheets("Personal Care").Select
Sheets("Personal Care").Visible = False
Sheets("Personal Care").Visible = xlVeryHidden
End If
If Sheets("Colour Cosmetics").Visible = True Then
Sheets("Colour Cosmetics").Select
Sheets("Colour Cosmetics").Visible = False
Sheets("Colour Cosmetics").Visible = xlVeryHidden
End If
If Sheets("Literature").Visible = True Then
Sheets("Literature").Select
Sheets("Literature").Visible = False
Sheets("Literature").Visible = xlVeryHidden
End If
If Sheets("Art of Promotion").Visible = True Then
Sheets("Art of Promotion").Select
Sheets("Art of Promotion").Visible = False
Sheets("Art of Promotion").Visible = xlVeryHidden
End If
If Sheets("Sheet2").Visible = True Then
Sheets("sheet2").Select
Sheets("sheet2").Visible = False
Sheets("sheet2").Visible = xlVeryHidden
End If
If Sheets("Database").Visible = True Then
Sheets("Database").Select
Sheets("Database").Visible = False
Sheets("Database").Visible = xlVeryHidden
End If
If Sheets("Invoice Lookup").Visible = True Then
Sheets("Invoice Lookup").Select
Sheets("Invoice Lookup").Visible = False
Sheets("Invoice Lookup").Visible = xlVeryHidden
End If
If Sheets("Customer").Visible = True Then
Sheets("Customer").Select
Sheets("Customer").Visible = False
Sheets("Customer").Visible = xlVeryHidden
End If
If Sheets("Autofill").Visible = True Then
Sheets("Autofill").Select
Sheets("Autofill").Visible = False
Sheets("Autofill").Visible = xlVeryHidden
End If
If Sheets("Invoice").Visible = True Then
Sheets("Invoice").Select
Else
Sheets("Invoice").Visible = True
Sheets("Invoice").Select
End If
getsetup2
ActiveWorkbook.protect Password:="2rsuccess"
If CheckSysPass() = False Then
EnterPassword.Show
End If
clear
getdate
getnewinvoicenum
'drop = True
Sheets("sheet2").Range("invoice").Value = GetSetting(appname:=ThisWorkbook.name, section:="Startup", _
key:="Invoice", Default:="1000") + 1
Sheets("invoice").invcont = GetSetting(appname:=ThisWorkbook.name, section:="Startup", _
key:="Invoice", Default:="1000") + 1
'invcont = Sheets("sheet2").Range("invoice")
Application.ScreenUpdating = True
End Sub
Just to give you an example of the time on open it took about 15 seconds for the enable/disable macro prompt to come up and then it was another almost 1:30 min before it actually opened. Any ideas would be much appreciated.
John
Dim test
Dim test2
Dim x
ActiveWorkbook.Unprotect Password:="2rsuccess"
setvars
Utl_BuildCmdBar "invtool", msoBarTop, Workbooks(fname).Sheets("menusheet").Range("cbdata"), fname
Application.ScreenUpdating = False
If Worksheets("invoice").Range("N:IV").EntireColumn.Hidden = False Then
Worksheets("invoice").Range("N:IV").EntireColumn.Hidden = True
End If
If Worksheets("invoice").Range("49:65536").EntireRow.Hidden = False Then
Worksheets("invoice").Range("49:65536").EntireRow.Hidden = True
End If
If Sheets("setup").Visible = True Then
Sheets("setup").Select
Sheets("setup").Visible = False
Sheets("setup").Visible = xlVeryHidden
End If
If Sheets("MenuSheet").Visible = True Then
Sheets("MenuSheet").Select
Sheets("MenuSheet").Visible = False
Sheets("MenuSheet").Visible = xlVeryHidden
End If
If Sheets("temp").Visible = True Then
Sheets("temp").Select
Sheets("temp").Visible = False
Sheets("temp").Visible = xlVeryHidden
End If
If Sheets("Nutritional Products").Visible = True Then
Sheets("Nutritional Products").Select
Sheets("Nutritional Products").Visible = False
Sheets("Nutritional Products").Visible = xlVeryHidden
End If
If Sheets("Personal Care").Visible = True Then
Sheets("Personal Care").Select
Sheets("Personal Care").Visible = False
Sheets("Personal Care").Visible = xlVeryHidden
End If
If Sheets("Colour Cosmetics").Visible = True Then
Sheets("Colour Cosmetics").Select
Sheets("Colour Cosmetics").Visible = False
Sheets("Colour Cosmetics").Visible = xlVeryHidden
End If
If Sheets("Literature").Visible = True Then
Sheets("Literature").Select
Sheets("Literature").Visible = False
Sheets("Literature").Visible = xlVeryHidden
End If
If Sheets("Art of Promotion").Visible = True Then
Sheets("Art of Promotion").Select
Sheets("Art of Promotion").Visible = False
Sheets("Art of Promotion").Visible = xlVeryHidden
End If
If Sheets("Sheet2").Visible = True Then
Sheets("sheet2").Select
Sheets("sheet2").Visible = False
Sheets("sheet2").Visible = xlVeryHidden
End If
If Sheets("Database").Visible = True Then
Sheets("Database").Select
Sheets("Database").Visible = False
Sheets("Database").Visible = xlVeryHidden
End If
If Sheets("Invoice Lookup").Visible = True Then
Sheets("Invoice Lookup").Select
Sheets("Invoice Lookup").Visible = False
Sheets("Invoice Lookup").Visible = xlVeryHidden
End If
If Sheets("Customer").Visible = True Then
Sheets("Customer").Select
Sheets("Customer").Visible = False
Sheets("Customer").Visible = xlVeryHidden
End If
If Sheets("Autofill").Visible = True Then
Sheets("Autofill").Select
Sheets("Autofill").Visible = False
Sheets("Autofill").Visible = xlVeryHidden
End If
If Sheets("Invoice").Visible = True Then
Sheets("Invoice").Select
Else
Sheets("Invoice").Visible = True
Sheets("Invoice").Select
End If
getsetup2
ActiveWorkbook.protect Password:="2rsuccess"
If CheckSysPass() = False Then
EnterPassword.Show
End If
clear
getdate
getnewinvoicenum
'drop = True
Sheets("sheet2").Range("invoice").Value = GetSetting(appname:=ThisWorkbook.name, section:="Startup", _
key:="Invoice", Default:="1000") + 1
Sheets("invoice").invcont = GetSetting(appname:=ThisWorkbook.name, section:="Startup", _
key:="Invoice", Default:="1000") + 1
'invcont = Sheets("sheet2").Range("invoice")
Application.ScreenUpdating = True
End Sub
Just to give you an example of the time on open it took about 15 seconds for the enable/disable macro prompt to come up and then it was another almost 1:30 min before it actually opened. Any ideas would be much appreciated.
John