Workbook_SheetCalculate

[stevebutler]

Is there a way to make this sub routine run every second calculation?

I have a code that prompts the user under certain conditions. but since the sheet makes 2 calculations at a time, it gives the same prompt twice. any ideas?

thanks
steve

 

[just_jon]

Just a quick thought -- could you use a Static Boolean variable, and have the event code

1] toggle it True-->False-->True...

2] execute the remaining code only if the variable were == True

 

[Greg Truby]

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)
    <SPAN style="color:#00007F">If</SPAN> Intersect(Target, Range("K24").Precedents) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN>
        Application.StatusBar = <SPAN style="color:#00007F">False</SPAN>
        <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    <SPAN style="color:#00007F">If</SPAN> Range("K24") > 24 <SPAN style="color:#00007F">Then</SPAN>
        MsgBox "Danger, danger, Will Robinson!", vbInformation, "The robot speaks"
    <SPAN style="color:#00007F">Else</SPAN>
        Application.StatusBar = "No danger.  BTW " & Target.Address(False, False) & " just changed."
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

</FONT>

HTH

 

[Greg Truby]

Oh, and I'd Name range("k24") and put the name in the double-quotes. That way if you insert or delete rows/columns above/left of K24, your code doesn't suddenly fall to pieces on you.

 

[Greg Truby]

Crud - BRB with something better. .Precedents won't work since some are located off-sheet (thanks for nothing, microsoft).

 

[Greg Truby]

Sorry, but this seems a snipe hunt. This code, which is basically what you posted, just cleaned up a smidge, works just fine for me. I only get one warning about exceeding the limit. And once I click "no", I get no further warnings. What, exactly is the problem? I wonder if you don't have some leftover code in the worksheet's module or something that is causing a second message.

<font face=Courier New><SPAN style="color:#00007F">Dim</SPAN> booNoWarnings <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN>

<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Workbook_SheetCalculate(<SPAN style="color:#00007F">ByVal</SPAN> Sh <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN>)

    <SPAN style="color:#00007F">Const</SPAN> c_intLimit <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN> = 24          <SPAN style="color:#007F00">' tons</SPAN>

    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> booNoWarnings <SPAN style="color:#00007F">Then</SPAN>
        <SPAN style="color:#00007F">If</SPAN> Range("KeyRange") > c_intLimit <SPAN style="color:#00007F">Then</SPAN>
            MsgBox "YOU HAVE EXCEEDED " & c_intLimit & " TON LIMIT"
            booNoWarnings = (vbNo = MsgBox("Do you want to receive further warnings?", vbYesNo))
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

 

[stevebutler]

thanks for your help guys....

greg,

it works when the user selects no. the issue is when he selects yes....

because now the warning comes up after every calculation. i just want it to come up when cell K26 changes.

 

[Tom Urtis]

In a new fresh standard module , enter

Public KayNumBefore#
Public KayNumAfter#



In the worksheet module holding cell K26 of interest:

Private Sub Worksheet_Calculate()
KayNumBefore = KayNumAfter
KayNumAfter = Range("K26").Value
If KayNumAfter > 24 Then MsgBox "Warning!!!"
KayNumAfter = Range("K26").Value
End Sub



Make sure you've previously deleted all other Calc event code in that sheet module so you don't get a compile error for ambiguous declaration.

Any help ??

 

[Greg Truby]

Ah - got it. Bit slow today!
<font face=Courier New><SPAN style="color:#00007F">Dim</SPAN> booNoWarnings <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN>

<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Workbook_SheetCalculate(<SPAN style="color:#00007F">ByVal</SPAN> Sh <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN>)

    <SPAN style="color:#00007F">Const</SPAN> c_intLimit <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN> = 24          <SPAN style="color:#007F00">' tons</SPAN>

    <SPAN style="color:#00007F">Static</SPAN> s_dblOldKey <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Double</SPAN>

    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> booNoWarnings <SPAN style="color:#00007F">Then</SPAN>
        <SPAN style="color:#00007F">If</SPAN> Range("KeyRange") > c_intLimit _
        And s_dblOldKey <> Range("KeyRange") <SPAN style="color:#00007F">Then</SPAN>
            MsgBox "YOU HAVE EXCEEDED " & c_intLimit & " TON LIMIT"
            booNoWarnings = (vbNo = MsgBox("Do you want to receive further warnings?", vbYesNo))
            s_dblOldKey = Range("KeyRange")
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

Hi Tom, unless I muffed the implementation, that didn't work so well for me. Still got message on calcs that did not impact the key range.

 

[Tom Urtis]

G-man:

Hmmm...worked for me based on my understanding of the question, only fired when K26 calculated greater than 24, which could well be an incorrect interpretation on my part, so I'll look forward to the yay or nay result on this one.




SB:

Edit, reading closer I see there are several calculations in one multiple-macro iteration, and the once-per-multiple-macro-execution was the issue. Sorry, look at Greg's, mine probably was not relevant to your situation. However, I'm curious as to what was so unique about what the nature of your project that required several independent calcs in one multiple macro cycle.

 

[stevebutler]

greg,

That worked perfect, thanks guys.

where do i go to learn all of these commands? i'm just learning, but there's so much syntax to remember. how did you guys learn all of this?