Run macro after 10 seconds if cell doesn't change

Trickle

New Member
Joined
Feb 23, 2012
Messages
9
Hi All

I know you can pause a macro using Application.Wait, but I need to have the sheet available for cell AA10's contents to be updated by an external link, and only run the macro if the cell is not updated within 10 seconds.

Would Application.Wait make the sheet unavailable for external update, and if so, if there another way of doing it?

Many thanks in advance for any suggestions.

Trickle
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Visnyy

New Member
Joined
Sep 28, 2014
Messages
5
Can you please elaborate on external link?
you definitely have work around for that too. maybe second workbook to run macro and other to get feed from other ext links you're referring to.
 

BiocideJ

Well-known Member
Joined
Jan 23, 2012
Messages
1,733
The following will do what you are asking.
The MsgBox lines are simply for testing.

Code:
Sub Wait2Run()
    
    StartTime = Timer
    startAA10 = Range("AA10").Value
    Do While Timer < StartTime + 10    'wait 10 seconds max
        DoEvents    'allow updates, etc.
        If startAA10 <> Range("AA10").Value Then
            'the cell value changed
            MsgBox "Success", vbInformation, "=)"
            Exit Sub
        End If
    Loop
    
    Call FailMacro    'cell didn't change for 10 seconds
End Sub
Sub FailMacro()
    MsgBox "Failed", vbExclamation, "=("
End Sub
 

Trickle

New Member
Joined
Feb 23, 2012
Messages
9
The external link is to a betting scheduler, so I'm not sure the second spreadsheet is an option. Many thanks for the code - this looks ideal, and I'll test it tomorrow.

Thanks again for your time and expertise.

Trickle
 

Watch MrExcel Video

Forum statistics

Threads
1,108,506
Messages
5,523,305
Members
409,509
Latest member
CheekyDevil2386

This Week's Hot Topics

Top