Cannot get Application.Ontime to work

pooollito · Dec 21st 2019

Good morning, I have formulated the following code to run the macro every 2 minutes, and if there is a change in cells 03: O11, send me an email with Table4. I can't get it to work, let's see if you can help me out, please.

Code

Sub CellValueAutoIncr1 ()
    rTime = Now + TimeValue ("00:02:00")

    Application.OnTime EarliestTime: = rTime, Procedure: = "CellValueAutoIncr1", schedule: = True

    Application.EnableEvents = False

        If IsEmpty (Target) Then

        Application.EnableEvents = True
    
        Exit Sub

        End if

        If Not Intersect (Target, Range ("O3: O11")) Is Nothing Then

        If IsNumeric (Target) Then

        ActiveSheet.Range ("Table4"). Select

        ActiveWorkbook.EnvelopeVisible = True

        With ActiveSheet.MailEnvelope

        .Item.To = "[email protected]"

        .Item.Subject = "by email"

        .Introduction = "Example"

        .Item.Send

        End with

        End if
        End if
        Exit Sub

End Sub

Display More

Thank you so much for everything

**Carim** · Dec 21st 2019

Hello,

The code you posted is rather strange ...

As long as you do have a macro stored is a standard module called : CellValueAutoIncr1

you can test following event macro

Code

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("O3: O11")) Is Nothing Then Exit Sub
    If IsEmpty(Target) Then Exit Sub
    If Not IsNumeric(Target) Then Exit Sub
    
    rTime = Now + TimeValue("00:02:00")
    Application.OnTime EarliestTime:=rTime, Procedure:="CellValueAutoIncr1", schedule:=True
        ActiveSheet.Range("Table4").Select
        ActiveWorkbook.EnvelopeVisible = True
            With ActiveSheet.MailEnvelope
                .Item.To = "[email protected]"
                .Item.Subject = "by email"
                .Introduction = "Example"
                .Item.Send
            End With
    Application.EnableEvents = True
End Sub

Display More

Hope this will help

pooollito · Dec 21st 2019

Thanks for the code, and for the speed. But I don't have a stored macro, there is a standard module called: CellValueAutoIncr1. I just want to have this code.

How could I modify it?

Thank you very much again

**Carim** · Dec 21st 2019

Hello again,

The keyword Target is to be used within a worksheet event macro ...

For a standard macro, you should be using ActiveCell

Code

Sub CellValueAutoIncr1()
 If Not Intersect(ActiveCell, Range("O3: O11")) Is Nothing Then Exit Sub
 If IsEmpty(ActiveCell) Then Exit Sub
 If Not IsNumeric(ActiveCell) Then Exit Sub
 Dim rtime
 rtime = Now + TimeValue("00:02:00")
 Application.OnTime EarliestTime:=rtime, Procedure:="CellValueAutoIncr1", schedule:=True
 ActiveSheet.Range("Table4").Select
 ActiveWorkbook.EnvelopeVisible = True
    With ActiveSheet.MailEnvelope
        .Item.To = "[email protected]"
        .Item.Subject = "by email"
        .Introduction = "Example"
        .Item.Send
    End With
End Sub

Display More

To be tested ...

But this macro will NOT be triggered by a change ... you do have to run it ...

**AliGW** · Dec 21st 2019

Administrative Note:

Your thread title is extremely weak. Please try to summarise what you are trying to achieve next time to give members some idea of what the query is about.

pooollito · Dec 21st 2019

Sorry for not explaining clearly to me.

I have an excel sheet fed from external data that is updated every 2 minutes. These external data are edited by me and if they meet certain conditions, they are placed in Table 4 (O3: X11). What I want is that every 2 minutes the VBA looks at Rank O3: O11, and if there is any news, send me an email. I have a normal excel sheet, not a macro, maybe that's why it doesn't work for me ...

**AliGW** · Dec 21st 2019

Did you understand my comment about your thread title?

pooollito · Dec 21st 2019

Quote from AliGW

Administrative Note:

Your thread title is extremely weak. Please try to summarise what you are trying to achieve next time to give members some idea of what the query is about.

Yes, you want me to change the thread title. But I can't change it now, I create a new thread with a more descriptive title?

**AliGW** · Dec 21st 2019

No, I asked you to make sure that you use better titles in future.

No, do not open a new thread on this issue.

