worksheet_calculate event changes worksheets, but shouldn't

[Harold Good]

Hi,

The code below is in the Sheet1 (Budget) Module: The user is entering
data into the "Budget" worksheet. When this fires, it should hide some
rows on the Categories worksheet. It does work ok, however, everytime a
user enters a new figure on the Budget worksheet, it fires and
momentarily flashes over to the Categories worksheet. It is quite
distractive, especially when one has hundreds of figures to enter.

I set a trap to stop it on the top line of this code - the Private Sub
Worksheet_Calculate() line.

Then, when I enter a new number on the Budget page, it fires, and stops
on this top line of code. At that point, before any of the code below
has run, the screen has already flashed over to the Categories page and
waits there until I hit F8. Then when I hit F8 twice, after it executes
the second line (screen updating = false), then it switches back to the
Budget worksheet where it should be and remains there.

To be more precise, when it flashes to the Categories worksheet, what
actually shows on the screen is:
Top half of screen is the Budget page
Bottom half of screen is the Categories page.
It's almost like when a partial refresh happens sometimes. The Budget
tab is still active, not the Categories tab.

I'd surely appreciate any ideas you may have to stop this flashing
over. 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("Budget").Range("J111").Value > 1 Then
Sheets("Categories").Rows("32:33").EntireRow.Hidden = False
Else
If Sheets("Budget").Range("E30").Value = 0 Then
Sheets("Categories").Rows("32:33").EntireRow.Hidden = True
End If
End If
Application.EnableEvents = True
Application.ScreenUpdating = False
Sheets("Categories").Protect Password:="xxx"
End Sub

 

[Don Guillett]

I re-created your file and put the macro in another sheet module and did not
see a problem although I would have written it differently

You are not resetting screen updating to True at the end. Do you really want
this to fire on EVERY calculation or just when you do____________
If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.