Link to home
Start Free TrialLog in
Avatar of cpatte7372
cpatte7372Flag for United Kingdom of Great Britain and Northern Ireland

asked on

Excel Modification Request Pt2

Hello Experts

A very clever expert modified code that will allow certain values to be displayed at different time intervals.

The code is as follows:

Sub Make_Record(XLOG As String)
BinPath = ThisWorkbook.Path
BinFile = "ABC.TXT"
BinFileFullpath = BinPath & BinFile

            Open BinFileFullpath For Append As #1  'Change path & name to suit
            Write #1, XLOG
            Close 1
            ThisWorkbook.Sheets("mini sized DOW").Range("$AY$1").End(xlUp).Offset(1, 0).Value = XLOG
End Sub
Sub StartTimer()
myValue = ThisWorkbook.Sheets("mini sized DOW").Range("aw3")
Dim X() As String
Dim XTIME As String
Dim XLOG As String
DoEvents

If ThisWorkbook.Sheets("mini sized DOW").Range("aw3") = myValue Then
XTIME = Format(Now(), "hh:MM:ss")
    'If Mid(XTIME, 5, 1) = 4 Or Mid(XTIME, 5, 1) = 9 Then
    If Mid(XTIME, 5, 1) = 4 Or Mid(XTIME, 5, 1) = 9 Then
    XLOG = Format(Now(), "hh:MM:ss") & ": Pause " & myCount & " seconds, value' =" & ThisWorkbook.Sheets("mini sized DOW").Range("aw3")
    Make_Record 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


Everything works fine except that it only works if a manual change is made in aw3. I really need it to work dynamically.

I wonder if you could help?

Cheers

Carlton
dynamicv2.xlsm
Avatar of cpatte7372
cpatte7372
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

Hi Experts,

Anyone able to help with this???
You have a Worksheet_Calculate() event that already looks for changes in particular cells.  You just need to add a public variable holding the last AW3 value then check on changes made to AW3.  What is it you want done if that value has changed?

Looking at the code, above, there are two commands that don't make sense:

Sub StartTimer()
myValue = ThisWorkbook.Sheets("mini sized DOW").Range("aw3")

..
..

If ThisWorkbook.Sheets("mini sized DOW").Range("aw3") = myValue Then 'won't this always resolve to TRUE?  What are you trying to do, here?
..
..

Why do you need a timer for changes to AW3 when those changes can be checked on via the Worksheet_Calculate event?
Hi dlmille,

You won't be able to see what's happening with the spreadsheet, unless you're viewing the sheet at certain times e.g every, 04, 09, 14, 19 etc past the hour. So, if there was a value of 11111 in cell aw3 nothing should happen until the times mentioned. At those times, or should I say 'StartTimer' times the price will appear in AY2 and each time the price changes it will show how long that price has paused.

It works fine if I manually insert a number in aw3, but won't work if the number appears automatically.

I hope that makes sense

Cheers
ASKER CERTIFIED SOLUTION
Avatar of dlmille
dlmille
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
OK, going to do it now....
Wow! And just like magic it works.

Dave, once again U D Man......

Cheers mate.
Brilliant!