Get a list of open workbooks and choose one
 

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
--
Programmer on Budget

Get a list of open workbooks and choose one
 

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




"Budget Programmer" wrote in
message ...
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
--
Programmer on Budget

Get a list of open workbooks and choose one
 

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


"Budget Programmer" wrote in
message ...
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
--
Programmer on Budget

Get a list of open workbooks and choose one
 

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

"Budget Programmer" wrote:

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
--
Programmer on Budget

Get a list of open workbooks and choose one
 

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!
--
Programmer on Budget


"Chip Pearson" wrote:

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




On Thu, 13 May 2010 13:49:01 -0700, Budget Programmer
wrote:

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
.