//форма
Public SelectedWorkBook As workBook

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Dim SelectedName As String
    SelectedName = Me.ListBox1.list(Me.ListBox1.ListIndex)
    Set SelectedWorkBook = Workbooks(SelectedName)
    Me.Hide
End Sub

Private Sub UserForm_Initialize()
    Set SelectedName = Nothing
    Dim wb As workBook
    For Each wb In Workbooks
        If Not (wb.Name = ThisWorkbook.Name) Then
            Me.ListBox1.AddItem wb.Name
        End If
    DoEvents
    Next
End Sub

//модуль
Public Function GetWorkBookFromForm() As workBook
    UserForm1.Show
    If Not (UserForm1.SelectedWorkBook Is Nothing) Then
        Set GetWorkBookFromForm = UserForm1.SelectedWorkBook
    End If
    Unload UserForm1
End Function

Public Sub Test1()
    Set targetBook = GetWorkBookFromForm()
    If Not (targetBook Is Nothing) Then MsgBox targetBook.Name
End Sub