Is there a macro that will automatically do an F9 page refresh in Excel 2003, say, every 5 seconds so that a time calculation will update data fields?
I worked out the time and data update, but I’m fed up with pressing the F9 key to refresh it.
Droid<-------------------------- is a lazy so and so 
Thanks in advance
doesn’t autocalculate setting for the sheet do this? Do you need it to ticker time, or alter on event, I’d put in a function for cell click as a cheat that did the recalc.
DT.
It does after a change of cell value. The sheet I have calculates a time when I will have enough res to complete a build, but also what the current res value is if I am not logged in based on the original input values and time. It only recalcs this if I press F9, but it would be nice if the sheet auto updated like a ticker.
anychance of a copy of that sheet m8y?
Sure you can Scream. Go HERE
The sheet is protected to prevent overtyping formulas, but no password input. The calculations are on hidden rows and columns, but not marked, so it’s a bit messy. I’ll tidy it up when I have time, but you can play with it if you want. Needs Excel 2003 or better.
thinking I might be able to incorporate some of that into my sheet that I use
If you’re not sure about the calculations let me know. I had a mare of a time getting it to realise that we use a 24 hour clock and there will be an issue when some builds go over more than two days (I haven’t incorporated contingency for that yet, just for builds over more than one day)
Droid, Pm me your email and I’ll send you my sheet so you can have a look over it. I have something similar on one of the sheets where I could update it to work like yours.
Also, if you use messenger send me the addie you use for that.
Cheers
Steve
you two really need to get a hold of c# express :chuckle:
DT.
Probably would be a good idea. Got one spare?
Are you trying to say the formulas in my sheet get a little complex DT?
[QUOTE=DoubleTop;445695]it’s a freebie download
http://www.microsoft.com/express/vcsharp/
DT.[/QUOTE]
…Limited to 30 days use according to the video then you have to pay? Not made clear, but I’ll have a play anyways.
pay? pay?..
not sure of that concept
express edition are free, the text asking you to buy are for the full editions.
As you would expect, there are advantages to the full editions over the free, but most (not all) of the project I’ve looked at on places like codeproject.com all work in Express editions.
Having seen some of your work, the logic flow is all there but you can only go so far with Excel. Tools for the job and all that, it’s not as big a leap as you might think, especially if you were to go to the vb express edition over the c# one as the syntax will seem a little more familiar to you in terms of excel macros.
I’ve a ton of resources for helping with this sort of thing, from sample code to tutorial projects to run through. Loads of them are there on the MS site, but as with most MS, it takes some digging. Just remember, the more programmers on a OS/framework, the more OS sells 
and that’s the MS end-game.
There are of course, just as many logical programming languages for linux, and the fun in that realm get’s even more exciting when you can use the GUI to actually view + compile the GUI you are using 
DT.
You could place in the spreadsheet some VB Code like: http://www.vbaexpress.com/kb/getarticle.php?kb_id=242
Offset Timer Events
Code:
Public stopMe As Boolean
Public resetMe As Boolean
Public myVal As Variant
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Column = 1 Then
If Target.Value = myVal And Target.Value <> “” Then
'Changed
Dim startTime, finishTime, totalTime, timeRow
startTime = Timer
stopMe = False
resetMe = False
myTime = Target.Offset(, 2).Value
Target.Offset(, 1).Select
startMe:
DoEvents
timeRow = Target.Row
finishTime = Timer
totalTime = finishTime - startTime
Target.Offset(, 1).Value = Format(myTime + totalTime, “0.0000”) & " Seconds"
If resetMe = True Then
Target.Offset(, 1).Value = 0
Target.Offset(, 2).Value = 0
stopMe = True
End If
If Not stopMe = True Then
Target.Offset(, 2).Value = totalTime
Goto startMe
End If
Cancel = True
End
Else
'Not Changed
stopMe = True
Cancel = True
End If
End If
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
myVal = Target.Value
End Sub
Interesting code that Greg. Thanks. A little more than I needed really as all I need is something that after a few seconds does a page refresh, like when you press the F9 key. I’ve been around the usual excel forums, but they don’t seem to have anything similar.
/edit
Found something, but it still does not work:
In the ThisWorkbook module:
Private Sub Workbook_Open()
’ Put this procedure in ThisWorkbook module
' Run the procedure to refresh data when the workbook opened Call RefreshDataEnd Sub
and in a new module:
Public Sub RefreshData()
’ Add new module and put this procedure in it
Debug.Print "Data refreshed at " & Now ' ' This is where we put everything needed to refresh the data ' ' Schedules this procedure to be run 1 minute from now Application.OnTime Now + TimeValue("00:01:00"), "RefreshData"End Sub
Obviously missing something, but I don’t know what 
On the Refreshdata()
Dim I as Long
While I < 100000
If I = 99999 then
RefreshData
I = 1
End If
I = 1 +1
Wend
This will just keep in a loop, and then refresh…Remember the darn thing is going to be slow (using clock cycles) if your looking for simple this maybe the ticket.
Thanks Greg, I’ll give it a go
I’m a little late to the party but we’ve got an application here which I wrote years ago which uses similar functionality. I’ve stripped out the relevant bits, tidied things up and made a demo workbook which you should be able to work with: