Get a list of open workbooks and choose one
in [Programming]
|
Prev: Converting a “Delete Column” Macro to a “Delete Row” Macro
Next: Going through Named Ranges in only specific Sheets in my WorkBook
From: Budget Programmer on 13 May 2010 16:49 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
From: JLGWhiz on 13 May 2010 18:24 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" <BudgetProgrammer(a)discussions.microsoft.com> wrote in message news:9474D741-0E5B-4E7C-A6E4-0C8DD0120B82(a)microsoft.com... > 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
From: Project Mangler on 13 May 2010 18:28 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" <BudgetProgrammer(a)discussions.microsoft.com> wrote in message news:9474D741-0E5B-4E7C-A6E4-0C8DD0120B82(a)microsoft.com... > 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
From: B Lynn B on 13 May 2010 18:54 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
From: Chip Pearson on 15 May 2010 10:33
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 <BudgetProgrammer(a)discussions.microsoft.com> 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 |