Private Sub Is Triggered From A Different Worksheet
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
from the sub
Worksheets("Sheet4").Range
=M2-DAY(M2)+1
other cell formulas/values
Worksheets("Sheet4").Range
=IF(Calculations!B49=0,She
Calculations!B49
=COUNTIF('Score Card'!$E$19:$E$30,Calculat
'Score Card'!$E$19:$E$30
project phases entered manually
Calculations!$A49
"Design"
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 Subfrom the sub
Worksheets("Sheet4").Range
=M2-DAY(M2)+1
other cell formulas/values
Worksheets("Sheet4").Range
=IF(Calculations!B49=0,She
Calculations!B49
=COUNTIF('Score Card'!$E$19:$E$30,Calculat
'Score Card'!$E$19:$E$30
project phases entered manually
Calculations!$A49
"Design"
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
The left hand drop down in the VB Editor should read WorkSheet, if it doesn't then it is not in the correct module.
Private Sub Worksheet_calculate()The left hand drop down in the VB Editor should read WorkSheet, if it doesn't then it is not in the correct module.
ASKER
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.
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.
ASKER
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.
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.
Have you got two Calculate events in the same sheet module? That is not allowed and will error.
Does a change in the ScoreCard sheet has an effect on Sheet4 or not?
If yes that's the answer
If yes that's the answer
ASKER
Roy,
No, there's only one Calculate event in each of the two sheet modules I mentioned.
No, there's only one Calculate event in each of the two sheet modules I mentioned.
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
OK, I guess I can understand that logic. Thanks for the explanation and verification.
Private Sub Worksheet_calculate() is an event code.
It is triggered every time excel calculates the sheet
Regards