| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Screen flicker w/ Screen-Updating = False?
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Screen flicker w/ Screen-Updating = False?
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Screen flicker w/ Screen-Updating = False?
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Screen flicker w/ Screen-Updating = False?
You've got application.screenupdating = false in just 2 of your case
statements. Move it around a bit: Private Sub Worksheet_SelectionChange(ByVal Target As Range) application.screenupdating = false application.eneable events = false select case target.address case ... case ... end select application.screenupdating = false application.enableevents = false end sub I've put the enableevents stuff in there because in your case statements you have Range("D1").Select, which is going to trigger your macro again etc. Sam "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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Screen flicker w/ Screen-Updating = False?
Thanks for all of the responses ... it's very much appreciated.
I implemented Sam's solution first, as it was the easiest ... and worked perfectly. Chip, your solution is intriguing -- could you please explain what the functional difference (to end-user) is btw this solution and disabling screen-updating? |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Screen flicker w/ Screen-Updating = False?
Hi,
application.screenupdating = false is an instruction to excel not to update the window. If your code crashes, or hits a bug then it will automatically be turned back on. Chip's API function is an instruction to the the computer, not just excel, not to update the window. It's a lot stronger, but if you don't turn it off (or you hit a bug before you turn it off) you're knackered, hence his emphasis on calling it with parameter 0. "Ray" wrote: Thanks for all of the responses ... it's very much appreciated. I implemented Sam's solution first, as it was the easiest ... and worked perfectly. Chip, your solution is intriguing -- could you please explain what the functional difference (to end-user) is btw this solution and disabling screen-updating? |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Screen flicker w/ Screen-Updating = False?
Chip, your solution is intriguing -- could you please explain what the functional difference (to end-user) is btw this solution and disabling screen-updating? The primary difference is who is doing the blocking of the window updates. With Application.ScreenUpdating, Excel won't update the screen, assuming it obeys its own rules. The setting is internal to Excel, and for all I know, there are methods within Excel that ignore this setting. LockWindowUpdate operates at the much lower level of the operating system, and causes it to temporarily ignore (until the window is unlocked) any update messages sent to the locked window. Application.ScreenUpdating applies only to Excel and is automatically turned off when the chain of execution in VBA terminates and is control is returned to Excel. LockWindowUpdate can lock any window, though only one window may be locked at any one time, and the window lock will remain in effect until it is explicitly released. I use LockWindowUpdate to prevent screen flickering when running code under the VBA Extensibility model to modify VBE objects. Since the VBA editor doesn't respect the setting of Application.ScreenUpdating (as it should -- ScreenUpdating is part of Excel, not the general VBA language), I use LockWindowUpdate to suppress visual changes in the VBE. See the "Eliminating Screen Flicker During VBProject Code" section in the middle of my "Programming The VBA Editor" page at http://www.cpearson.com/Excel/vbe.aspx . In nearly all cases, the end user will not notice any difference between Application.ScreenUpdating and LockWindowUpdate. 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 07:37:49 -0700 (PDT), Ray wrote: Thanks for all of the responses ... it's very much appreciated. I implemented Sam's solution first, as it was the easiest ... and worked perfectly. Chip, your solution is intriguing -- could you please explain what the functional difference (to end-user) is btw this solution and disabling screen-updating? |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Screen flicker w/ Screen-Updating = False?
Just to add to Chip's response.
The window you get from excel when you print something doesn't respect the screenupdating setting (for one example). Chip's API routine will hide that from the user. But you have to make sure that your code turns this LockWindowUpdate API off. If not, you'll be rebooting and you may lose any changes in any open applications. Chip Pearson wrote: Chip, your solution is intriguing -- could you please explain what the functional difference (to end-user) is btw this solution and disabling screen-updating? The primary difference is who is doing the blocking of the window updates. With Application.ScreenUpdating, Excel won't update the screen, assuming it obeys its own rules. The setting is internal to Excel, and for all I know, there are methods within Excel that ignore this setting. LockWindowUpdate operates at the much lower level of the operating system, and causes it to temporarily ignore (until the window is unlocked) any update messages sent to the locked window. Application.ScreenUpdating applies only to Excel and is automatically turned off when the chain of execution in VBA terminates and is control is returned to Excel. LockWindowUpdate can lock any window, though only one window may be locked at any one time, and the window lock will remain in effect until it is explicitly released. I use LockWindowUpdate to prevent screen flickering when running code under the VBA Extensibility model to modify VBE objects. Since the VBA editor doesn't respect the setting of Application.ScreenUpdating (as it should -- ScreenUpdating is part of Excel, not the general VBA language), I use LockWindowUpdate to suppress visual changes in the VBE. See the "Eliminating Screen Flicker During VBProject Code" section in the middle of my "Programming The VBA Editor" page at http://www.cpearson.com/Excel/vbe.aspx . In nearly all cases, the end user will not notice any difference between Application.ScreenUpdating and LockWindowUpdate. 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 07:37:49 -0700 (PDT), Ray wrote: Thanks for all of the responses ... it's very much appreciated. I implemented Sam's solution first, as it was the easiest ... and worked perfectly. Chip, your solution is intriguing -- could you please explain what the functional difference (to end-user) is btw this solution and disabling screen-updating? -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Screen Flicker | Excel Discussion (Misc queries) | |||
| ScreenUpdating=False doesnt stop screen flicker?? | Excel Programming | |||
| screen flicker | Excel Programming | |||
| Screen flicker despite ScreenUpdate = False in the first line of code | Excel Programming | |||
| Screen Flicker | Excel Programming | |||
Linear Mode
