Ungroup selected worksheets after Before_Print event

**clng** · 07-25-2010, 11:01 PM

Hi all,

Been trying to check whether I can ungroup all selected worksheets (that I have group as a array for printing) but has been unsuccessful. The reason being that I am using workbook before print event to control only certain worksheets will requires this type of array printing and after grouping and printing, the workbook befoer print event cannot help me ungroup those worksheets and switch back to the original worksheet that I had activated the print command (using Ctrl + P) or print .

Can anyone advice how I can circumvent this?

Thanks.
CL

**Simon Lloyd** · 07-26-2010, 01:55 AM

Simply at the bottom of the Before_Print add Call MyMacro where MyMacro is your standard module macro that selects another sheet or does whatever you want

**clng** · 07-26-2010, 03:09 AM

Hi Simon,

Thanks for the quick reply but it does not work.

I am actually allowing users to choose the printers to print from (may be hardcopy or PDF etc.). But before choosing the printer, excel will automatically (with workbook before print event) adds another appendix worksheet to the end of the current worksheet to be printed (by using

If ActiveSheet.Name = "Print Sheet" Then

    Sheets(Array("Print Sheet", "Appendix add sheet")).Select

End If

But the problem now is that users will need to manually click shift + "Appendix add sheet" off to get back to "Print Sheet".

Your suggestion will make it go back to "Print Sheet" even before the actual printing and thus I will miss out the "Appendix add sheet"

Is there a way to circumvent this or I should relook at my code or just simply leave it to users to manual select back to "Print Sheet" to work on. It will be best if there is a "workbook after print event".

Cheers,
CL

**Simon Lloyd** · 07-26-2010, 10:54 AM

Originally Posted by clng

Hi Simon,

Thanks for the quick reply but it does not work.

I am actually allowing users to choose the printers to print from (may be hardcopy or PDF etc.). But before choosing the printer, excel will automatically (with workbook before print event) adds another appendix worksheet to the end of the current worksheet to be printed (by using

If ActiveSheet.Name = "Print Sheet" Then
 
    Sheets(Array("Print Sheet", "Appendix add sheet")).Select
 
End If

But the problem now is that users will need to manually click shift + "Appendix add sheet" off to get back to "Print Sheet".

Your suggestion will make it go back to "Print Sheet" even before the actual printing and thus I will miss out the "Appendix add sheet"

Is there a way to circumvent this or I should relook at my code or just simply leave it to users to manual select back to "Print Sheet" to work on. It will be best if there is a "workbook after print event".

Cheers,
CL

In your Before_print

'created by Simon Lloyd http://www.thecodecage.com
Private Sub Workbook_BeforePrint(Cancel As Boolean)
If ActiveSheet.Name = "Print Sheet" Then
    Sheets(Array("Print Sheet", "Appendix add sheet")).Select
Call Print_it
End If
End Sub

in a standard module

'created by Simon Lloyd http://www.thecodecage.com
Sub print_it()
ActiveWindow.SelectedSheets.PrintOut Copies:=1
Sheets("Print Sheet").Activate
End Sub

That should work for you!

**clng** · 07-26-2010, 11:37 AM

Hi Simon,

Am wondering whether your method works when user do action such as "Ctrl + P" or click file + print? This is because when user click print, the printer dialouge box will appear, but this is after all macro in the "workbook before print" has been activated. so in your recommendation, I assume that the printer will be activated twice. I cannot test it now as I am out of office, will try your method tomorrow and post again.

Cheers,
CL

**clng** · 07-27-2010, 09:57 PM

Hi Simon,

Still unable to figure out how to execute a new macro immediately after a print command. I had tried your method but the sub-routine still gets "trapped" and is being executed in the "Before Print" event. I am looking for a "After Print" solution.

Please kindly help.

Cheers,
CL

**Simon Lloyd** · 07-27-2010, 11:53 PM

Follow my original instructions for the 2 pieces of code i gave you but use this as i've made a slight amendment

Before_print

'created by Simon Lloyd http://www.thecodecage.com
Private Sub Workbook_BeforePrint(Cancel As Boolean)

Cancel = True  'prevents the print process started by the user from completing
Application.EnableEvents = False
If ActiveSheet.Name = "Print Sheet" Then
    Sheets(Array("Print Sheet", "Appendix add sheet")).Select
Call print_it
End If
Application.EnableEvents = True
End Sub

Standard module

'created by Simon Lloyd http://www.thecodecage.com
Sub print_it()
ActiveWindow.SelectedSheets.PrintOut Copies:=1
Sheets("Sheet2").Select' or some other sheet
Sheets("Print Sheet").Activate
End Sub

This will work perfect for you!