Does Application.ScreenUpdating = False work in a workbook change event?

[AmezNez]

I've written a code that hides certain tabs and rows based on the selection from a drop down list by using a worksheet_change event. However when it runs it flicks between screens which looks a bit rubbish.

I tried adding in the Application.ScreenUpdating = False however this made no difference so I'm wondering if it only works if it is placed in a module and not if its within a worksheet change event.

Does anyone know the answer and how I can make it work? Thanks a lot!!

 

[cytop]

Works fine... nonsense code below, but ScreenUpdating is turned off as you'd expect.

Private Sub Worksheet_Change(ByVal Target As Range)


    Dim lngrow As Long
    
    Application.ScreenUpdating = False
    
    For lngrow = 1 To 10000
        Rows(lngrow).EntireRow.Hidden = Not Rows(lngrow).EntireRow.Hidden
    Next
    
    Application.ScreenUpdating = True
    
End Sub

Post an anonymised copy of your workbook that displays the problem.

 

[dmt32]

It would be helpful if you could post your code but as a general thought you can in some cases, find that Screen Updating is set back to true before your macro completes. This can happen with macros that have the words Select and Activate used frequently - Turning screen updating off may not always be necessary if the code is written efficiently and excludes the use of Select and Activate operations.</SPAN>

 

[Jon von der Heyden]

Welcome to the forum.

Yes indeed ScreenUpdating isn't 100% reliable. For instance, looping worksheets and e.g. writing to a range in each still produces a flicker (worse so though without ScreenUpdating).

One way is to use Win API LockWindowUpdate.

