My simple MsgBox VBA halts entire spreadsheet unless i click the "ok"?

Question

Please bear with me as i try to elaborate as succinctly as poss:

I have a basic trading spreadsheet that takes live prices in and if the price surpasses my target entry price a cell, say AB4, shows the text "BUY". If I own the stock and the price drops below my target the same cell shows "SELL". Either way, the spreadsheet will also automatically send out an order to execute the trade.

All I wanted was a msgbox to remind me to take notes whenever a signal appears. I only need the one reminder but I do need it as i always forget.

After reading many past posts here, this was my first attempt:

Private Sub worksheet_calculate()
 If Range("AB4").value = "BUY" Or Range("AB4").value = "SELL" Then
  MsgBox ("Record Catalyst")
 End If
End Sub

Seemed to work but as soon as I clicked the "ok" the msgbox would instantly reappear. So long as the "BUY" or "sell" text was showing the msgbox just wouldn't go away no matter the number of times i clicked it.

So i searched again and found a way to make the msgbox appear just once:

Private Sub worksheet_calculate()
If ActiveSheet.Range("BV4").Text = "Triggered" Then Exit Sub
  If Range("AB4").value = "BUY" Or Range("AB4").value = "SELL" Then
   MsgBox ("Record Catalyst")
ActiveSheet.Range("BV4") = "Triggered"
End If
End Sub

Seemed to work a charm EXCEPT if I don't click the "ok" to make the msgbox disappear my entire spreadsheet ceases to do anything (ie prices no longer update, calculations no longer perform etc)! It just seems to wait for me to click the blasted "ok" first. This is a major problem because if I'm not around when a msgbox appears and the spreadsheet is halted then should the price for another stock in my portfolio reach its target the spreadsheet wouldn't even know let alone shoot the order out automatically.

Why does this simple procedure stop the spreadsheet functioning and how can i solve my original "simple" msgbox problem? I don't want to go the conditional formatting route on the spreadsheet as its already cluttered with too many conditionally formatted cells.

Thank you gurus!

Answer 1

It seems that the main issue is that the MsgBox doesn't Time out and disappear as the message is only informative.

With the code below the Promped message will time out in 2 secs and allow the sub to end.

Private Sub worksheet_calculate()
    If ActiveSheet.Range("BV4").Text = "Triggered" Then Exit Sub
        If Range("AB4").Value = "BUY" Or Range("AB4").Value = "SELL" Then

            Set objShell = CreateObject("Wscript.Shell")

            intReturn = objShell.Popup("Record Catalyst", _
                2, , wshOk)

            If intReturn = 1 Then
                'do something if you click ok
            ElseIf intReturn = -1 Then
                'do something if times out alone, like record in a diff sheet
            End If

            ActiveSheet.Range("BV4") = "Triggered"

            Set objShell = Nothing

    End If
End Sub

Hope this solves your issue.

Answer 2

If the Windows messenger service is running (it has been by default on every machine I've ever tried this on) you can use the Shell function to Msg yourself. This is similar to @Miguel_Ryu's method with the exception that Shell will run asynchronously - it is completely non-blocking. The following sample demonstrates (obviously you'll want to replace 'Comintern' with your Windows user name):

Sub NonBlockingMessage()

    Dim i As Integer

    Shell "msg Comintern Look at me, I'm non-blocking!", vbHide

    For i = 1 To 10000
        Debug.Print i
    Next i

End Sub

You'll see the popup message and then see the scroll of numbers as the loop executes. This also has the advantage that the message isn't limited to the runtime of your code. It will stay there until dismissed regardless of whether your code completes in the meantime unless you give it a timout with the /time: parameter.

Even better, the messages will stack up. If you run the test code above twice without dismissing the first message, you'll still get both of them.