asked on

New Excel Alert

Hello Experts,

I would be very grateful if someone could assist me with an highlight and sound alert when the following charactors appear:

:04:
:09:
:14:

This goes on but if you show me how to do for the above and should be able to take it from there.

There is already a formula in place that would log the formula should an event occur:

"26-10-2011 15:20:39.39 Highlight 10 seconds, value =8525"
"26-10-2011 15:20:44.44 Highlight 15 seconds, value =1140"
"26-10-2011 15:25:09.09 Highlight 10 seconds, value =8750"
"26-10-2011 15:25:15.15 Highlight 15 seconds, value =8596"

Now I would dearly like an alert if any of the following occurred instead of the above:

"26-10-2011 15:04:39.39 Highlight 10 seconds, value =8525"
"26-10-2011 15:09:44.44 Highlight 15 seconds, value =1140"
"26-10-2011 15:14:09.09 Highlight 10 seconds, value =8750"
"26-10-2011 15:19:15.15 Highlight 15 seconds, value =8596"

Notice
:04:
:09:
:14:
:19:

You will find from the following formula that everything has already been coded by an Expert called Brian. So, the logs and everything are already in place.

Sub StartTimer()
Dim xLog As String
DoEvents
If ThisWorkbook.Sheets("mini sized DOW").Range("aw3") = myValue Then
If myCount > 15 Then
'set lastAlert =2
If lastAlert <> 2 Then

lastAlert = 2
End If
DoEvents
ElseIf myCount = 15 Then
Call tenminalert(True)
Call shiftDown
'-- green highlite when over 15 seconds
ThisWorkbook.Sheets("mini sized DOW").Range("aw3").Interior.ColorIndex = 4
xLog = Format(Now, "dd-mm-yyyy hh:MM:ss.ss") & " Highlight 15 seconds, value =" & ThisWorkbook.Sheets("mini sized DOW").Range("aw3")
Open "C:\Users\User\Documents\ABC.txt" For Append As #1 'Change path & name to suit
Write #1, xLog
Close 1
ThisWorkbook.Sheets("mini sized DOW").Range("$AY$1048576").End(xlUp).Offset(1, 0).Value = xLog
ElseIf myCount > 10 Then
'set lastAlert =1
If lastAlert <> 1 Then

lastAlert = 1
End If
DoEvents
ElseIf myCount = 10 Then
Call fiveminalert(True)
Call shiftDown
'-- yellow highlite when over 10 seconds
ThisWorkbook.Sheets("mini sized DOW").Range("aw3").Interior.ColorIndex = 6
xLog = Format(Now, "dd-mm-yyyy hh:MM:ss.ss") & " Highlight 10 seconds, value =" & ThisWorkbook.Sheets("mini sized DOW").Range("aw3")
Open "C:\Users\User\Documents\ABC.txt" For Append As #1 'Change path & name to suit
Write #1, xLog
Close 1
ThisWorkbook.Sheets("mini sized DOW").Range("$AY$1048576").End(xlUp).Offset(1, 0).Value = xLog
End If
myCount = myCount + 1
Else
myCount = 1
ThisWorkbook.Sheets("mini sized DOW").Range("aw3").Interior.ColorIndex = xlNone
myValue = ThisWorkbook.Sheets("mini sized DOW").Range("aw3")
End If
myTime = Now + TimeValue("00:00:01")
Application.OnTime myTime, "StartTimer"
End Sub

I just need someone kind enough to modify the code to alert me via sound and highlight as described above.

Just by way of explanation, it is those times within the hour that I would consider making stock market trade.

Cheers

sir plus

Instead of the line
xLog = Format(Now, "dd-mm-yyyy hh:MM:ss.ss") & " Highlight 15 seconds, value =" & ThisWorkbook.Sheets("mini sized DOW").Range("aw3")
Insert the following

ASKER CERTIFIED SOLUTION

sir plus

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

cpatte7372

ASKER

Sirplus thanks for responding.

I will apply the change to the spreadsheet and test it when the market opens.

