?
Solved

New Excel Alert

Posted on 2011-10-27
11
Medium Priority
?
240 Views
Last Modified: 2012-06-27
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
 
0
Comment
Question by:cpatte7372
  • 6
  • 4
11 Comments
 
LVL 5

Expert Comment

by:sir plus
ID: 37036871
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
 
LVL 5

Accepted Solution

by:
sir plus earned 2000 total points
ID: 37036877
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
 

Author Comment

by:cpatte7372
ID: 37037149
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 5

Expert Comment

by:sir plus
ID: 37037335
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
 

Author Comment

by:cpatte7372
ID: 37037416
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
 

Author Comment

by:cpatte7372
ID: 37037607
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
 
LVL 26

Expert Comment

by:redmondb
ID: 37040499
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
 

Author Comment

by:cpatte7372
ID: 37044132
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
 

Author Comment

by:cpatte7372
ID: 37044472
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
 

Author Closing Comment

by:cpatte7372
ID: 37054428
THANKS
0
 
LVL 5

Expert Comment

by:sir plus
ID: 37056217
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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question