My simple MsgBox VBA stops the whole table if I don't click ok?

Question

Please take with me how I try to work out as succinctly as possible:

I have a basic trading table that includes prices for living houses, and if the price exceeds my target price for entering the cell, say, AB4, the text “BUY” is displayed. If I have a stock, and the price falls below my target, then “SELL” is displayed in the same cell. In any case, the spreadsheet will also automatically send the order to complete the trade.

All I wanted was msgbox to remind me to take notes whenever a signal appears. I need only one reminder, but I need it, because I always forget.

Having read 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 

It seemed like I was working, but as soon as I click OK, msgbox will appear again. As long as the text “BUY” or “SELL” is displayed, msgbox just won’t go away, no matter how many times I clicked it.

So, I searched again and found a way to make the msgbox message only 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 

Suppose you are working in charm EXCEPT mode, if I do not press "ok" to disappear msgbox, my whole table stops doing anything (i.e. prices are no longer updated, calculations are no longer performed, etc.) ! It just seems to me that I waited for me to click on "okay." This is a serious problem, because if I am gone when msgbox appears and the spreadsheet is stopped, then the price of another stock in my portfolio will reach a goal that the spreadsheet does not even know, let alone automatically cancel the order.

Why does this simple procedure stop the spreadsheet and how can I solve my original "simple" problem with msgbox? I do not want to use a conditional formatting route in a spreadsheet, because it is already cluttered with too many conditionally formatted cells.

Thank you guru!

Answer 1

It seems that the main problem is that the MsgBox does not shut down and disappears, because the message is only informative.

Using the code below, the Promped message will be disabled after 2 seconds and will allow end 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 problem.

Answer 2

If the Windows Messenger service is running (by default it was on all the machines I have ever tried), you can use the Shell Msg function yourself. This is similar to the @Miguel_Ryu method, with the exception that Shell will execute asynchronously - it is not completely blocked. The following example demonstrates (obviously, you'll want to replace "Comintern" with your Windows username):

 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 will see a pop-up message, and then you will see numbers scrolling as the loop progresses. This also has the advantage that the message is not limited by the execution time of your code. It will remain there until it is rejected, regardless of whether your code completes in the meantime, unless you give it a graph with the /time: parameter.

Even better, messages will add up. If you run the test code twice without discarding the first message, you will still get both of them.