how can I add a print dialogue?

Hi

I am using the code below I have copied from a site at
the address -

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

Please could someone tell me how to modify it to allow
for a dialogue box as you get when selecting file then
print allowing for the alteration of print settings.

If this is not possible I at least need the print options
to be pre set to print from page 1 to 1.

Any help appreciated :->

Rob

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

I have worked out how to do that but Instead having
thought about it I actually only want to be able to
specify that page 1 of 1 will be printed in all
circumstances, where do i put the 1 of 1 code?

This?

Application.Dialogs(xlDialogPrint).Show



Rob wrote:
>
> Hi
>
> I am using the code below I have copied from a site at
> the address -
>
> http://www.j-walk.com/ss/excel/tips/tip48.htm
>
> Please could someone tell me how to modify it to allow
> for a dialogue box as you get when selecting file then
> print allowing for the alteration of print settings.
>
> If this is not possible I at least need the print options
> to be pre set to print from page 1 to 1.
>
> Any help appreciated :->
>
> Rob
>
> 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

--

Dave Peterson