Dialog Box List of Current Open Workbooks

F

Frank & Pam Hayes

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
 
C

Chip Pearson

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
 
B

Bob Phillips

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)
 
Top