Need macro to automatically update

[A-Train]

Hi,

I need a mesage box to appear once cell A1 is over the value 50,000. I need the sheet to be in the background so I want the macro to be either contantly running or updating every few minutes.

I am able to get the popup box to work but not the time function. This is what I have so far.

Sub Macro5second()
If [A1].Value >= 50000 Then MsgBox "Time to trade boss!"
Application.OnTime Now() + TimeValue("00:05:00"), "Macro5second"
End Sub

Thanks

 

[sykes]

Can't you just put your line of code into the worksheet_change event, so that it fires each time A1 changes? :

Private Sub Worksheet_Change(ByVal Target As Range)
If [A1].Value >= 50000 Then MsgBox "Time to trade boss!"
End Sub

 

[A-Train]

Thanks Sykes, exactly what I needed.

 

[sykes]

Pleasure! Saves messing around with having a counter running etc, which can get messy.

 

[A-Train]

One issue with this script, the macro seems to run when any cell is updated not just A1. Any ideas?

 

[sykes]

Yes - it's the worksheet_change event so it will run every time something changes on the sheet.
If you only want it to respond to changes in A1, then do a test for A1 first:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$1" Then Exit Sub
If [A1].Value >= 50000 Then MsgBox "Time to trade boss!"
End Sub

 

[sykes]

By the way, I'm forgetting my manners! I'm afraid I didn't notice that you were new to Mr Excel, so welcome to the board!!

 

[A-Train]

Thanks man, appreciate it.

Ok, I'm getting close here.

The message box will appear when I manually update cell A1. However A1 is referencing other cells and the box isn't popping up when they update cell A1 via F9 or auto calculate.

Do you know how I can solve this?

Thanks