Office 32bit example (place this in it's own module, or wrap it with other API calls if you have more):

Option Explicit

Public Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
                         (ByVal ClassName As String, ByVal WindowName As String) As Long

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

Public Sub FreezeScreen(ByVal blnOnOff As Boolean)
    Dim hWnd As Long

    If blnOnOff Then
        hWnd = FindWindow("XLMAIN", vbNullString)
        If hWnd > 0 Then Call LockWindowUpdate(hWnd)
    Else
        Call LockWindowUpdate(0)
    End If
End Sub

Then to disable screen flicker:

Call FreezeScreen(True)

And to reset:

Call FreezeScreen(False)

Edit: You can use the same method to freeze userforms and the VBE too; only one has to grab the respective windows (hWnd); whereas this example only grabs the main excel window.

 

[AmezNez]

Thanks for all your quick responses!!

Jon, I tried your method but it still flickered a bit but also froze the screen until I clicked out of the workbook and back in again.

Here is the code I'm trying to apply it to: (I'm not sure how to put it in a separate box like you are all doing)

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = ("$A$3") Then
Select Case Target.Range("A1").Value
Case "X"
If Sheets("Y Scheme specific items").Visible = True Then
Sheets("Y Scheme specific items").Select
ActiveWindow.SelectedSheets.Visible = False
End If
If Sheets(Z Scheme specific items").Visible = True Then
Sheets("Z Scheme specific items").Select
ActiveWindow.SelectedSheets.Visible = False
End If
Sheets("Tax Account").Activate
Range("WPFTransRows").Select
Selection.EntireRow.Hidden = True
Range("WPFOtherRows").Select
Selection.EntireRow.Hidden = True
Range("WPFDTTransRows").Select
Selection.EntireRow.Hidden = True
Range("WPFDTOtherRows").Select
Selection.EntireRow.Hidden = True
Case "Y"
If Sheets("Z Scheme specific items").Visible = True Then
Sheets("Z Scheme specific items").Select
ActiveWindow.SelectedSheets.Visible = False
End If
If Sheets("Y Scheme specific items").Visible = False Then
Sheets("Tax Account").Select
Sheets("Y Scheme specific items").Visible = True
End If
Sheets("Tax Account").Activate
Range("WPFTransRows").Select
Selection.EntireRow.Hidden = False
Range("WPFOtherRows").Select
Selection.EntireRow.Hidden = True
Sheets("Tax Account").Activate
Range("WPFDTTransRows").Select
Selection.EntireRow.Hidden = False
Range("WPFDTOtherRows").Select
Selection.EntireRow.Hidden = True
Case "Z"
If Sheets("Y Scheme specific items").Visible = True Then
Sheets("Y Scheme specific items").Select
ActiveWindow.SelectedSheets.Visible = False
End If
If Sheets("Z Scheme specific items").Visible = False Then
Sheets("Tax Account").Select
Sheets("Z Scheme specific items").Visible = True
End If
Sheets("Tax Account").Activate
Range("WPFTransRows").Select
Selection.EntireRow.Hidden = False
Range("WPFOtherRows").Select
Selection.EntireRow.Hidden = False
Sheets("Tax Account").Activate
Range("WPFDTTransRows").Select
Selection.EntireRow.Hidden = False
Range("WPFDTOtherRows").Select
Selection.EntireRow.Hidden = False
End Select
End If
Range("A11").Activate
End Sub


I'm sure its a very inefficient code as well but I'm new to macros so any other tips would be welcome!!

I also have a Worksheet calculate event that runs on another worksheet based on the same drop down which is adding to the flickering so perhaps there is something I need to do to this code as well?

Here it is:

Sub Worksheet_Calculate()

If Range("EntityGL").Value = "X" Then
Sheets("Gains & Losses").Activate
Range("WPFTransColumns").Select
Selection.EntireColumn.Hidden = True
Range("WPFOtherColumns").Select
Selection.EntireColumn.Hidden = True
End If
If Range("EntityGL").Value = "Y" Then
Sheets("Gains & Losses").Activate
Range("WPFTransColumns").Select
Selection.EntireColumn.Hidden = False
Range("WPFOtherColumns").Select
Selection.EntireColumn.Hidden = True
End If
If Range("EntityGL").Value = "Z" Then
Sheets("Gains & Losses").Activate
Range("WPFTransColumns").Select
Selection.EntireColumn.Hidden = False
Range("WPFOtherColumns").Select
Selection.EntireColumn.Hidden = False
End If
Range("B7").Activate
Sheets("Tax Account").Activate

End Sub

Thanks again for your help!

 

[AmezNez]

Sorry Jon, realised I hadn't put in the

Call FreezeScreen(False)

at the end. I've added that now to both macros and it works perfectly!! Thanks so much for your help!!

 

[Jon von der Heyden]

You're welcome!

BTW, the way to embed code in code tags is to type an opening code tag, followed by code, and then type a closing code tag.

E.g:

[code]
msgbox "well done!"
[/code]

 

[englandexpects]

Welcome to the forum.

Yes indeed ScreenUpdating isn't 100% reliable. For instance, looping worksheets and e.g. writing to a range in each still produces a flicker (worse so though without ScreenUpdating).

One way is to use Win API LockWindowUpdate.

Office 32bit example (place this in it's own module, or wrap it with other API calls if you have more):

Option Explicit

Public Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
                         (ByVal ClassName As String, ByVal WindowName As String) As Long

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

Public Sub FreezeScreen(ByVal blnOnOff As Boolean)
    Dim hWnd As Long

    If blnOnOff Then
        hWnd = FindWindow("XLMAIN", vbNullString)
        If hWnd > 0 Then Call LockWindowUpdate(hWnd)
    Else
        Call LockWindowUpdate(0)
    End If
End Sub

Then to disable screen flicker:

Call FreezeScreen(True)

And to reset:

Call FreezeScreen(False)

Edit: You can use the same method to freeze userforms and the VBE too; only one has to grab the respective windows (hWnd); whereas this example only grabs the main excel window.

Hello. I know this post is 4 years old, but I just wanted to say what a great solution this is! It has saved me hours of hunting around every instance of application.screenupdating and testing what causes a "false" to be ignored.

This solution is a killer one - it works 100% every time. Thank you so much for sharing this