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

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

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
.

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
.