Hopefully, you'll be around at 14:30 GMT, 09:30 EDT, just in case it doesn't work (although I'm sure it will :-)

Thanks, really appreciate it.

Cheers

sir plus

That will make it beep
I need to know more about what u mean by highlight
Perhaps send a sheet/s with what yuo want formatted & I can make teh code fire

PS
The line
Case "4", "09", "14", "24"

should be
Case "04", "09", "14", "24"
as its formatted MM not M

PPS
The triger is the minute not the hour like i think your comment about 14:30 GMT implies......

PPPS

You can change the trigger to fire at 10 instead of 09 by changing the "09" to "10" for testing purposes

cpatte7372

ASKER

Hi sirplus.

Lets say I get the following:

"26-10-2011 15:09:44.44 Highlight 15 seconds, value =1140

With your code I should get a beep, correct?

In addition to the beep I would like the cell to highlighted so that I can distinguish if from all the other intervals.

I hope that makes sense

Unfortunately, I can't test it before the stock markets open to tell if it works.

Cheers

cpatte7372

ASKER

Hi sirplus

I'm getting the following error message when I replace code with your code

Compile error:

Duplicate declaration in scope.

Sub StartTimer()
Dim xLog As String
DoEvents
If ThisWorkbook.Sheets("mini sized DOW").Range("aw3") = myValue Then
If myCount > 15 Then
'set lastAlert =2
If lastAlert <> 2 Then

lastAlert = 2
End If
DoEvents
ElseIf myCount = 15 Then
'Call tenminalert(True)
'Call shiftDown
'-- green highlite when over 15 seconds
ThisWorkbook.Sheets("mini sized DOW").Range("aw3").Interior.ColorIndex = 4
xLog = Format(Now(), "dd-mm-yyyy hh:MM:ss.ss") ' & " Highlight 15 seconds, value' =" & ThisWorkbook.Sheets("mini sized DOW").Range("aw3")
Dim x() As String
x = Split(xLog, ":")
Select Case x(1)
Case "4", "09", "14", "24"
Beep
End Select
Open "C:\Users\User\Documents\ABC.txt" For Append As #1 'Change path & name to suit
Write #1, xLog
Close 1
ThisWorkbook.Sheets("mini sized DOW").Range("$AY$1048576").End(xlUp).Offset(1, 0).Value = xLog
ElseIf myCount > 10 Then
'set lastAlert =1
If lastAlert <> 1 Then

lastAlert = 1
End If
DoEvents
ElseIf myCount = 10 Then
'Call fiveminalert(True)
'Call shiftDown
'-- yellow highlite when over 10 seconds
ThisWorkbook.Sheets("mini sized DOW").Range("aw3").Interior.ColorIndex = 6
xLog = Format(Now(), "dd-mm-yyyy hh:MM:ss.ss") ' & " Highlight 15 seconds, value' =" & ThisWorkbook.Sheets("mini sized DOW").Range("aw3")
Dim x() As String
x = Split(xLog, ":")
Select Case x(1)
Case "4", "09", "14", "24"
Beep
End Select
Open "C:\Users\User\Documents\ABC.txt" For Append As #1 'Change path & name to suit
Write #1, xLog
Close 1
ThisWorkbook.Sheets("mini sized DOW").Range("$AY$1048576").End(xlUp).Offset(1, 0).Value = xLog
End If
myCount = myCount + 1
Else
myCount = 1
ThisWorkbook.Sheets("mini sized DOW").Range("aw3").Interior.ColorIndex = xlNone
myValue = ThisWorkbook.Sheets("mini sized DOW").Range("aw3")
End If
myTime = Now + TimeValue("00:00:01")
Application.OnTime myTime, "StartTimer"
End Sub

Can you help?

redmondb

Carlton,

Not for points.

The following line below appears twice so remove either one...

Dim x() As String

Open in new window

Regards,
Brian.

cpatte7372

ASKER

Hi guys,

I'm now only interested in having the situations where the following occurs being viewed in the spreadsheet;

Case "4", "09", "14", "24"

As you're aware, I get log in the spreadsheet everytime a 10min or 15min interval appears. Can you show me how to stop that logging to screen from the above formula?

I'm still happy for the intervals to be logged to the ABC.txt file, just not on the screen.

I think its just a case of commenting them out, but not sure.

Cheers

cpatte7372

ASKER

Sirplus/Brian

I put a comment on the following, however, by doing that the formula by sirplus won't work

ThisWorkbook.Sheets("mini sized DOW").Range("$AY$1048576").End(xlUp).Offset(1, 0).Value = xLog

I think the problem is, sirplus's formula won't work without the above. So not sure if there is a way to get sirplus to work without showing all the other intervals??

cpatte7372

ASKER

THANKS

sir plus

Here you go
Uncomment the lines as appropriate
Cheers

PS Change the times at the top in the constants

Sub StartTimer()
Dim xLog As String
Dim x() As String

Const cT1 = "04"
Const cT2 = "09"
Const cT3 = "14"
Const cT4 = "24"
   
   DoEvents
   If ThisWorkbook.Sheets("mini sized DOW").Range("aw3") = myValue Then
      If myCount > 15 Then
        'set lastAlert =2
        If lastAlert <> 2 Then
            
            
            lastAlert = 2
        End If
            DoEvents
      ElseIf myCount = 15 Then
      Call tenminalert(True)
    Call shiftDown
      
      xLog = Format(Now(), "dd-mm-yyyy hh:MM:ss.ss") ' & " Highlight 15 seconds, value' =" & ThisWorkbook.Sheets("mini sized DOW").Range("aw3")
        x = Split(xLog, ":")
'            ThisWorkbook.Sheets("mini sized DOW").Range("$AY$1048576").End(xlUp).Offset(1, 0).Value = xLog 'Uncomment this line to make it appear on the screen
        Select Case x(1)
        Case cT1, cT2, cT3, cT4
            Beep
'            ThisWorkbook.Sheets("mini sized DOW").Range("$AY$1048576").End(xlUp).Offset(0, 0).Interior.ColorIndex = 4'Uncomment this line to make it Highlight what appear on the screen
        End Select
      
            '-- green highlite when over 15 seconds
            ThisWorkbook.Sheets("mini sized DOW").Range("aw3").Interior.ColorIndex = 4
            xLog = xLog & " Highlight 15 seconds, value =" & ThisWorkbook.Sheets("mini sized DOW").Range("aw3")
            Open "C:\Users\User\Documents\ABC.txt" For Append As #1  'Change path & name to suit
            Write #1, xLog
            Close 1
      ElseIf myCount > 10 Then
        'set lastAlert =1
        If lastAlert <> 1 Then
            
            lastAlert = 1
        End If
              DoEvents
      ElseIf myCount = 10 Then
      Call fiveminalert(True)
    Call shiftDown
      xLog = Format(Now(), "dd-mm-yyyy hh:MM:ss.ss") ' & " Highlight 10 seconds, value' =" & ThisWorkbook.Sheets("mini sized DOW").Range("aw3")
        x = Split(xLog, ":")
'            ThisWorkbook.Sheets("mini sized DOW").Range("$AY$1048576").End(xlUp).Offset(1, 0).Value = xLog 'Uncomment this line to make it appear on the screen
        Select Case x(1)
        Case cT1, cT2, cT3, cT4
            Beep
'            ThisWorkbook.Sheets("mini sized DOW").Range("$AY$1048576").End(xlUp).Offset(0, 0).Interior.ColorIndex = 6'Uncomment this line to make it Highlight what appear on the screen
        End Select
            '-- yellow highlite when over 10 seconds
            ThisWorkbook.Sheets("mini sized DOW").Range("aw3").Interior.ColorIndex = 6
            xLog = xLog & " Highlight 10 seconds, value =" & ThisWorkbook.Sheets("mini sized DOW").Range("aw3")
            Open "C:\Users\User\Documents\ABC.txt" For Append As #1  'Change path & name to suit
            Write #1, xLog
            Close 1
      End If
      myCount = myCount + 1
   Else
      myCount = 1
      ThisWorkbook.Sheets("mini sized DOW").Range("aw3").Interior.ColorIndex = xlNone
      myValue = ThisWorkbook.Sheets("mini sized DOW").Range("aw3")
   End If
   myTime = Now + TimeValue("00:00:01")
   Application.OnTime myTime, "StartTimer"
End Sub

Open in new window