screenupdating = false not working in Workbook_open sub

  • Thread starter Thread starter TommySzalapski
  • Start date Start date
T

TommySzalapski

Application.ScreenUpdating = False

This code works well except in the Private Sub Workbook_Open() macro
where it seems to be comepletely ignored. Why?

thanks,
Tommy
 
I am using Excel 2000 (from the Office 2000 SR-Professional package) an
this is the code where screenupdating = false is ignored

Private Sub Workbook_Open()

Dim strDriver As String
Dim driverFile As Workbook

strDriver = Worksheets("Form").Range("B1").Value

If Len(Dir(strDriver, vbDirectory)) = 0 Then
strDriver = ThisWorkbook.Path & "\" & strDriver
End If

If Len(Dir(strDriver)) = 0 Then ' file does not exist
* MsgBox "File not found: " & strDriver, vbExclamation, "Error!"
strDriver = Application.GetOpenFilename("Excel Workbooks,*.xls, Al
Files, *.*", _
1, "Select the report compiler file.")

If strDriver = "False" Then
MsgBox "No driver file loaded; reports will not work."
vbExclamation, "Error!"
Exit Sub
End If

End If

Application.ScreenUpdating = False

Set driverFile = Workbooks.Open(strDriver)

strDataPath = driverFile.Path & "\" & "Data"

If ThisWorkbook.Path = driverFile.Path Then
Worksheets("Form").Range("B1").Value = Dir(strDriver)
Else
Worksheets("Form").Range("B1").Value = strDriver
End If

strDriver = Dir(strDriver)

driverFile.Worksheets("Info").UsedRange.Copy _
ThisWorkbook.Worksheets("Info").Range("A1")
driverFile.Worksheets("Data").UsedRange.Copy _
ThisWorkbook.Worksheets("Data").Range("A1")

Workbooks(strDriver).Close

Worksheets("Form").Range("B3").Value
Worksheets("Info").Range("A12").Value
Worksheets("Form").Range("B4").Value
Worksheets("Info").Range("A14").Value
Worksheets("Form").Range("B5").Value
Worksheets("Info").Range("A13").Value

Worksheets("Form").eDepartment.ListFillRange = "Info!F2:F"
Worksheets("Info").Range("E1")
Worksheets("Form").eDepartment.Height = (Worksheets("Info").Range("E1"
- 1) * 12.5
Worksheets("Form").Activate
Worksheets("Form").eFromYear.Value
Worksheets("Form").Range("B3").Value
Worksheets("Form").eToYear.Value
Worksheets("Form").Range("B3").Value
Worksheets("Form").eFromWeek.Value
Worksheets("Form").Range("B5").Value - 1
Worksheets("Form").eToWeek.Value = Worksheets("Form").Range("B5").Valu
- 1

Application.ScreenUpdating = True

End Su
 
I didn't set up a workbook that mimicked your workbook (way too much work!).

But I didn't see anything that would cause the application.screenupdating not to
work.

You could add a few lines that might help you determine where it gets turned
back on, though:

Pepper your code with:
Debug.Print "step 1: " & Application.ScreenUpdating
'your code
Debug.Print "step 2: " & Application.ScreenUpdating
'more of your code
Debug.Print "step 3: " & Application.ScreenUpdating
'etc....

Then after it runs, you can look at the immediate window (in the VBE) to see
what's happening.
 
The Workbooks.Open command is setting it back to true. I tried adding

With driverFile
..Application.ScreenUpdating = false
End With

and it did turn back off, but after printing the newly opened book to
the screen

Wait, I just figured it out, the workbook I am opening also disables
screen updating and reenables it in its open event. How can I get
around this? Is there a way to open the file and disable its macros?
(That doesn't seem to be a parameter for the open method)
 
Application.EnableEvents = False
' open the workbook
Application.EnableEvents = True
 
Back
Top