Nice Print Macro

[Blue Hornet]

Here's a modified Print Macro that I got from Smitty (pennysaver) when he posted it in answer to a question on MrExcel.com. (He won't take credit for it, saying that he picked it up himself somewhere. Whoever built it should take some credit!)

As Smitty posted it, the thing would only list Worksheets for printing, always dropped the user off at the last visible worksheet in the workbook, and exited ingloriously if started from a non-Worksheet. I wanted it to be able to print Chartsheets, and I think I have done that (but I have questions). I also trapped the error and showed the user a message for the times when the macro is started from a non-Worksheet.

What the macro does is set up a Dialogsheet with a form to record each printable sheet in the workbook, and a checkbox next to each. The user checks which sheets to print, OKs the dialog box, and the sheets are printed. Then the Dialogsheet is deleted from the workbook.

I think it can be improved some more, such as:
1. Ability to select the printer or print file for output, rather than the default printer;

2. How can I query for the Type of Sheet which is not a Worksheet? I've stumbled onto Sheet Type = 3 and Sheet Type = 4 for Charts, but why is there more than one Type for charts (depending on chart type, maybe?)?;

3. I'd like to selectively activate the 'batch printing' mode "Print all sheets with consecutive numbers", OR sheet-by-sheet, starting each page with '1', but I ran into problems when I tried to make that work, and gave it up pretty quickly.

In any case, it's a very cool tool! Thanks for posting it, Smitty.

Chris

Sub Special_Print()
' from MrExcel.com -- look at favorites
' http://www.mrexcel.com/board2/viewtopic.php?t=101102
Dim i As Integer
Dim TopPos As Integer
Dim SheetCount As Integer
Dim PrintDlg As DialogSheet
Dim CurrentSheet As Worksheet
Dim CurrentChart As Chart
Dim StartSheet As String
Dim cb As CheckBox
'Application.ScreenUpdating = False
' Check for protected workbook
If ActiveWorkbook.ProtectStructure Then
MsgBox "Workbook is protected.", vbCritical
Exit Sub
' Check for 'non-worksheet'
ElseIf ActiveSheet.Type <> xlWorksheet Then
MsgBox "You can only start this from a WorkSheet.", vbCritical
Exit Sub
End If
' Add a temporary dialog sheet
Set CurrentSheet = ActiveSheet
StartSheet = ActiveSheet.Name
Set PrintDlg = ActiveWorkbook.DialogSheets.Add
SheetCount = 0 
' Add the checkboxes
TopPos = 40
For i = 1 To ActiveWorkbook.Sheets.Count
If Left(ActiveWorkbook.Sheets(i).Name, 6) = "Dialog" Then GoTo GetNextSheet
If ActiveWorkbook.Sheets(i).Type = xlWorksheet Then
Set CurrentSheet = ActiveWorkbook.Sheets(i)
GoTo GotWorksheet
ElseIf ActiveWorkbook.Sheets(i).Type = 3 _
Or ActiveWorkbook.Sheets(i).Type = 4 Then
Set CurrentChart = ActiveWorkbook.Sheets(i) 'Types 3 and 4 = Chart ... what else?
GoTo GotChart
Else
GoTo GetNextSheet
End If
' Skip empty sheets and hidden sheets
GotChart:
If CurrentChart.Visible Then
SheetCount = SheetCount + 1
PrintDlg.CheckBoxes.Add 78, TopPos, 150, 16.5
PrintDlg.CheckBoxes(SheetCount).Text = _
CurrentChart.Name
TopPos = TopPos + 13
GoTo GetNextSheet
End If
GotWorksheet:
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
GetNextSheet:
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
Sheets(StartSheet).Activate
Application.ScreenUpdating = True
If SheetCount <> 0 Then
'Print as one print job (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
'Print as separate print jobs
If PrintDlg.Show Then
For Each cb In PrintDlg.CheckBoxes
If cb.Value = xlOn Then
Sheets(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
Sheets(StartSheet).Activate
End Sub

[Anne Troy]

Hey, Chris. You want to add that to our knowledgebase?

http://www.vbaexpress.com/kb

You may need to log in.
Be careful with the lines that number items!
(Please don't put hyperlinks in the code in the KB; put that stuff in the discussion area.)

[Blue Hornet]

I'd like to consider that, after I have the bugs worked out that I enumerated in the posting.

Thanks for the suggestion.

Chris

[Anne Troy]

Cool!

[Jacob Hilderbrand]

1) Try this to select the printer:

Application.Dialogs(xlDialogPrinterSetup).Show

[Jacob Hilderbrand]

Also is there a reason for using a Dialog Sheet instead of a User Form?

[Blue Hornet]

Regarding the DialogSheet vs. UserForm ... you got me; I have almost no experience with either of them. This was the code I got, and I've only been working on the parts that I understand. Obviously, that's not one of those areas. I just assumed that the DialogSheet was the 'parent' of the UserForm, and was therefore necessary.

Chris

[Jacob Hilderbrand]

User Forms are a lot more flexible. Try something like this. Make a User Form with two list boxes and three command buttons. Then paste this code. Or download the attachment. This will put all the worksheets in a listbox and you drag them to a second listbox to print. It will print hidden and veryhidden sheets as well.

Option Explicit
 
Private Sub CommandButton1_Click() 'Print
Dim Lst As String
Dim x As Long
For x = 0 To ListBox2.ListCount - 1
Lst = ListBox2.List(x)
Select Case Sheets(Lst).Visible
Case Is = xlSheetVisible
Sheets(Lst).PrintOut
Case Is = xlSheetHidden
Sheets(Lst).Visible = True
Sheets(Lst).PrintOut
Sheets(Lst).Visible = xlSheetHidden
Case Is = xlSheetVeryHidden
Sheets(Lst).Visible = True
Sheets(Lst).PrintOut
Sheets(Lst).Visible = xlSheetVeryHidden
End Select
Next
Unload Me
End Sub
 
Private Sub CommandButton2_Click() 'Cancel
Unload Me
End Sub
 
Private Sub CommandButton3_Click() 'Select Printer
Application.Dialogs(xlDialogPrinterSetup).Show
End Sub
 
Private Sub ListBox2_BeforeDragOver(ByVal Cancel As MSForms.ReturnBoolean, _
ByVal Data As MSForms.DataObject, _
ByVal x As Single, _
ByVal Y As Single, _
ByVal DragState As Long, _
ByVal Effect As MSForms.ReturnEffect, _
ByVal Shift As Integer)
Cancel = True
Effect = 1
End Sub
 
Private Sub ListBox2_BeforeDropOrPaste(ByVal Cancel As MSForms.ReturnBoolean, _
ByVal Action As Long, _
ByVal Data As MSForms.DataObject, _
ByVal x As Single, _
ByVal Y As Single, _
ByVal Effect As MSForms.ReturnEffect, _
ByVal Shift As Integer)
Cancel = True
Effect = 1
ListBox2.AddItem Data.GetText
End Sub
 
Private Sub ListBox1_MouseMove(ByVal Button As Integer, _
ByVal Shift As Integer, _
ByVal x As Single, _
ByVal Y As Single)
Dim MyDataObject As DataObject
Dim Effect As Integer
If Button = 1 Then
Set MyDataObject = New DataObject
MyDataObject.SetText ListBox1.Value
Effect = MyDataObject.StartDrag
End If
End Sub
 
Private Sub UserForm_Initialize()
Dim WS As Worksheet
For Each WS In ActiveWorkbook.Worksheets
ListBox1.AddItem WS.Name
Next
End Sub

[Blue Hornet]

DRJ -- Jacob,

Your example is certainly better than the one I posted in many respects, and if you'd like to continue developing it, then I'd certainly be finding some use in it. However, there are also some drawbacks:

1. Using the ListBoxes is not intuitive. That is, it's not evident that one has to drag sheet names from ListBox1 into ListBox2 to print. The checkboxes that the other macro (I'm not going to call it 'mine') used are pretty clear to even a novice user.

