Excel Timer with Sheet Toggle Help

[Tysone]

Here is my VB text:


Dim Nexttime

Sub Toggle_sheets()
Dim i
Nexttime = Now + TimeValue("00:00:30")
i = ActiveSheet.Index + 1
Range("b1").Select
ActiveCell.FormulaR1C1 = ""
If i > 4 Then i = 1
ActiveWorkbook.Worksheets(i).Activate
Application.OnTime Nexttime, "Toggle_sheets"
End Sub


And what I want to add is a timer inside of this so that:

Range("b1").Select
ActiveCell.FormulaR1C1 = ""

would happen every 00:00:01

Can someone help me?

Thanks


Tyson

 

[TH]

Try this link to an excellent description of OnTime and more.
Also try this from VBA HELP ANSWER WIZARD: Timer Function Example
You can also find the OnTime function in HELP. I think this may be the best
option so you can do other things in the worksheet while it updates every
second. Don't forget to Cancel the timer when in the Workbook_BeforeClose
event otherwise it will keep running! (Unless you quit Excel entirely.)

Terry

 

[TH]

Opps. Forgot the Link!

http://www.cpearson.com/excel/ontime.htm

 

[Bob Phillips]

Can I ask why you want this, if it happens every second, who is going to
change in the interim?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Tysone]

This is what it's for. I created a real time excel file that shows
all our sales people's revenue VS quota, that I display on a giant
plasma screen for them to look at. There will be no editing on this
workbook. All I do is take my laptop hook it up to the screen and let
it toggle. And why I want that action to take place every second is
because I have a timer on each page that I want to update every
second... Can someone help me make my formula happen??

 

[Bob Phillips]

How about something like

Sub Toggle_sheets()
Dim i As Long
Dim NextTime As Date
NextTime = Now + TimeValue("00:00:3")
'i = ActiveSheet.Index + 1
ResetCell

'If i > 4 Then i = 1
'ActiveWorkbook.Worksheets(i).Activate
MsgBox "Toggle"
Application.OnTime NextTime, "Toggle_sheets"
End Sub

Sub ResetCell()
Dim NextSec
NextSec = Now + TimeValue("00:00:01")
'Range("B1").FormulaR1C1 = ""
MsgBox "hello"
Application.OnTime NextSec, "ResetCell"
End Sub

be interesting to see what happens to the stack though.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Tysone]

Great - that link isn't exactly what I wanted, but I made it work.

Thanks

Tyson