Print Then Return To Sheet

max_lux · Jan 14th 2006

Hi,

I have a workbook which I have been working on for 5 years. Up until now I have used print and preview buttons from specific informational gathering pages. Print pages are different than the pages where the information is gathered, and other than previewing or printing, I do not allow the user access to these sheets.

I am about to add a custom toolbar to my workbook, and would like to know if it is possible to print a different sheet and then return to the sheet you were at, and if so, could you modify my example of some code so that that would be possible

Code

Sub Print_DH_Info()
    With Assistant
    .Animation = msoAnimationPrinting
    End With
    Application.ScreenUpdating = False
    Sheets("DH").Select
    ActiveSheet.PageSetup.PrintArea = "$G$1:$P$45"
    dlganswer = Application.Dialogs(xlDialogPrint).Show
    Sheets("info").Select             'currently returns to this page
    Application.ScreenUpdating = True
End Sub

Display More

Thanks very much for your help.

**norie** · Jan 14th 2006

Re: Print Then Return To Sheet

Max

Do you really need to show the dialog box?

If you don't you could use something like this.

Code

Sub Print_DH_Info() 
    With Assistant 
        .Animation = msoAnimationPrinting 
    End With 
    Application.ScreenUpdating = False 
    Sheets("DH").PageSetup.PrintArea = "$G$1:$P$45" 
    Sheets("DH").PrintOut Copies:=1
    Application.ScreenUpdating = True 
End Sub

**royUK** · Jan 14th 2006

Re: Print Then Return To Sheet

One thing - you must either ensure that the user has the Office assistant activated, or else your first lines of code won't be of any use.

It looks tom that you select one sheet, set the print area, display the Print Dialog then return to another sheet.

Code

Option Explicit


Sub Print_DH_Info()
    With Assistant
        .Animation = msoAnimationPrinting
    End With
    Application.ScreenUpdating = False
    Sheets("DH").Select
    ActiveSheet.PageSetup.PrintArea = "$G$1:$P$45"
    Application.Dialogs(xlDialogPrint).Show ' I don't think you need dlganswer= unless you use it elsewhere
    Sheets("info").Select 'currently returns to this page
    Application.ScreenUpdating = True
End Sub

Display More

max_lux · Jan 14th 2006

Re: Print Then Return To Sheet

Yes I need to show the dialog box, because multiple copies may need to be printed.

Your method will not return me to the original sheet.

Thanks for the try.

**royUK** · Jan 14th 2006

Re: Print Then Return To Sheet

Norie's example prints the sheet from anywhere. You could add an Inputbox to find out how many copies to print.

**norie** · Jan 14th 2006

Re: Print Then Return To Sheet

Max

Why not have an input box that prompts the user for the number of copies?

The code I posted will not move sheets.

max_lux · Jan 14th 2006

Re: Print Then Return To Sheet

I guess I wasn't clear in what I wish to do.

From any (original) sheet
Select "print (another)" sheet on custom tool bar
Print (another) sheet
Return to (original) sheet whatever sheet it was on

**norie** · Jan 14th 2006

Re: Print Then Return To Sheet

Max

My code should do that.

Do you mean you want the user to be able to select a sheet to print?

max_lux · Jan 14th 2006

Re: Print Then Return To Sheet

Norie,

won't it still be on the DH sheet once screen updating becomes true?

**royUK** · Jan 14th 2006

Re: Print Then Return To Sheet

Usual method is to have a Listbox on a UserFormthat lists all the sheets, make the selection then print. You could have a ComboBox to select how many copies.
I think if you check http://www.ozgrid you will find dave's example.

**norie** · Jan 14th 2006

Re: Print Then Return To Sheet

Quote from max_lux

Norie,

won't it still be on the DH sheet once screen updating becomes true?

No because the sheet wasn't actually selected.:)

max_lux · Jan 14th 2006

Re: Print Then Return To Sheet

hehe thanks for the faste rthan lightning replies guys

I need the print dialog because each person is in a truck and has their own printer, so printer selection, copies etc is easiest done with it.

All I want to do is return to the sheet I was at before I printed the hidden sheet.

Here's some basic code.

Code

Sub Print_M1()
    Sheets("M1").Select
    dlganswer = Application.Dialogs(xlDialogPrint).Show
End Sub

Do I just add the option explicit (as per below) to make this go back to the sheet I was at before I selected to print M1?

Code

Option Explicit


Sub Print_M1()
    Sheets("M1").Select
    dlganswer = Application.Dialogs(xlDialogPrint).Show
End Sub

**norie** · Jan 14th 2006

Re: Print Then Return To Sheet

Max

You do not need to select the sheet to print it.

If you need to set the printer, do it before you print the sheet.

max_lux · Jan 14th 2006

Re: Print Then Return To Sheet

Hmm, I'm confused. I'm attaching a simple workbook. Can you make it print sheet2 and end up back at sheet1, after printing, without selecting sheet1?

with screen updating false it still left it on sheet2

Thanks again,

**norie** · Jan 14th 2006

Re: Print Then Return To Sheet

This works for me and allows the user to select a printer and the no of copies.

Code

Sub Print_Sheet2()
Dim x
    Application.ScreenUpdating = False
    
    dlganswer = Application.Dialogs(xlDialogPrinterSetup).Show
    x = InputBox("Please enter no of copies", "No of copies to print", 1)
    Sheets("Sheet2").PrintOut Copies:=x
    Application.ScreenUpdating = True
End Sub

max_lux · Jan 14th 2006

Re: Print Then Return To Sheet

Ok that works brilliantly, but on error such as cancelling? not having the runtime error end/ debug/ help box pop up?

**norie** · Jan 14th 2006

Re: Print Then Return To Sheet

Which cancel are you referring to?

Code

Sub Print_Sheet2()
Dim x
    Application.ScreenUpdating = False
    
    dlganswer = Application.Dialogs(xlDialogPrinterSetup).Show
    x = InputBox("Please enter no of copies", "No of copies to print", 1)
    If IsNumeric(x) Then
        Sheets("Sheet2").PrintOut Copies:=x
    End If
    Application.ScreenUpdating = True
End Sub

Display More

max_lux · Jan 14th 2006

Re: Print Then Return To Sheet

if u cancel either printer or no of copies you get the error

**norie** · Jan 14th 2006

Re: Print Then Return To Sheet

I don't get an error when cancelling the printer.

But this is how you can deal with it.

Code

Sub Print_Sheet2()
Dim x
    Application.ScreenUpdating = False
    
    dlganswer = Application.Dialogs(xlDialogPrinterSetup).Show
    
    x = InputBox("Please enter no of copies", "No of copies to print", 1)
    If IsNumeric(x) And dlganswer Then
        Sheets("Sheet2").PrintOut Copies:=x
    End If
    Application.ScreenUpdating = True
End Sub

Display More

max_lux · Jan 14th 2006

Re: Print Then Return To Sheet

lol - nice job Norie - hehe replied before I noticed u'd changed the code Error gone!

Many thanks!

Print Then Return To Sheet

Participate now!

Share