Two ways come to mind. First, display a numbered list of open
workbooks and let the user select a workbook by number:
Sub AAA()
Dim N As Long
Dim S As String
Dim WB As Workbook
For Each WB In Workbooks
N = N + 1
S = S & CStr(N) & " " & WB.Name & vbNewLine
Next WB
N = Application.InputBox(prompt:="Select workbook by number." &
vbNewLine & S, _
Type:=1)
If N <= 0 Or N > Workbooks.Count Then
MsgBox "Invalid selection"
Else
MsgBox "You selected: " & Workbooks(N).Name
End If
End Sub
The other way is to have the user click a cell on the workbook he
wants to use. E.g.,
Sub BBB()
Dim R As Range
Dim WB As Workbook
On Error Resume Next
Set R = Application.InputBox(prompt:="Click on the workbook",
Type:=8)
If Err.Number = 0 Then
Set WB = R.Parent.Parent
MsgBox "you clicked workbook: " & WB.Name
Else
MsgBox "invalid"
End If
End Sub
Note that in both procs, the Application.InputBox, rather than VBA's
native InputBox method is called. This makes a difference.
Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com