You are not disabling screenupdating in all the Cases, why not move it in
front of the Select Case
--
Gary''s Student - gsnu200902


"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