ScreenUpdating Not Working

[Matthew Pfluger]

BTW, the search functionality on this forum seems to be screwed up right now.
It will not find any threads for any keywords on both of my machines here at
work.

Anyway, for some bizarre reason, Application.ScreenUpdating = False is not
turning off updating! Here is my code:

===============================================
Sub RebuildYearSheetsEx(ByRef rngHeadings As Range, ByRef rngDepts As Range,
ByRef rngSetting As Range)

' Set display properties
Application.ScreenUpdating = False

' Declare variables
Dim rngTemp As Range ' temporary range
variable
Dim iCount As Integer ' count variable
Dim wksTemp As Worksheet ' temporary sheet
variable

' Get list of "Year" sheets
' In future, if tool is expanded to allow for adding "Year" sheets on
' the fly, this code will detect all "Year" sheets
Dim colTasksSheets As Collection ' collection of "Year"
sheets
Set colTasksSheets = New Collection
Call GetListOfYearTaskSheets(colTasksSheets)


' Reset "Year" sheets
For Each wksTemp In colTasksSheets
' Unhide all columns
wksTemp.Cells.EntireColumn.Hidden = False
Next wksTemp


' Loop through list of departments settings
For iCount = 1 To rngSetting.Rows.Count

' Some values in the Settings range may be blank, so only look for
specifically FALSE values
If rngSetting.Cells(iCount, 1).Value = False Then

' Each FALSE value has a corresponding dept name. Search for dept
' name in column headers on template "Year" sheet.
Set rngTemp = rngHeadings.Find(What:=rngDepts.Cells(iCount,
1).Value, _
LookIn:=xlValues, LookAt:=xlPart)

' When dept heading is found, hide its columns on all "Year"
sheets
If Not (rngTemp Is Nothing) Then
For Each wksTemp In colTasksSheets
' Hide column
wksTemp.Columns(rngTemp.Column).EntireColumn.Hidden = True

' Also hide column to right since Department cells are 2
merged columns
wksTemp.Columns(rngTemp.Column + 1).EntireColumn.Hidden
= True
Next wksTemp
End If
End If
Next

' Reformat each "Year" sheet
For Each wksTemp In colTasksSheets
wksTemp.Activate
Call UpdateYearSheetFormatting
Next wksTemp

' Rebuild formulas since they are based on formatting
Application.CalculateFullRebuild
Application.ScreenUpdating = True

wksHours.Activate

End Sub
=========================================

Right after the "Application.ScreenUpdating = False" line, if I hover over
the property, the tooltip returns TRUE. If I check the property in the
Immediate window, it returns TRUE. If I type "Application.ScreenUpdating =
False" in the Immediate window, the property will then return FALSE in the
Immediate Window, but TRUE in the tooltip while hovering.

I have also tried cleaning the code with CodeCleaner. Didn't work.

I know Updating is off when it reaches the line "wksTemp.Activate" line.
The window flashes to the other worksheet and then back.

The subroutine called by this sub, "UpdateYearSheetFormatting", contains a
few lines of ".Select" since that was the only way I could figure out to
select all cells in columns below merged cells. However, when I comment out
the call to this subroutine, the same problem occurs.

On a blank worksheet, I tried this simple macro with the other workbook
still open:
Sub test()
Application.ScreenUpdating = False
For i = 1 To 1000
Sheets(2).Activate
Sheets(3).Activate
Next
Application.ScreenUpdating = True
End Sub

This works just fine, shutting of screenupdating as instructed. Anyone have
any suggestions? This seems like an easy problem, but it's stumping me.

Thanks,
Matthew Pfluger

 

[Jim Thomlinson]

I notice that you have calls to other procedures. If those procedures tun the
screen updating on then there is your issue...

Call GetListOfYearTaskSheets(colTasksSheets) 'here possibly???

 

[Matthew Pfluger]

No, the ScreenUpdating is still on before the other procedures are called.
Also, that subroutine is designed to be run with ScreenUpdating off.

 

[Jim Thomlinson]

Not sure if you caught my drift (I could still just be missing something). If
eithier of your procedures that you call toggle the screen update settings
then you are going to get flashing..

so if your sub looks like this then you have an issue
public sub SheetFormatting
application.screenupdating = false
'do stuff
application.screenupdating = true 'Problem here...
end sub

In any case you can just remove your application.screenupdating = true code
lines as this is a setting that automatically resets whenthe procedure is
done... As a matter of good housekeeping I always reset it but it is not
necessary.

 

[Matthew Pfluger]

Yes, I understand that. However, the program flashes before it even calls
the other subroutine. The code goes:

wksTemp.Activate
call OtherSub

As soon as I try to activate the other worksheet, the view flashes. The
same thing happens after CodeCleaning, restart, or closing the VBE.

I don't know what's going on here. I must have done this 50 times before,
and I've never had this issue.

matthew pfluger