Solved

Private Sub Is Triggered From A Different Worksheet

Posted on 2016-07-19
10
41 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
  • 5
  • 3
  • 2
10 Comments
 
LVL 49

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 18

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 1

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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
LVL 1

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 18

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 49

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 1

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 1

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 49

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 1

Author Closing Comment

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

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

832 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