2. That sheet dragging is only done one-at-a-time (and dragging itself is a pain). Checking boxes can be done with the mouse or keyboard (Tab + spacebar), which is a lot quicker.

3. The 'Select Printer' button is great, no doubt about it.

4. The code you posted above has the added benefit of showing hidden and veryhidden sheets, which may be nice (I wouldn't want to give that ability to a lot of my users, but I like to have it for myself), but it has the drawback of allowing empty workbooks to be printed. No point in that. I didn't test with Charts, but I assume they are handled.

5. The ListBox sizes are fixed, so if you have workbooks with more than a few sheets, then you'll be scrolling through the list to see and select them. The DialogSheet code grows a selection box as big as will fit on the screen (after which that form is useless, because it can't be moved or scrolled). At least the ListBox CAN be scrolled, when it gets to that point.

6. I'd still like a selection to be able to 'batch' all the sheets together, so that 3 1-page sheets print "Page 1 of 3" through "Page 3 of 3", instead of sending individual print jobs.

7. Your code ignores Chart sheets. But it doesn't fail when the user attempts to start from one, as the original does.

8. Finally, and maybe I should have listed this first ... I don't have a clue how to run the UserForm without going to the VBA editor and Run from there. The original code was easy to paste into my Personal.XLS macro list and run from my custom toolbar. How do I run the UserForm on a day-to-day basis?

As you can see, I'm not much of a programmer, but I do have some ideas about interfaces. Clearly you have some very good ideas, and if you carry them out to address the issues above, then YOU should be posting your code to the KnowledgeBase.

Nice start!

Chris

[Jacob Hilderbrand]

You can have the userform automatically show up when you print. Put this code in the Thisworkbook code section:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
    UserForm1.Show
End Sub

Or you can make a macro:

Private Sub Macro1
    UserForm1.Show
End Sub

And attach it to your toolbar.

[mdmackillop]

Hi Chris,
I posted some Excel print macro stuff here as a Work in Progress. There may be bits you can make use of.
http://www.vbaexpress.com/forum/showthread.php?t=569
MD