Trying to Print User-Selected Sheets from a button on one sheet.

Hello, everyone.

I am new to this forum and vaguely familiar with VB (had an intro to it a while back), and I simply need to figure out why something isn't working like it should.

I have an Excel Workbook (2007) with approximately 20 sheets. I set it up so that it is navigable using buttons with macros behind them, and so that only one sheet is visible at a time -to move from one sheet to a different one, the user needs to go back to a main selection sheet (a "switchboard form", if you like). Each sheet has a button to print that sheet, and all of that works perfectly.

What I am trying to add is a button on the "MASTER" sheet that allows the user to select which sheet he/she wants to print without going to the sheet, and also allows them to print more than one sheet with a single action.

The best code I have found for this thus far was a piece that I am posting here. It purports to do exactly what I want, but hangs up just before the print. It is meant to create a dialog box on the fly with checkboxes representing the sheets in the workbook. The user selects one or more checkbox(es), then clicks "OK", which is supposed to print the selected sheets.

The error comes after the sheet(s) are selected, and "OK" is pressed.

Microsoft KB had a solution to this, but none of the workarounds (they list 4) seem to apply to what I am doing.

Unfortunately, I cannot see replies to this thread until I get home in the evenings (my network policy at work has blocked this site), and I will not be able to post the workbook because it contains Personally Identifiable Information (PII) on a number of people.

I can post the code that is giving me trouble, as it is already in the public domain and I will bold and CAP the line that is highlighted when I go into the debugger.

Any help on this is greatly appreciated.

The code follows:

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

---End of Code---

Thank you again for any help you can provide.

Hello rattler418,

Welcome to the Forum!

It seems to me that is complicating a simple operation. You can add a UserForm to your VBA project with a ListBox and a CommandButton. The code below can be added to this UserForm.

Private Sub CommandButton1_Click()

    Application.Dialogs(xlDialogPrint).Show
    
End Sub

Private Sub UserForm_Initialize()

    Dim Sht As Object
    
        For Each Sht In Sheets
            ListBox1.AddItem Sht.Name
        Next Sht

End Sub

Add the following code to the button on your on the "Master". This will display the user form. Change the name of the user form to match the name of your user form.

    UserForm1.Show

Or you can simply use a validationlist; e.g. in cell C3

Sub snb()
 For Each sh In Sheets
  c01 = c01 & "," & sh.Name
 Next
 sheets("Master").Cells(3, 3).Validation.Add xlValidateList, , , Mid(c01, 2)
End Sub

in the macromodule of worksheet 'Master':

Private Sub Worksheet_Change(ByVal Target As Range)
 If Target.Address = "$C$3" Then Sheets(Target.Value).PrintOut
End Sub