error in loop

[jtwork]

is there any way i get get excel to go to a certain sheet if a loop is broken.

I am using the goto command to continuoesly loop round changing the sheets as im displaying data onto a plasma tv. if a hit ESC i want it to go to a certain page

[Leith Ross]

Hello Jtwork,

Add this code to your loop. Change Worksheet_Name to the worksheet you want displayed when you press the escape key. Example: ShowSheet("Totals")

Please Login or Register  to view this content.

Sincerely,
Leith Ross

[jtwork]

doesnt seem to work, heres my code


Sub Refresh(sheet_to_show As String)
Worksheets(sheet_to_show).Activate
' Refresh Macro
' Macro recorded 16/01/2007 by Any Authorised Employee
'
Application.OnKey "{ESCAPE}", showsheet("acd stats")
'
Begin:
'Goto ACD stats
Range("a1").Select
Sheets("ACD Stats").Select

Application.OnKey "{ESCAPE}", showsheet("acd stats")

'Refresh Screen
Application.ScreenUpdating = True
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 1
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime
Application.ScreenUpdating = False

'Refresh Dialler Data
Module5.Dialler_Update

'Hold 6 seconds
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 5
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime


'goto dialler stats
Sheets("Dialler Stats").Select
Range("A1").Select

'Refresh Screen
Application.ScreenUpdating = True
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 1
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime
Application.ScreenUpdating = False

'Hold
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 25
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime


'refresh acd

Sheets("ACD Data").Activate
Range("A1").Select
Selection.QueryTable.Refresh BackgroundQuery:=False
Sheets("ACD Stats").Select

GoTo Begin

End Sub

[jtwork]

im still stuck on this one, is there an easy way to end a loop. even a key press that diverts to a secondary macro would be good.