Get a list of open workbooks and choose one


Budget Programmer


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.


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
choice = InputBox("Enter one of the workbooks below:" _
& vbCrLf & wbNm, "CHOOSE A WORKBOOK")
End Sub

Project Mangler


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

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
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"
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
Me.CommandButton2.Enabled = True
End If
End Sub
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub CommandButton2_Click()
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!

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question