Can a macro be created to print

[btardo01]

I have a sales spreadsheet which has multiple worsheets, one worksheet per saleperson. On each worksheet there are 2 pairs of graphs. The 1st pair is sales performance based on gross revenue and the 2nd pair is sales performance based on net revenue.

I have created a template worksheet and am just copying that template for each additional sales guy. So, my 1st pair of graphs and its corresponding data spans from cell A3:O50 and my 2nd pair of graphs and their corresponding data spans from cell A51:O98. Since I am copying the same template each time, the sheets are all identical.

I'd like to create a "printing control panel" on its own worksheet at the beginning of the spreadsheet. Here the user would see a list of all the sales person in one column with checkboxes and in the second column the user would see a checkbox with gross revenue and one with net revenue.
At the top of each list, the user would also have the option to select all.

The user could then be able to select an combination of checkboxes, then click a command button that says print and everything would print out that was selected.

Sorry for long draw out description but I'd really appreciate if someone could help me through this.

thanks

 

[iliace]

I believe this is very close to what you need:

http://www.j-walk.com/ss/excel/tips/tip48.htm

I've adapted this code to many scenarios.

 

[iliace]

Actually I just noticed that the original code does not make multiple columns when you have too many sheets to display in one. Here is an adaptation that will create multiple columns.

Sub SelectSheets()
    Dim i As Integer, iColumnNumber As Integer
    Dim TopPos As Integer, LeftPos As Integer
    Dim ColumnCount As Integer
    Dim SheetCount As Integer
    Dim PrintDlg As DialogSheet
    Dim CurrentSheet As Worksheet
    Dim cb As CheckBox
    Application.ScreenUpdating = False
    
    Dim maxTopPos As Integer
    Dim dialogColumns As Integer
    
    Const topPosShift As Integer = 13
    Const LeftPosShift As Integer = 150
    Const initialTopPos As Integer = 40
    Const initialLeftPos As Integer = 78
    Const rowsPerDialogColumn As Integer = 30

'   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
    dialogColumns = 1
    maxTopPos = 0
    TopPos = initialTopPos
    LeftPos = initialLeftPos
    
    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
            ColumnCount = ColumnCount + 1
            PrintDlg.CheckBoxes.Add LeftPos, TopPos, 150, 16.5
            PrintDlg.CheckBoxes(ColumnCount).Text = CurrentSheet.Name
            TopPos = TopPos + topPosShift
            If (TopPos >= initialTopPos + rowsPerDialogColumn * topPosShift) Then
                dialogColumns = dialogColumns + 1
                maxTopPos = TopPos
                TopPos = initialTopPos
                LeftPos = LeftPos + LeftPosShift
            End If
        End If
    Next i

    If (maxTopPos = 0) Then
        maxTopPos = TopPos
    End If

'   Move the OK and Cancel buttons
    PrintDlg.Buttons.Left = 240

'   Set dialog height, width, and caption
   PrintDlg.Buttons.Left = 140 + dialogColumns * LeftPosShift

'   Set dialog height, width, and caption
    With PrintDlg.DialogFrame
        .Height = Application.Max(68, PrintDlg.DialogFrame.Top + maxTopPos - 34)
        .Width = 130 + dialogColumns * LeftPosShift
        .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

 

[btardo01]

Thanks so much for the response. I have actually seen that link before and it is close. However, I don't have very much experience with macros and am not sure how to edit the code. For example, I don't want a dialog box, I just want the checkboxes on the first sheet of my workbook. Secondly, I want to be able to have checkboxes that print a certain page of a sheet and not the entire sheet.

In summary, I want to list all the unhidden sheets of my workbook as checkboxes on my first sheet of the workbook (I can do this part manually if need be). Next to this list of checkboxes, I'll have a checkbox list of Page #s (which I want to do manually). If I choose just a sheet and hit print command button, it will print that entire sheet. If I choose a choose a sheet and a page number and then hit print, it will only print that specific page of that specific sheet. I'd like any combination of sheets and page #s to be available for selecting.

Can you help me edit that code to do this?

Your response was greatly appreciated.