Printer selection in VBA

[Mike Magill]

Hi,

I'm trying to give the user the ability to select the printer they use
within a macro. The code I've got so far is show below (I know it's
not pretty but I'm a bit of a novice). The problem is that when the
Printer Dialog box appears it requires to presses of the OK button to
make the macro continue and print the copy.

Any ideas why this might be and how to fix it?

Thanks,

Mike



Sub Print_Report()
'
' Macro to prepare rows for printing to ensure only rows containing
data
' are visible and then print one copy

UserForm1.Show

With ActiveSheet.PageSetup '
Set Page Setup
.PrintTitleRows = "$2:$6"
.PrintTitleColumns = ""
.PrintArea = "$B:$AB"
.Orientation = xlLandscape
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = False

End With

Rows("1:1").Select
Selection.EntireRow.Hidden = True '
Hide control panel when printing

Selection.AutoFilter Field:=30, Criteria1:="x" '
Hide rows with no data

Application.Dialogs(xlDialogPrinterSetup).Show

If Not (Application.Dialogs(xlDialogPrinterSetup).Show) Then
Selection.AutoFilter Field:=30 '
Show rows with no data

Rows("1:1").Select '
Reveal control panel
Selection.EntireRow.Hidden = False

Else
End If

ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True '
Print one copy



Selection.AutoFilter Field:=30 '
Show rows with no data

Rows("1:1").Select '
Reveal control panel
Selection.EntireRow.Hidden = False

End Sub

 

[Dave Peterson]

Try getting rid of the if statent that shows the dialog:

Application.Dialogs(xlDialogPrinterSetup).Show
Selection.AutoFilter Field:=30 '
'Show rows with no data
Rows("1:1").Select '
'Reveal control panel
Selection.EntireRow.Hidden = False