pooollito · Dec 21st 2019

Thanks for everything.

I have an excel sheet called Sheet1, it is powered by external data that is updated every 2 minutes. These external data are edited by me and if they meet certain conditions, they are placed in Table 4 (O3: X11). What I want is that every 2 minutes the VBA looks at Rank O3: O11, and if there is any news, send me an email. I have a normal excel sheet, not a macro, maybe that's why it doesn't work for me ...

I copy the code provided by Carim, but I don't get it to work. I would like to leave the VBA active so that it automatically checks every 2 minutes on sheet1 and if there is a change in O3: O11 automatically send me the email and continue executing the code for when there is another change to perform the same process again.

Thanks in advance.

**AliGW** · Dec 21st 2019

Did you save the workbook as an .xlsm workbook? If not, then you can't run it as a VBA-enabled workbook.

pooollito · Dec 21st 2019

Yes, I am saving it as .xlms, but I am changing the content of the O3: O11 cells and it does not send me any email ....

**Carim** · Dec 21st 2019

Hello,

Thanks for your explanations ..

Let's recap :

1. An Excel sheet called Sheet1 updated every 2 minutes by external engine ( such as RTD )

2. These external data get edited with formulas and they are replicated in Table 4 (O3: X11)

3. Objective is to leave Excel opened and have a macro running so that it checks every 2 minutes on sheet1 any change in range O3: O11 and automatically send you an alert email.

If my understanding is correct, the most important element is that you need an event to catch live update changes ...

This is not possible, since Excel can catch a manual input by the user ...but it cannot ' see ' a cell change by an external live process ...

Hope this clarifies

pooollito · Dec 21st 2019

Yes it is. You have perfectly understood my purpose. And you can think of a tool with which you can detect these automatic changes?

**Carim** · Dec 21st 2019

Well ... an untested idea could be to design a trick ... ( which would require some lengthy coding )

Say you have three distinct sheets in your workbook:

1. Sheet1 and Sheet2 can be populated with the very same update as the one you are currently getting

2. But, a macro could run every 2 minutes to 'freeze' the values only in Sheet2

3. Sheet3 would then work out all the differences between Sheet1 and Sheet2

4. Every 2 minutes, a worksheet calculate event could then check if all cells in Sheet3 are equal to 0 or not ...

5. and, then, a macro could automatically send an alert email

A lot of efforts ... to try to catch your live data updates ...

pooollito · Dec 21st 2019

Ok, thank you very much for the idea. I'm going to try to configure the trick you've told me, let's see how it goes

Cheers and thank you again.

pooollito · Dec 27th 2019

Good Morning. I am executing the code you put me above and I have a couple of doubts:

Quote from Carim

Well ... an untested idea could be to design a trick ... ( which would require some lengthy coding )

Say you have three distinct sheets in your workbook:

1. Sheet1 and Sheet2 can be populated with the very same update as the one you are currently getting
2. But, a macro could run every 2 minutes to 'freeze' the values only in Sheet2
3. Sheet3 would then work out all the differences between Sheet1 and Sheet2
4. Every 2 minutes, a worksheet calculate event could then check if all cells in Sheet3 are equal to 0 or not ...
5. and, then, a macro could automatically send an alert email

A lot of efforts ... to try to catch your live data updates ...

Display More

- I have 3 pages as you told me to compare the data. I wanted to predict when I invoke the range (O3: O11), I have to tell you which page I want you to look at, right?

- And then I just get him to send me the email once, I don't know how to make the macro run automatically every 2 minutes without me having to do anything.

Thanks I hope you can help me.

**Carim** · Dec 27th 2019

Hello,

Attached is a test file with an example on how to use Application.OnTime

Hope this will help

fzz · Dec 27th 2019

If you only need an e-mail when at least one cell in O3:O11 changes, better to use event handlers to e-mail the new cell values when they change whenever they change rather than checking every 2 minutes if there could be changes more frequently than that. If these cells usually contain constants (NOT formulas), a .Change event handler would be best. If they contain formulas, a .Calculate event handler would be best but would require caching the previous values for comparison.

**Carim** · Dec 27th 2019

Since Real time data feeds do not trigger worksheet change event ...

you could try : Application.OnData = "MyEmailMacro"

Cannot get Application.Ontime to work

Participate now!

Share