Link to home
Start Free TrialLog in
Avatar of Juan Velasquez
Juan VelasquezFlag for United States of America

asked on

how to stop screen flicker when looping through worksheets

Hello,

Is there a way to eliminate screen flicker when looping through the worksheets in a workbook. I wrote the following code to loop through the worksheets in a workbook and set the columns of worksheets.  However, the client is complaining that the flicker is bothering his eyes.  Go figure.

Public Sub SetColumns(strType As String)
    ' Comments:
    ' Params  :
    ' Created : 08/05/13 11:50 JV
    ' Modified:
    
    On Error GoTo PROC_ERR
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim cell As Range
    
    Set wb = ThisWorkbook
    
    For Each ws In wb.Worksheets
        If Left(ws.Name, 3) = "WTS" Then
            ws.Unprotect
            If strType = "Construction" Then
                For Each cell In ws.Range("HoursWorked").Cells
                    Application.EnableEvents = False
                    cell.Locked = False
                    Application.EnableEvents = True
                Next
                For Each cell In ws.Range("WorkOrder").Cells
                    Application.EnableEvents = False
                    cell.value = ""
                    Application.EnableEvents = True
                Next
                For Each cell In ws.Range("TimeIn").Cells
                    Application.EnableEvents = False
                    cell.value = ""
                    Application.EnableEvents = True
                Next
                For Each cell In ws.Range("TimeOut").Cells
                    Application.EnableEvents = False
                    cell.value = ""
                    Application.EnableEvents = True
                Next
                
                ws.Range("C:F").EntireColumn.Hidden = True
            ElseIf strType = "Service" Then
                ws.Range("HoursWorked").Locked = False
                ws.Range("C:C").EntireColumn.Hidden = False
            End If
            ws.Protect
        End If
    Next
    
PROC_EXIT:
    Application.EnableEvents = True
    Exit Sub
    
PROC_ERR:
    MsgBox Err.Description, vbCritical, "basExcelUtilities.SetColumns"
    Resume PROC_EXIT
    
End Sub

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of nutsch
nutsch
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Juan Velasquez

ASKER

I did use application.screenupdating =false but it didn't seem to make much difference until I put it in the right location.   Thanks