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

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

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

--
TommySzalapsk
-----------------------------------------------------------------------
TommySzalapski's Profile: http://www.excelforum.com/member.php...fo&userid=2556
View this thread: http://www.excelforum.com/showthread.php?threadid=39196

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

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

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

--
Regards,
Tom Ogilvy

"TommySzalapski"
<TommySzalapski.1t56yn_1122995159.7006@excelforu m-nospam.com wrote in
message news:TommySzalapski.1t56yn_1122995159.7006@excelfo rum-nospam.com...

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

That did it.

thanks


--
TommySzalapski
------------------------------------------------------------------------
TommySzalapski's Profile: http://www.excelforum.com/member.php...o&userid=25561
View this thread: http://www.excelforum.com/showthread...hreadid=391960