Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Private Sub Is Triggered From A Different Worksheet

Posted on 2016-07-19
10
Medium Priority
?
54 Views
Last Modified: 2016-07-20
This is an inherited workbook, not my code, but I want to understand why this is triggered by a change on the Score Card worksheet if it is a private sub on the Sheet4 worksheet.
The Undo stack is cleared by this sub, and I've read enough to accept that losing Undo happens with some events, but why on a different worksheet?

sub on Sheet4
Private Sub Worksheet_calculate()
    Dim cht As Chart

    Set cht = Worksheets("Sheet4").ChartObjects(1).Chart

    cht.Axes(xlValue).MinimumScale = Worksheets("Sheet4").Range("M3").Value
    cht.Axes(xlValue).MaximumScale = Worksheets("Sheet4").Range("N4").Value
    cht.Axes(xlValue).MajorUnit = Worksheets("Sheet4").Range("O4").Value

End Sub

Open in new window


from the sub
Worksheets("Sheet4").Range("M3").Value
=M2-DAY(M2)+1

other cell formulas/values
Worksheets("Sheet4").Range("M2").Value
=IF(Calculations!B49=0,Sheet4!B3-90,Sheet4!B2)

Calculations!B49
=COUNTIF('Score Card'!$E$19:$E$30,Calculations!$A49)

'Score Card'!$E$19:$E$30
project phases entered manually

Calculations!$A49
"Design"
0
Comment
Question by:Alan Varga
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
10 Comments
 
LVL 53

Expert Comment

by:Rgonzo1971
ID: 41719500
Hi,

Private Sub Worksheet_calculate() is an event code.
It is triggered every time excel calculates the sheet

Regards
0
 
LVL 22

Expert Comment

by:Roy Cox
ID: 41719553
Are you sure that is the correct module. It looks like it may have been hand typed because the c in Calculate is Lower Case

Private Sub Worksheet_calculate()

Open in new window


The left hand drop down in the VB Editor should read WorkSheet, if it doesn't then it is not in the correct module.

2016-07-19_174137.png
0
 
LVL 2

Author Comment

by:Alan Varga
ID: 41719690
The behavior hasn't changed after changing the punctuation.  I know this is the offending code, because I set a breakpoint and verified a halt after typing in a value on the Score Card worksheet.

This code is in the Sheet object, not a standalone module.  There is code for another sheet with a similar sub (except that references the ChartObjects name explicitly ("Chart 4"); the author apparently wanted to update some Gantt chart ranges in two different worksheets if any of the dates changed.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 2

Author Comment

by:Alan Varga
ID: 41719725
The behavior hasn't changed after changing the punctuation.  I know this is the offending code, because I set a breakpoint and verified a halt after typing in a value on the Score Card worksheet.

This code is in the Sheet object, not a standalone module.  There is code for another sheet with a similar sub (except that references the ChartObjects name explicitly ("Chart 4"); the author apparently wanted to update some Gantt chart ranges in two different worksheets if any of the dates changed.
0
 
LVL 22

Expert Comment

by:Roy Cox
ID: 41720183
Have you got two Calculate events in the same sheet module? That is not allowed and will error.
0
 
LVL 53

Expert Comment

by:Rgonzo1971
ID: 41720204
Does a change in the ScoreCard sheet has an effect on Sheet4 or not?
If yes that's the answer
0
 
LVL 2

Author Comment

by:Alan Varga
ID: 41721009
Roy,
No, there's only one Calculate event in each of the two sheet modules I mentioned.
0
 
LVL 2

Author Comment

by:Alan Varga
ID: 41721013
RGonzo,
Yes, a change to the correct cell on the ScoreCard sheet could trigger a change on the metrics on Sheet4.  I would think Excel would have a record of triggering cells in memory and only fire the macro if one of those cells was changed; if any other cell, the event would be ignored.
0
 
LVL 53

Accepted Solution

by:
Rgonzo1971 earned 2000 total points
ID: 41721063
a change to the correct cell on the ScoreCard sheet could trigger a change on the metrics on Sheet4
it does not necessarily change the result but it's calculating it, therefore the event
like =IF('ScoreCard'!A1>10,"Bigger than 10","Not bigger than 10") if you change the value of 'ScoreCard'!A1 from 4 to 6, it does not change the result but it will recalculated
0
 
LVL 2

Author Closing Comment

by:Alan Varga
ID: 41721152
OK, I guess I can understand that logic.  Thanks for the explanation and verification.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

661 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question