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

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
    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?


Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

cpatte7372Author Commented:
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?
cpatte7372Author Commented:
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

Become a Microsoft Certified Solutions Expert

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

Do me a favor.  Comment out the

myValue = line right under the Sub StartTimer() and let me know if it still works correctly when you manually make a change.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
cpatte7372Author Commented:
OK, going to do it now....
cpatte7372Author Commented:
Wow! And just like magic it works.

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

Cheers mate.
cpatte7372Author Commented:
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.