Get a list of open workbooks and choose one

[Budget Programmer]

Hello,

I need to get a list of all the workbooks that the user currently has open,
give that list to the user, let them select one, and then Activate that
selected workbook and continue with processing.
I can generate a list of workbooks in a given directory, but the requirement
here is to get a list of workbooks that are currently open.

Many thanks for all your help.
Phil

 

[JLGWhiz]

This is not real fancy but it worked in a test.


Sub chooseWB()
Dim Wb As Workbook, wbNm As String, choice As String
For Each Wb In Application.Workbooks
wbNm = wbNm & Wb.Name & vbCrLf
Next
choice = InputBox("Enter one of the workbooks below:" _
& vbCrLf & wbNm, "CHOOSE A WORKBOOK")
Workbooks(choice).Activate
End Sub

 

[Project Mangler]

Phil,

Just looking at the currently open workbooks bit:

Sub showBooks()
Dim wb As Workbook
Dim nm As String

For Each wb In Workbooks
nm = wb.Name
Debug.Print nm
Next wb

End Sub

 

[B Lynn B]

I think you just want to loop through the Application.Workbooks collection.
Something like this, only maybe you'd want to add them to a listbox control
for selection instead of making a list on the sheet, but just for ease of
demonstration...

Sub WkbList()

Dim R as Long
Dim Wkb as Workbook

R = 1
For Each Wkb in Application.Workbooks
Cells(R, 1) = Wkb.Name
R = R + 1
Next Wkb

End Sub

 

[Chip Pearson]

The following function will prompt the user to select a workbook by
number and if a valid selection is made, return the name of the
workbook. If an invalid selection is made, the result is vbNullString.


Function PromptForWorkbook() As String
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 a workbook by number." & _
vbNewLine & S, Type:=1)
If N <= 0 Or N > Workbooks.Count Then
PromptForWorkbook = vbNullString
Else
PromptForWorkbook = Workbooks(N).Name
End If
End Function


You can use this in code like

Sub AAA()
Dim T As String
T = PromptForWorkbook
If T = vbNullString Then
MsgBox "user cancel"
Else
Workbooks(T).Activate
End If
End Sub

 

[Dave Peterson]

One more...

I created a small userform with a combobox and two commandbuttons.

This was the code behind the userform:

Option Explicit
Private Sub ComboBox1_Change()
If Me.ComboBox1.ListIndex < 0 Then
Me.CommandButton2.Enabled = False
Else
Me.CommandButton2.Enabled = True
End If
End Sub
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub CommandButton2_Click()
Application.Workbooks(Me.ComboBox1.Value).Activate
End Sub
Private Sub UserForm_Initialize()

Dim myWin As Window
Dim wkbk As Workbook

With Me.ComboBox1
.Style = fmStyleDropDownList
End With

With Me.CommandButton1
.Caption = "Cancel"
.Enabled = True
.Cancel = True
.TakeFocusOnClick = False
End With

With Me.CommandButton2
.Enabled = False
.Default = True
.Caption = "Activate Workbook"
.TakeFocusOnClick = False
End With

Me.Caption = "Please select a workbook"

For Each wkbk In Application.Workbooks
For Each myWin In wkbk.Windows
If myWin.Visible = True Then
Me.ComboBox1.AddItem wkbk.Name
Exit For
End If
Next myWin
Next wkbk

End Sub

 

[Budget Programmer]

Hi Chip,
Sorry it took me a while to get back to you. My priorities were changed for
a little while.
Your suggestion did the trick. Many Thanks!