Worksheet_Calculate Event is also triggered in another Excel workbook?

I hope someone can help me with the following problem:

I have written a Worksheet_Calculate Event for a particular worksheet in a particular workbook. I assumed that this event will only be triggered in that particular worksheet and in that particular workbook. But it seems that this event is also triggerd when I switch to another Excel workbook that I have open, and change a cell overthere. I don't want this, and I don't understand why he is doing that. Please help.

Best Regards.
 
BoerdijkAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

bruintjeCommented:
Hello Boerdijk,

try to use it in the workbook event

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
  if sh.name = "MySheet" then
  'dosomething here
  end if
End Sub

you can test for the sh object first to check it's the correct sheet you want to act on

HAGD:O)Bruintje
0
bruintjeCommented:
Another way of preventing this is turning off autocalculate in excel, which is triggered also on close of a workbook . but then you need to turn it on again and every caculation you need has to be called on the worksheet.calculate event
0
BoerdijkAuthor Commented:
Hello bruintje

Thanks for your suggested solution, but unfortunately it doesn't work.

The workaround I found out myself in the meantime, is to add the condition:

If ActiveWorkbook.Name = "MyWorkbook" and ActiveSheet.Name = "MySheet" Then

'do something here

End if

That is also working in the Worksheet_Calculate event. But I still find it strange why this condition should be added if the event itself is already written in "MyWorkbook" and "MySheet".

I'm still puzzled why this event is triggered in another workbook if you don't add the condition.

If you or anyone has the explanation and/or a better solution to solve this, please tell me. Thanks

Regards.


0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

CleanupPingCommented:
Boerdijk:
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
Experts: Post your closing recommendations!  Who deserves points here?
0
DanRollinsCommented:
Boerdijk, an EE Moderator will handle this for you.
Moderator, my recommended disposition is:

    Refund points and save as a 0-pt PAQ.

DanRollins -- EE database cleanup volunteer
0
YensidModCommented:
Question is PAQ'd and points refunded.

YensidMod
Community Support Moderator @Experts Exchange
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.