worksheet_calculate event changes worksheets, but shouldn't
 

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.Hidde n = False
Else
If Sheets("Budget").Range("E30").Value = 0 Then
Sheets("Categories").Rows("32:33").EntireRow.Hidde n = True
End If
End If
Application.EnableEvents = True
Application.ScreenUpdating = False
Sheets("Categories").Protect Password:="xxx"
End Sub

worksheet_calculate event changes worksheets, but shouldn't
 

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.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Harold Good" wrote in message
...
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.Hidde n = False
Else
If Sheets("Budget").Range("E30").Value = 0 Then
Sheets("Categories").Rows("32:33").EntireRow.Hidde n = True
End If
End If
Application.EnableEvents = True
Application.ScreenUpdating = False
Sheets("Categories").Protect Password:="xxx"
End Sub