Scroll through worksheets then select one

[DChambers]

My eventual goal is to create a function that will scroll through every worksheet displaying them 1 at a time then being able to select one. -

That being said here is where I currently stand: I have a function that gives me a comma seperated string of the worksheet names, so I decided to add a section that would display the current worksheet and wait for my entry in the inputbox before continuing to the next worksheet, but for some reason when I try to select the worksheet I bounce out of the function.

Any Ideas?

Function GetwsNames(sWBName) As String
'
' Return a comma seperated string of all the Worksheet Names in the Workbook
'
Dim answer As String
Dim ws As Worksheet
Dim sWSNames As String
With Workbooks.Open(sWBName)
    For Each ws In .Sheets
        If sWSNames = "" Then
            sWSNames = ws.Name
        Else: sWSNames = sWSNames & "," & ws.Name
        End If
        'the following should display each worksheet as it is selected
        'I have tried many combinations of code to get this to work
        'and am now trying in round about ways
        answer = ws.Name
        Worksheets(answer).Cells(1, 1).Activate
        Range("A1").Select
        answer = Application.InputBox("Press any key", "Waiting", Type:=2)
    Next
End With
GetwsNames = sWSNames
End Function

 

[John_w]

Option Explicit

Sub Test()
    MsgBox GetwsNames("C:\temp\excel\book1.xls")
End Sub

Function GetwsNames(sWBName As String) As String
'
' Return a comma seperated string of all the Worksheet Names in the Workbook
'
Dim answer As Integer
Dim ws As Worksheet
Dim sWSNames As String
Dim wb As Workbook

Set wb = Workbooks.Open(sWBName)
With wb
    For Each ws In .Sheets
        If sWSNames = "" Then
            sWSNames = ws.Name
        Else
            sWSNames = sWSNames & "," & ws.Name
        End If
        ws.Activate
        answer = MsgBox("Press any key")
    Next
End With
GetwsNames = sWSNames
End Function

 

[Kenneth Hobson]

A popup would seem easiest.
http://www.mrexcel.com/forum/showthread.php?t=328062

 

[DChambers]

John, that was what I was looking for! Thank you.

Kenneth, I have never seen that way before ! Very Cool ! played with it overnight and have learned something new! Thanks, Only problem is that I need to have the operator see each worksheet before choosing.

 

[Kenneth Hobson]

If they just needed a glimpse of the data one could use a control on a userform to show a snapshot of some range.

If you are showing each sheet at some interval, you might check for the activecell not being A1 which you could set as an early trigger.