Select from a list of workbooks that are open

[Budget Programmer]

Hello,
How do I display a list of workbooks that the user currently has open, and
choose one from that list, so that I can activate that workbook and continue
with macro processing.
Many Thanks for your help.

 

[JLGWhiz]

Did you read the response to your other post?

 

[Gord Dibben]

Window>Choose from list of open workbooks.

In 2007 that would be View>Window section>Switch Windows

Assumes workbooks have not been hidden.


Gord Dibben MS Excel MVP

 

[Chip Pearson]

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

 

[Budget Programmer]

Hi,
My posts seem to take a while to show up. There was never a delay
previously. I thought my request was lost. Chip answered it though. I'm
all set. Thanks.