Suppressing a screen

[ordnance1]

The code below is triggered by clicking on a button on my UserForm. It opens
EmployeeList.xlsm and runs a sort routine then saves and closes the file. My
problem is that the EmployeeList.xlsm briefly flashes on the screen during
the sort routine (code below). How can I prevent the EmployeeList.xlsm
from flashing on the screen?


Private Sub CommandButton4_Click()

Application.ScreenUpdating = False

Workbooks.Open
Filename:="P:\Dispatch\Vacation\Template\EmployeeList.xlsm"
ThisWorkbook.Activate

'Sort by Fixed Route Driver Names

SortModule.FRDSort

Workbooks("EmployeeList.xlsm").Save
Workbooks("EmployeeList.xlsm").Close

Unload EmployeeList

EmployeeList.Show

Application.ScreenUpdating = True

End Sub



Sub PTDSort()
'
' Macro1 Macro
' Macro recorded 12/20/2005 by Cathy Baker
'
'Sort by Paratransit Drivers Names


Dim bk As Workbook
Set bk = Workbooks("EmployeeList.xlsm")

Application.ScreenUpdating = False
Application.EnableEvents = False
bk.Worksheets("Employee_List").Unprotect
bk.Worksheets("Employee_List").Range("B1").Value = 1

With bk.Worksheets("Employee_List").Range("A1:Z300")
.Sort Key1:=bk.Worksheets("Employee_List").Range("F2:F300"),
Order1:=xlAscending, _
Key2:=bk.Worksheets("Employee_List").Range("D2300"),
Order2:=xlAscending, _
Key3:=bk.Worksheets("Employee_List").Range("A2"), Order3:=xlAscending,
_
Header:=xlYes, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _
DataOption3:=xlSortNormal
End With

bk.Worksheets("Employee_List").Protect
Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

 

[ozgrid.com]

Each time you select or activate, screenupdating is set back to True.

 

[aflatoon]

Your sort routine explicitly sets sreenupdating back to true. Remov
that line from the routine and you should be ok.


The code below is triggered by clicking on a button on my UserForm. I
opens

EmployeeList.xlsm and runs a sort routine then saves and closes th file. My
problem is that the EmployeeList.xlsm briefly flashes on the scree during
the sort routine (code below). How can I prevent th EmployeeList.xlsm
from flashing on the screen?


Private Sub CommandButton4_Click()

Application.ScreenUpdating = False

Workbooks.Open
Filename:="P:\Dispatch\Vacation\Template\EmployeeList.xlsm"
ThisWorkbook.Activate

'Sort by Fixed Route Driver Names

SortModule.FRDSort

Workbooks("EmployeeList.xlsm").Save
Workbooks("EmployeeList.xlsm").Close

Unload EmployeeList

EmployeeList.Show

Application.ScreenUpdating = True

End Sub



Sub PTDSort()
'
' Macro1 Macro
' Macro recorded 12/20/2005 by Cathy Baker
'
'Sort by Paratransit Drivers Names


Dim bk As Workbook
Set bk = Workbooks("EmployeeList.xlsm")

Application.ScreenUpdating = False
Application.EnableEvents = False
bk.Worksheets("Employee_List").Unprotect
bk.Worksheets("Employee_List").Range("B1").Value = 1

With bk.Worksheets("Employee_List").Range("A1:Z300")
.Sort Key1:=bk.Worksheets("Employee_List").Range("F2:F300"),
Order1:=xlAscending, _
Key2:=bk.Worksheets("Employee_List").Range("D2300"),
Order2:=xlAscending, _
Key3:=bk.Worksheets("Employee_List").Range("A2"), Order3:=xlAscending,
_
Header:=xlYes, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _
DataOption3:=xlSortNormal
End With

bk.Worksheets("Employee_List").Protect
Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

 

[aflatoon]

That is not the case in my experience. Can I ask what you base it on?


Each time you select or activate, screenupdating is set back to True.Macros/VBA and Excel Help' (http://www.ozgrid.com)

 

[Dave Peterson]

Any chance that there's a workbook_Open procedure in the employeelist.xlsm
workbook?

If there is, maybe it has some screenupdating code in it.

I wouldn't be surprised if it turned off screenupdating and then turned it back
on.

If you don't control that workbook, then I think the best you can do is live
with a slight flicker and add a screenupdating=false line after the open.

(or turn off events to avoid the workbook_Open event???)

application.enableevents = false
'your code to open
application.enableevents = true