I have a spread sheet that I want to control from a userform. When
the person hits a botton in a userform it opens the spread sheet, goes
to sheet 1 for 2 seconds, then sheet 2 for 2 seconds, sheet 3 for 2
seconds, sheet 4 for 2 seconds and sheet 5 for 2 seconds. I would
like it to loop 3 times and then close the spread sheet.

Please help

will this work for you?

Option Explicit

Sub display_sheets()
Dim i As Long
Dim ws As Worksheet

For i = 1 To 3
For Each ws In ThisWorkbook.Worksheets
ws.Activate
Application.Wait (Now + TimeValue("0:00:03"))
Next
Next
ActiveWorkbook.Close savechanges:=False
End Sub

--


Gary


wrote in message
...
I have a spread sheet that I want to control from a userform. When
the person hits a botton in a userform it opens the spread sheet, goes
to sheet 1 for 2 seconds, then sheet 2 for 2 seconds, sheet 3 for 2
seconds, sheet 4 for 2 seconds and sheet 5 for 2 seconds. I would
like it to loop 3 times and then close the spread sheet.

Please help

Not really
I think it is trying to work but I have two different spread sheets
open and it is trying to cycle through through the wrong one

be best to supply some workbook names and somebody will help.

--


Gary


wrote in message
...
Not really
I think it is trying to work but I have two different spread sheets
open and it is trying to cycle through through the wrong one

Change thisworkbook to activeworkbook assuming that the book you want to loop
through is the active book...

For Each ws In Activeworkbook.Worksheets

--
HTH...

Jim Thomlinson


" wrote:

Not really
I think it is trying to work but I have two different spread sheets
open and it is trying to cycle through through the wrong one

try this, just change test.xls to whatever the name of the file you're opening
is. this works if they're in the same folder

Option Explicit

Sub display_sheets()
Dim i As Long
Dim fpath As String
Dim wb1 As Workbook
Dim wb2 As Workbook
fpath = ThisWorkbook.Path
Set wb1 = ThisWorkbook
Set wb2 = Workbooks.Open(fpath & "\test.xls")
Dim ws As Worksheet

For i = 1 To 3
For Each ws In wb2.Worksheets
ws.Activate
Application.Wait (Now + TimeValue("0:00:03"))
Next
Next
wb2.Close savechanges:=False
End Sub


--


Gary


wrote in message
...
Not really
I think it is trying to work but I have two different spread sheets
open and it is trying to cycle through through the wrong one

On Aug 8, 4:31*pm, "Gary Keramidas" <GKeramidasATmsn.com wrote:
try this, just change test.xls to whatever the name of the file you're opening
is. this works if they're in the same folder

Option Explicit

Sub display_sheets()
Dim i As Long
Dim fpath As String
Dim wb1 As Workbook
Dim wb2 As Workbook
fpath = ThisWorkbook.Path
Set wb1 = ThisWorkbook
Set wb2 = Workbooks.Open(fpath & "\test.xls")
Dim ws As Worksheet

For i = 1 To 3
* * * For Each ws In wb2.Worksheets
* * * * * * ws.Activate
* * * * * * Application.Wait (Now + TimeValue("0:00:03"))
* * * Next
Next
wb2.Close savechanges:=False
End Sub

--

Gary

wrote in message

...



Not really
I think it is trying to work but I have two different spread sheets
open and it is trying to cycle through through the wrong one- Hide quoted text -

- Show quoted text -

Got it

Thanks guys!!!!!!!!

try this from within the opened workbook

Sub gotosheetspause()
For i = 1 To 5
Sheets(i).Activate
Application.Wait (Now + TimeValue("0:00:02"))
'MsgBox Sheets(i).Name
Next i
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

wrote in message
...
I have a spread sheet that I want to control from a userform. When
the person hits a botton in a userform it opens the spread sheet, goes
to sheet 1 for 2 seconds, then sheet 2 for 2 seconds, sheet 3 for 2
seconds, sheet 4 for 2 seconds and sheet 5 for 2 seconds. I would
like it to loop 3 times and then close the spread sheet.

Please help