Macro to choose an open workbook and Worksheet

[DChambers]

Anyone have a macro that will display all the open workbooks and allow me to choose 1?
Then using the chosen workbook display all the worksheets and choose 1?

Thanks

 

[joefrench]

Do you want this displayed in a form? On a sheet?

 

[DChambers]

Was hoping to add this to some existing VBA code wbChoice as workbook, wsChoice as worksheet - so I thought maybe an inputbox or msgbox - something that would ask the question then disapear when answered. Is that possible?

 

[joefrench]

Hmm. A MsgBox is just going to ...well.....give you a message and an InputBox is just going to ask for some input. I think what you will want is a UserForm with two listboxes in it.

Try creating a userform with two listBoxes and add this code. It is not complete yet but I am working on activating the selected sheet

Private Sub ListBox1_Click()
Dim actvWb, wsName As String
Dim ws As Object
    For i = 0 To Me.ListBox1.ListCount - 1
        If Me.ListBox1.Selected(i) Then
            actvWb = Me.ListBox1.List(Me.ListBox1.ListIndex, 0)
        End If
    Next i
    
Application.Workbooks(actvWb).Activate



End Sub

Private Sub UserForm_Initialize()
Dim wbName As String
Dim wb As Object
Dim listQty As Integer
Dim rStart As Range
Set rStart = Sheet1.Range("A:A")

With Me.ListBox1
    For Each wb In Application.Workbooks
        wbName = wb.Name
        .AddItem wbName
    Next
End With
End Sub

 

[joefrench]

Try this:

Private Sub ListBox1_Click()
Dim actvWb, wsName As String
Dim ws As Worksheet
    For i = 0 To Me.ListBox1.ListCount - 1
        If Me.ListBox1.Selected(i) Then
            actvWb = Me.ListBox1.List(Me.ListBox1.ListIndex, 0)
        End If
    Next i
    
Application.Workbooks(actvWb).Activate

Me.ListBox2.Clear
With Me.ListBox2
    For Each ws In Application.Workbooks(actvWb).Worksheets
        wsName = ws.Name
        .AddItem wsName
    Next
End With

End Sub
'***********************************
Private Sub ListBox2_Click()
Dim actvWs As String
Dim ws As Worksheet
    For i = 0 To Me.ListBox2.ListCount - 1
        If Me.ListBox2.Selected(i) Then
            actvWs = Me.ListBox2.List(Me.ListBox2.ListIndex, 0)
        End If
    Next i
    
Sheets(actvWs).Activate
Unload Me
End Sub
'***********************************
Private Sub UserForm_Initialize()
Dim wbName As String
Dim wb As Object
Dim listQty As Integer
Dim rStart As Range
Set rStart = Sheet1.Range("A:A")

With Me.ListBox1
    For Each wb In Application.Workbooks
        wbName = wb.Name
        .AddItem wbName
    Next
End With
End Sub