Suppressing a screen
 

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\EmployeeL ist.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:F30 0"),
Order1:=xlAscending, _
Key2:=bk.Worksheets("Employee_List").Range("D2:D30 0"),
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

Suppressing a screen
 

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





--
Regards
Dave Hawley
www.ozgrid.com



"ordnance1" wrote in message
...
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\EmployeeL ist.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:F30 0"),
Order1:=xlAscending, _
Key2:=bk.Worksheets("Employee_List").Range("D2:D30 0"),
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

Suppressing a screen
 

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


ordnance1;697720 Wrote:

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\EmployeeL ist.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:F30 0"),
Order1:=xlAscending, _
Key2:=bk.Worksheets("Employee_List").Range("D2:D30 0"),
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

Regards,
A.
------------------------------------------------------------------------
aflatoon's Profile: http://www.thecodecage.com/forumz/member.php?u=1501
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=195137

http://www.thecodecage.com/forumz

Suppressing a screen
 

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


ozgrid.com;697736 Wrote:

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





--
Regards
Dave Hawley
'Microsoft Excel® Training. Excel Templates, Add-ins, Formulas &
Macros/VBA and Excel Help' (http://www.ozgrid.com)




--
aflatoon

Regards,
A.
------------------------------------------------------------------------
aflatoon's Profile: http://www.thecodecage.com/forumz/member.php?u=1501
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=195137

http://www.thecodecage.com/forumz

Suppressing a screen
 

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



ordnance1 wrote:

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\EmployeeL ist.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:F30 0"),
Order1:=xlAscending, _
Key2:=bk.Worksheets("Employee_List").Range("D2:D30 0"),
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

--

Dave Peterson