Excel Alerts

[davey11372]

I am trying to create a stock trading template in Excel. The template/worksheet will be minimized/running in the background while other applications run in the foreground.

When certain cells in the template/sheet populate certain values (based on formulas or by referencing other cells) - can alerts be created (ideally pop-up alerts/dialog box) so as to bring my notice to the sheet/template running in the background/minimized.

Please advise. Thank you.

 

[wigi]

You might want to look at the Worksheet_Calculate event in VBA.

Then, you can execute code each time the sheet is calculated.

Wigi

 

[davey11372]

Can I use the following to create Alerts/MsgBox:
.................................................................
Sub AlertBox()

If Range("C1:CK3").Value = "Txt1" Then
MsgBox ("Txt1")
If Range("C1:CK3").Value = "Txt2" Then
MsgBox ("Txt2")
If Range("C1:CK3").Value = "Txt3" Then
MsgBox ("Txt3")
If Range("C1:CK3").Value = "Txt4" Then
MsgBox ("Txt4")
If Range("C1:CK3").Value = "Txt5" Then
MsgBox ("Txt5")
If Range("C1:CK3").Value = "Txt6" Then
MsgBox ("Txt6")
End If

End Sub
..................................................
the above code will not create a MsgBox when I type in eg. "Txt1" in cell within the selected range - Please explain why?

I am trying to create alerts for an excel sheet that will be minimized/running in the background while other applications run in the foreground.

I want to create MsgBox Pop-Up alerts depending on whether any of the cells within the selected range have a specific value (in this text such as "buy", "sell" etc.).

Thank you

 

[paipimenta]

Also, by setting the Buttons attribute of the MsgBox to 4096 or "VbApplicationModal", you can force the MsgBox on top of all applications. You can still do things, but the MsgBox stays on top grayed.

E.g.:
MsgBox Prompt:="You better click OK, or you're stuck!", Buttons:=4096
MsgBox Prompt:="You better click OK, or you're stuck!", _
Buttons:=vbSystemModal

 

[paipimenta]

Comma separated method

MsgBox "MSFT up 1,000 points!", vbSystemModal, "StockAlertDialogTitle"

 

[davey11372]

Thank you, Paipimenta, that was one of the obstacles that I was anticipating.

Still waiting for some help on the scripting of the code or any improvements/modicfications to make it work.

 

[paipimenta]

Paste this into the worksheet (not module or workbook) where the range is.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C1:CK3")) Is Nothing Then
For Each myCell In Range("C1:CK3")
If myCell.Value = "Txt1" Then
MsgBox ("Txt1")
ElseIf myCell.Value = "Txt2" Then
MsgBox ("Txt2")
ElseIf myCell.Value = "Txt3" Then
MsgBox ("Txt3")
ElseIf myCell.Value = "Txt4" Then
MsgBox ("Txt4")
ElseIf myCell.Value = "Txt5" Then
MsgBox ("Txt5")
ElseIf myCell.Value = "Txt6" Then
MsgBox ("Txt6")
End If
Next myCell
End If
End Sub

By using the Worksheet_Change with an if statement seeing if what was changed even affects your range, you minimize comparison work to be done only when those cells change.

I would recommend creating a named range (Ctrl+F3), single selecting each cell in that range (comma separated, not colon), and using the following code.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("ChangingRange")) Is Nothing Then
For Each myCell In Range("ChangingRange")
If myCell.Value = "Txt1" Then
MsgBox (myCell.Address(False, False) & "= Txt1")
ElseIf myCell.Value = "Txt2" Then
MsgBox (myCell.Address(False, False) & "= Txt2")
ElseIf myCell.Value = "Txt3" Then
MsgBox (myCell.Address(False, False) & "= Txt3")
ElseIf myCell.Value = "Txt4" Then
MsgBox (myCell.Address(False, False) & "= Txt4")
ElseIf myCell.Value = "Txt5" Then
MsgBox (myCell.Address(False, False) & "= Txt5")
ElseIf myCell.Value = "Txt6" Then
MsgBox (myCell.Address(False, False) & "= Txt6")
End If
Next myCell
End If
End Sub


This will also let you know exactly what cell has changed.

You can have a large named range (i.e. "ChangingRange") refer to the entire collection of cells, while a different name (i.e. "MSFT" or "DOW") can refer to one or more of the subset of cells. For example, "ChangingRange" = C1,D1,E1,F1....... etc. until EK3
"Schwab Financial" = D1,D2,E1,E2

This way, you would be able to return the more specific name associated with that range instead of just the cell address (more on this coming....)

It would also be easier to adjust your code when changes are made (and if you just adjust the named range, no adjustment to your code is necessary... much more portable and readable)


Thanks to http://www.excelforum.com/excel-programming/391198-change-event-triggered-by-a-named-range.html for showing me how to react to a range specific worksheet change

Thanks to http://www.tek-tips.com/faqs.cfm?fid=5254 for showing me how to return the letter-number cell address of a cell.

 

[davey11372]

Thank you for the code. Is does give me a base to start with. A few related points would be:

1 - I tried using the Buttons:= 4096, but nothing happened, I could not minimize excel with the MsgBox showing, I had to close the MsgBox before trying to minimize excel.

2 - it seems the worksheet_functionality, works only if the user makes a 'manual' change to the defined cell range. If there are formulas in the cell range that change the cell values, it does not activate the MsgBox code (does not recognize the event change).

3 - if a change is made to one or more cells in the defined range, it activates the MsgBox code, however, with any subsequent changes, it re-triggers the MsgBox for all the cells including those that changed because of an earlier event/change. This point is kind of difficult to explain. In other words, it does not recognize only the new cells that changed with the new event, but all the cells that have changed with earlier events.

4 - is there a way to time the message box, say to auto-close after 15 seconds (a defined time interval).

Thanks for the code, again.

 

[paipimenta]

Instead of this...

MsgBox (myCell.Address(False, False) & "= Txt1")

use this....

CreateObject("WScript.Shell").Popup "Text 1", 15, "Message Title"