Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ScreenUpdating doesn't prevent updating screen on Worksheet_Calculate
Hi, I have the following code below. E30 is a formula on the Categories
worksheet that refers to the Budget worksheet. So when I make an entry in Budget worksheet, it triggers this code. But, unfortunately, the screen also updates and displays the Categories worksheet momentarily. Very distracting for every budget entry. I've put ScreenUpdating = false, but the ScreenUpdating happens before it gets to that line. Is there any way to prevent this happening? Thanks, Harold Private Sub Worksheet_Calculate() 'this hides the message rows on Categories page if Budget Total is greater than 1 Application.ScreenUpdating = False Sheets("Categories").Unprotect Password:="xxx" Application.EnableEvents = False If Sheets("Categories").Range("E30").Value 1 Then Sheets("Categories").Rows("32:33").EntireRow.Hidde n = False Else If Sheets("Categories").Range("E30").Value = 0 Then Sheets("Categories").Rows("32:33").EntireRow.Hidde n = True End If End If Application.EnableEvents = True Application.ScreenUpdating = True Sheets("Categories").Protect Password:="xxx" End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
ScreenUpdating doesn't prevent updating screen onWorksheet_Calculate
Maybe you could use a different event to trigger hiding the rows -
maybe worksheet activate on the Categories sheet? Thanks! Cliff Edwards |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
ScreenUpdating doesn't prevent updating screen on Worksheet_Calcul
Harold
Just a tip, rather than an answer to your question: whenever I use the "Application.EnableEvents = False" statement (I have to use it a lot), I put in an error trap in case an error occurs at a point before I've turned the Events back on. In some cases of course there is no potential for such an error, but if there is any chance at all, I don't take the chance. -- Time is just the thing that keeps everything from happening all at once. "Harold Good" wrote: Hi, I have the following code below. E30 is a formula on the Categories worksheet that refers to the Budget worksheet. So when I make an entry in Budget worksheet, it triggers this code. But, unfortunately, the screen also updates and displays the Categories worksheet momentarily. Very distracting for every budget entry. I've put ScreenUpdating = false, but the ScreenUpdating happens before it gets to that line. Is there any way to prevent this happening? Thanks, Harold Private Sub Worksheet_Calculate() 'this hides the message rows on Categories page if Budget Total is greater than 1 Application.ScreenUpdating = False Sheets("Categories").Unprotect Password:="xxx" Application.EnableEvents = False If Sheets("Categories").Range("E30").Value 1 Then Sheets("Categories").Rows("32:33").EntireRow.Hidde n = False Else If Sheets("Categories").Range("E30").Value = 0 Then Sheets("Categories").Rows("32:33").EntireRow.Hidde n = True End If End If Application.EnableEvents = True Application.ScreenUpdating = True Sheets("Categories").Protect Password:="xxx" End Sub . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
ScreenUpdating doesn't prevent updating screen on Worksheet_Ca
Further to my (incomplete) previous post about "Events Off", it's a setting
that stays the way it is for the remainder of your Excel "session", i.e. it stays "off" until you have closed all your Excel files and opened Excel again. That's why I use an error trap that turns Events back on; otherwise I have to restart Excel or run a little Sub that brings them back. -- Time is just the thing that keeps everything from happening all at once "Neptune Dinosaur" wrote: Harold Just a tip, rather than an answer to your question: whenever I use the "Application.EnableEvents = False" statement (I have to use it a lot), I put in an error trap in case an error occurs at a point before I've turned the Events back on. In some cases of course there is no potential for such an error, but if there is any chance at all, I don't take the chance. -- Time is just the thing that keeps everything from happening all at once. "Harold Good" wrote: Hi, I have the following code below. E30 is a formula on the Categories worksheet that refers to the Budget worksheet. So when I make an entry in Budget worksheet, it triggers this code. But, unfortunately, the screen also updates and displays the Categories worksheet momentarily. Very distracting for every budget entry. I've put ScreenUpdating = false, but the ScreenUpdating happens before it gets to that line. Is there any way to prevent this happening? Thanks, Harold Private Sub Worksheet_Calculate() 'this hides the message rows on Categories page if Budget Total is greater than 1 Application.ScreenUpdating = False Sheets("Categories").Unprotect Password:="xxx" Application.EnableEvents = False If Sheets("Categories").Range("E30").Value 1 Then Sheets("Categories").Rows("32:33").EntireRow.Hidde n = False Else If Sheets("Categories").Range("E30").Value = 0 Then Sheets("Categories").Rows("32:33").EntireRow.Hidde n = True End If End If Application.EnableEvents = True Application.ScreenUpdating = True Sheets("Categories").Protect Password:="xxx" End Sub . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Screen flicker w/ Screen-Updating = False? | Excel Programming | |||
Screen flikering (even with ScreenUpdating = False) | Excel Programming | |||
Screen still changes. Why ? (Application.Screenupdating = False not working | Excel Programming | |||
Application.ScreenUpdating = False still causing screen to flash... | Excel Programming | |||
ScreenUpdating=False doesnt stop screen flicker?? | Excel Programming |