cpatte7372
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(x lUp).Offse t(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").Interio r.ColorInd ex = 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
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(x
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").Interio
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
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?
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?
ASKER
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
OK, going to do it now....
ASKER
Wow! And just like magic it works.
Dave, once again U D Man......
Cheers mate.
Dave, once again U D Man......
Cheers mate.
ASKER
Brilliant!
ASKER
Anyone able to help with this???