screenupdating = false not working in Workbook_open sub

[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

[Dave Peterson]

I've never seen this line ignored.

But I have seen calls to built in routines turn it back on. (No, I don't
remember what they were. Sorry.)

Maybe you can post your troublesome code (and the version of excel) that causes
the problem.

TommySzalapski wrote:
>
> 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
>
> --
> TommySzalapski
> ------------------------------------------------------------------------
> TommySzalapski's Profile: http://www.excelforum.com/member.php...o&userid=25561
> View this thread: http://www.excelforum.com/showthread...hreadid=391960

--

Dave Peterson

[TommySzalapski]

I am using Excel 2000 (from the Office 2000 SR-Professional package) and 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, All 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").Value - 1

Application.ScreenUpdating = True

End Sub

[Dave Peterson]

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.



TommySzalapski wrote:
>
> I am using Excel 2000 (from the Office 2000 SR-Professional package) and
> 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, All
> 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").Value
> - 1
>
> Application.ScreenUpdating = True
>
> End Sub
>
> --
> TommySzalapski
> ------------------------------------------------------------------------
> TommySzalapski's Profile: http://www.excelforum.com/member.php...o&userid=25561
> View this thread: http://www.excelforum.com/showthread...hreadid=391960

--

Dave Peterson

[TommySzalapski]

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)

[Tom Ogilvy]

Application.EnableEvents = False
' open the workbook
Application.EnableEvents = True

--
Regards,
Tom Ogilvy

"TommySzalapski"
<[email protected]> wrote in
message news:[email protected]...
>
> 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)
>
>
> --
> TommySzalapski
> ------------------------------------------------------------------------
> TommySzalapski's Profile:
http://www.excelforum.com/member.php...o&userid=25561
> View this thread: http://www.excelforum.com/showthread...hreadid=391960
>

[TommySzalapski]

That did it.

thanks