Displaying A Menu Of Worksheets To Print
This tip contains a VBA subroutine that displays a dialog box that contains the names of all non-empty worksheets in the active workbook. The names are displayed as checkboxes. The user can select which sheets to print, and click OK. The macro then prints the selected worksheets.
This subroutine is rather unusual since it creates the dialog box on the fly, and then deletes it after it is dismissed.
The code below prints the selected worksheets, but this general technique can be adapted to other situations in which you need the user to specify one or more sheets in a workbook.
Although this procedure uses an Excel 5/95 Dialog Sheet, it will also work with later versions of Excel. By the way, this is a rare example of how using a Dialog Sheet is easier than using a UserForm. Creating a UserForm on the fly is much more difficult, but it is possible.
Using the code
To try out the example, copy the code below and paste it to a VBA module (there is no need to add a dialog sheet). Then execute the SelectSheets subroutine.
The SelectSheets subroutine
Option Explicit Sub SelectSheets() Dim i As Integer Dim TopPos As Integer Dim SheetCount 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 SheetCount = 0 ' Add the checkboxes TopPos = 40 For i = 1 To ActiveWorkbook.Worksheets.Count Set CurrentSheet = ActiveWorkbook.Worksheets(i) ' Skip empty sheets and hidden sheets If Application.CountA(CurrentSheet.Cells) <> 0 And _ CurrentSheet.Visible Then SheetCount = SheetCount + 1 PrintDlg.CheckBoxes.Add 78, TopPos, 150, 16.5 PrintDlg.CheckBoxes(SheetCount).Text = _ CurrentSheet.Name TopPos = TopPos + 13 End If 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 sheets to print" 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 CurrentSheet.Activate Application.ScreenUpdating = True If SheetCount <> 0 Then If PrintDlg.Show Then For Each cb In PrintDlg.CheckBoxes If cb.Value = xlOn Then Worksheets(cb.Caption).Activate ActiveSheet.PrintOut ' ActiveSheet.PrintPreview 'for debugging End If Next cb End If Else MsgBox "All worksheets are empty." End If ' Delete temporary dialog sheet (without a warning) Application.DisplayAlerts = False PrintDlg.Delete ' Reactivate original sheet CurrentSheet.Activate End Sub
NOTE: Aaron Blood suggested the following modification, which prints the selected sheets as a single print job. This allows the sheet to be printed with continuous page numbers.
If PrintDlg.Show Then For Each cb In PrintDlg.CheckBoxes If cb.Value = xlOn Then Worksheets(cb.Caption).Select Replace:=False End If Next cb ActiveWindow.SelectedSheets.PrintOut copies:=1 ActiveSheet.Select End If
Excel has a long history, and it continues to evolve and change. Consequently, the tips provided here do not necessarily apply to all versions of Excel.
In particular, the user interface for Excel 2007 (and later), is vastly different from its predecessors. Therefore, the menu commands listed in older tips, will not correspond to the Excel 2007 (and later) user interface.
Browse Tips by Category
Search for Tips
Needs tips? Here are two books, with nothing but tips:
Contains more than 200 useful tips and tricks for Excel 2007 | Other Excel 2007 books | Amazon link: John Walkenbach's Favorite Excel 2007 Tips & Tricks