Solved

Private Sub Is Triggered From A Different Worksheet

Posted on 2016-07-19
10
48 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 52

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 20

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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
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 20

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 52

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 52

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:Alan Varga
ID: 41721152
OK, I guess I can understand that logic.  Thanks for the explanation and verification.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

635 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