You might try using the LockWindowUpdate API function. E.g.,

Private Declare Function LockWindowUpdate Lib "user32" ( _
ByVal hwndLock As Long) As Long

Sub AAA()
On Error GoTo ErrH:
LockWindowUpdate Application.Hwnd
'
' your code here
'
ErrH:
LockWindowUpdate 0&
End Sub

Be SURE (!) that you call LockWindowUpdate with a parameter of 0 to
unlock the window before your code terminates, either through a normal
exit or end of procedure or via an error handler. Do not leave the
window in a locked state.

The "Private Declare...." statement must be placed before and outside
of any Sub or Function procedure. If you use the Declare statement
within an object module (one of the Sheet modules, ThisWorkbook, a
Class module, or a userform's code module), it must be declared using
"Private", as shown above. If the Declare is in a regular code module,
you should use "Public" instead of "Private".

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)



On Fri, 28 Aug 2009 05:42:10 -0700 (PDT), Ray
wrote:

I'm using the code below to sort a range based on one which column
header (in row 3) that the user clicks on. I included
Application.ScreenUpdating=False in an attempt to prevent screen
flicker, but it's still happening ... did I somehow use it
incorrectly?

Any other improvements to the code are welcome also!

The Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'
' Macro to sort store performance, based on which KPI header was
clicked
'



Select Case Target.Address


Case "$H$3", "$I$3", "$J$3", "$K$3"
Application.ScreenUpdating = False
Range("D5:Y41").Select
ActiveWorkbook.Worksheets("Exec
Summary").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Exec
Summary").Sort.SortFields.Add Key:=Range( _
"K5:K41"), SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Exec Summary").Sort
.SetRange Range("D5:Y41")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("D1").Select

Case "$M$3", "$N$3", "$O$3"
Application.ScreenUpdating = False
Range("D5:Y41").Select
ActiveWorkbook.Worksheets("Exec
Summary").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Exec
Summary").Sort.SortFields.Add Key:=Range( _
"O5:O41"), SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Exec Summary").Sort
.SetRange Range("D5:Y41")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("D1").Select

Case "$Q$3", "$R$3", "$S$3"

Range("D5:Y41").Select
ActiveWorkbook.Worksheets("Exec
Summary").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Exec
Summary").Sort.SortFields.Add Key:=Range( _
"S5:S41"), SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Exec Summary").Sort
.SetRange Range("D5:Y41")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("D1").Select

Case "$U$3", "$V$3"

Range("D5:Y41").Select
ActiveWorkbook.Worksheets("Exec
Summary").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Exec
Summary").Sort.SortFields.Add Key:=Range( _
"V5:V41"), SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Exec Summary").Sort
.SetRange Range("D5:Y41")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("D1").Select

Case "$X$3", "$Y$3"

Range("D5:Y41").Select
ActiveWorkbook.Worksheets("Exec
Summary").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Exec
Summary").Sort.SortFields.Add Key:=Range( _
"Y5:Y41"), SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Exec Summary").Sort
.SetRange Range("D5:Y41")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("D1").Select

End Select

Application.ScreenUpdating = True

End Sub