Solved

Private Sub Is Triggered From A Different Worksheet

Posted on 2016-07-19
10
45 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:VieleFragen
[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 51

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 19

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:VieleFragen
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
Independent Software Vendors: 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:VieleFragen
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 19

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 51

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:VieleFragen
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:VieleFragen
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 51

Accepted Solution

by:
Rgonzo1971 earned 500 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:VieleFragen
ID: 41721152
OK, I guess I can understand that logic.  Thanks for the explanation and verification.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

738 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