Solved

Private Sub Is Triggered From A Different Worksheet

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

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 17

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
 
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 17

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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 48

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 48

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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
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;…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

758 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now