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
 
cpatte7372Asked:
Who is Participating?
 
sir plusConnect With a Mentor Sales ManagementCommented:
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
 
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 in new window

0
 
sir plusSales ManagementCommented:
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
0
 
cpatte7372Author Commented:
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
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
sir plusSales ManagementCommented:
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
0
 
cpatte7372Author Commented:
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
0
 
cpatte7372Author Commented:
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?
0
 
redmondbCommented:
Carlton,

Not for points.

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

Dim x() As String

Open in new window


Regards,
Brian.
0
 
cpatte7372Author Commented:
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
0
 
cpatte7372Author Commented:
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??
0
 
cpatte7372Author Commented:
THANKS
0
 
sir plusSales ManagementCommented:
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

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.