Print code with checkbox and print dialogue/summary

[sigilbo]

Hi,

I'm in need of some help regarding printing in excel with a vba code.

At the moment I have a checkbox which pops up when you click the "Print" button I created on the sheet. But once you have selected the sheets you want and hit "OK" it goes straight through to the printer.

Pic 2.JPG

I'm hoping there is a way of having the pop up menu like above, but when you hit "OK" it takes you through to the printing options dialogue, where you can choose what printer, margin options, orientation, active sheets and so on, like the image below.. and ideally, with the print preview.


Pic 1.JPG



The current print code I'm using is this one, which i found online. If someone could lend me a few minutes to show me what extra bit of code to put where I would really appreciate it!

-----------------------------------------------------------------------------------

Option Explicit

Sub Print_Workbook()

Dim i As Integer
Dim TopPos As Integer
Dim SheetCount As Integer
Dim PrintDlg As DialogSheet
Dim CurrentSheet As Worksheet
Dim Wsh 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 Each Wsh In ActiveWorkbook.Worksheets
' Skip empty sheets and hidden sheets
If Application.CountA(Wsh.Cells) <> 0 And Wsh.Visible Then
SheetCount = SheetCount + 1
PrintDlg.CheckBoxes.Add 78, TopPos, 150, 16.5
PrintDlg.CheckBoxes(SheetCount).Text = Wsh.Name
TopPos = TopPos + 13
End If
Next Wsh

' 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
If SheetCount <> 0 Then
If PrintDlg.Show Then
'* Re-use SheetCount as flag [1= 1st sheet, 2=Other sheets]
SheetCount = 1
For Each cb In PrintDlg.CheckBoxes
If cb.Value = xlOn Then
Worksheets(cb.Caption).Select Replace:=SheetCount = 1
SheetCount = 2
End If
Next cb
ActiveWindow.SelectedSheets.PrintOut copies:=1
End If
Else
MsgBox "All worksheets are empty."
End If

' Delete temporary dialog sheet (without a warning)
Application.DisplayAlerts = False
PrintDlg.Delete
Application.DisplayAlerts = True
Application.ScreenUpdating = True

' Reactivate original sheet
CurrentSheet.Activate


End Sub

[Olly]

Try replacing:

Please Login or Register  to view this content.

with:

Please Login or Register  to view this content.

Edit: oh, and please use CODE tags when you post VBA code. Thanks!

[sigilbo]

Thanks for the quick response Olly.

I've replaced the part of the code you suggested and i do get the print preview screen i was after, however it is only showing to print the sheet that the button is on, no matter which sheet i select in the list. Nearly there!

Is there a way of when the sheets are selected they keep the page number footer running as well?

Many Thanks
Si