Popup with selectable list of open workbooks

[DThompson89]

I am trying to figure out how to get a popup with a list of open workbooks that is selectable, so that the selected workbook can be used to copy data into the original workbook containing the macro. I was able to do something similar with the worksheets, see the example below. Is it possible to do the same with all open workbooks? I've tried it a bunch of different ways, and keep getting errors.

Sub Example()

Dim ws As Worksheet
Application.CommandBars("Workbook Tabs").ShowPopup
Set ws = ActiveSheet

End Sub

 

[footoo]

Seeing All Open Workbook Names

Ever want to see a list of all the workbooks that are open? If you open more than nine, Excel only displays the first nine without requiring you to go to a different menu to find the others. To make your job easier, this tip explains how to see every open workbook.

excelribbon.tips.net

 

[footoo]

An alternative to the link :

Create a UserForm called OpenFiles.
Put on the UserForm the following:
• A list box (ListBox1)
• A button (CommandButton1) with the caption ‘OK’
• A button (CommandButton2) with the caption ‘Close’
• A button (CommandButton3) with the caption ‘Refresh’

Put the following macros in the UserForm module:

Option Explicit
Dim x#, f#, pth As Range, wb$

'Close button
Private Sub CommandButton2_Click()
Unload OpenFiles
End Sub

'OK Button
Private Sub CommandButton1_Click()
Call Activate_File
End Sub

'Refresh Button
Private Sub CommandButton3_Click()
Application.ScreenUpdating = False
Call Open_Files
End Sub

'List Box
Private Sub ListBox1_DblClick (ByVal Cancel As MSForms.ReturnBoolean)
Call Activate_File
End Sub
Sub Activate_File()
x = 0
For f = 0 To ListBox1.ListCount - 1
    If ListBox1.Selected(f) Then
        If ListBox1.Value <> "" Then
            x = x + 1
            Windows(ListBox1.Value).Activate
            Application.WindowState = xlNormal
            ListBox1.Selected(f) = False
            Exit For
        End If
    End If
Next
If x = 0 Then MsgBox "You have not selected a file to activate"
End Sub

To display the UserForm run this macro from a normal module:

Sub Open_Files()
Dim checkCell As Range, wb As Workbook, maxW#

Application.ScreenUpdating = False
Unload OpenFiles

For Each wb In Workbooks
    If Windows(wb.Name).Visible Then _
        OpenFiles.ListBox1.AddItem wb.Name
Next
OpenFiles.Show vbModeless

ActiveWindow.WindowState = xlNormal
OpenFiles.Top = 563
OpenFiles.Left = 1452 - 295

Application.ScreenUpdating = True
End Sub

 

[DThompson89]

Seeing All Open Workbook Names

Ever want to see a list of all the workbooks that are open? If you open more than nine, Excel only displays the first nine without requiring you to go to a different menu to find the others. To make your job easier, this tip explains how to see every open workbook.

excelribbon.tips.net

Thank you! This worked for what I needed.