Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dialog Box List of Current Open Workbooks
I am lookng for an example of VBA code that will produce a dialog box of all
the currently opened Excel Workbooks and will allow the user to select which of the open Workbooks they would like to perform an action on. Can anyone point me in the right direction? I have tried multiple Google searches and the closest I could come is code to open Workbooks from the directory. Thank you, Frank |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dialog Box List of Current Open Workbooks
Frank,
Create a UserForm with a list box and a command button, in put the following code in the form's code module: Private Sub CommandButton1_Click() Dim WB As Workbook With Me.ListBox1 Set WB = Workbooks(.List(.ListIndex)) ' do something with WB End With End Sub Private Sub UserForm_Initialize() Dim WB As Workbook With Me.ListBox1 For Each WB In Workbooks .AddItem WB.Name Next WB .ListIndex = 0 End With End Sub -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Frank & Pam Hayes" wrote in message ... I am lookng for an example of VBA code that will produce a dialog box of all the currently opened Excel Workbooks and will allow the user to select which of the open Workbooks they would like to perform an action on. Can anyone point me in the right direction? I have tried multiple Google searches and the closest I could come is code to open Workbooks from the directory. Thank you, Frank |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dialog Box List of Current Open Workbooks
Frank,
Here is an alternative to Chip's suggestion, which uses the technique John Walkenbach gives in http://j-walk.com/ss/excel/tips/tip48.htm. John uses the print dialog, and I have adapted this, although it is not necessarily to print. You would need to ass your processing to this code. Option Explicit Sub SelectSheets() Dim i As Integer Dim TopPos As Integer Dim iBooks As Integer Dim PrintDlg As DialogSheet Dim CurrentSheet As Worksheet Dim cb As CheckBox Application.ScreenUpdating = False ' Check for protected workbook If ActiveWorkbook.ProtectStructure Then MsgBox "Workbook is protected.", vbCritical Exit Sub End If ' Add a temporary dialog sheet Set CurrentSheet = ActiveSheet Set PrintDlg = ActiveWorkbook.DialogSheets.Add iBooks = 0 ' Add the checkboxes TopPos = 40 For i = 1 To Workbooks.Count Set CurrentSheet = ActiveWorkbook.Worksheets(i) ' Skip empty sheets and hidden sheets iBooks = iBooks + 1 PrintDlg.CheckBoxes.Add 78, TopPos, 150, 16.5 PrintDlg.CheckBoxes(iBooks).Text = _ Workbooks(iBooks).Name TopPos = TopPos + 13 Next i ' Move the OK and Cancel buttons PrintDlg.Buttons.Left = 240 ' Set dialog height, width, and caption With PrintDlg.DialogFrame .Height = Application.Max _ (68, PrintDlg.DialogFrame.Top + TopPos - 34) .Width = 230 .Caption = "Select workbooks to process" End With ' Change tab order of OK and Cancel buttons ' so the 1st option button will have the focus PrintDlg.Buttons("Button 2").BringToFront PrintDlg.Buttons("Button 3").BringToFront ' Display the dialog box Application.ScreenUpdating = True If PrintDlg.Show Then For Each cb In PrintDlg.CheckBoxes If cb.Value = xlOn Then MsgBox Workbooks(cb.Caption).Name & " selected" End If Next cb Else MsgBox "Nothing selected" End If ' Delete temporary dialog sheet (without a warning) Application.DisplayAlerts = False PrintDlg.Delete ' Reactivate original sheet CurrentSheet.Activate End Sub -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Chip Pearson" wrote in message ... Frank, Create a UserForm with a list box and a command button, in put the following code in the form's code module: Private Sub CommandButton1_Click() Dim WB As Workbook With Me.ListBox1 Set WB = Workbooks(.List(.ListIndex)) ' do something with WB End With End Sub Private Sub UserForm_Initialize() Dim WB As Workbook With Me.ListBox1 For Each WB In Workbooks .AddItem WB.Name Next WB .ListIndex = 0 End With End Sub -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Frank & Pam Hayes" wrote in message ... I am lookng for an example of VBA code that will produce a dialog box of all the currently opened Excel Workbooks and will allow the user to select which of the open Workbooks they would like to perform an action on. Can anyone point me in the right direction? I have tried multiple Google searches and the closest I could come is code to open Workbooks from the directory. Thank you, Frank |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
File name list in the open dialog box. | Excel Discussion (Misc queries) | |||
How to tell the number of current open workbooks... | Excel Worksheet Functions | |||
list Workbooks in Current Folder | Excel Programming | |||
Getting list of open workbooks | Excel Programming | |||
List Open Workbooks in VBA | Excel Programming |