Print Issue #3 - Embed Printer Selection into custom print Form

Sandcastor

Board Regular
Joined
May 14, 2012
Messages
97
What I have: a large sheet, containing 25 reports (each report having three non-contiguous parts, defined by named ranges like Stim_01_T). A button on the report sheet summons a custom form to choose which reports print (using checkboxes).
Picture:
http://sdrv.ms/KB4SqH

The form is called from the sheet by this code:
Code:
Sub PrintSStim()
UserPrintSStim.Show
End Sub
THen the following either cancels or prints based on value of checkboxes. (Big Thanks to JS411 and PGC01 for their help getting me going)
Code:
Private Sub CancelSStim_Click()
UserPrintSStim.Hide
End Sub
 
 
Private Sub SSPrintButton_Click()
Application.ScreenUpdating = False
    If Me.SSP1.Value = True Then
        ActiveSheet.PageSetup.PrintArea = "Stim_01_W"
            ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, Preview:=False 'ActivePrinter:=
        ActiveSheet.PageSetup.PrintArea = "Stim_01_T"
            ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, Preview:=False 'ActivePrinter:=
        ActiveSheet.PageSetup.PrintArea = "Stim_01_B"
            ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, Preview:=False 'ActivePrinter:=
    End If
 
    If Me.SSP2.Value = True Then
        ActiveSheet.PageSetup.PrintArea = "Stim_02_W"
            ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, Preview:=False 'ActivePrinter:=
        ActiveSheet.PageSetup.PrintArea = "Stim_02_T"
            ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, Preview:=False 'ActivePrinter:=
        ActiveSheet.PageSetup.PrintArea = "Stim_02_B"
            ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, Preview:=False 'ActivePrinter:=
    End If
 
    If Me.SSP3.Value = True Then
        ActiveSheet.PageSetup.PrintArea = "Stim_03_W"
            ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, Preview:=False 'ActivePrinter:=
        ActiveSheet.PageSetup.PrintArea = "Stim_03_T"
            ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, Preview:=False 'ActivePrinter:=
        ActiveSheet.PageSetup.PrintArea = "Stim_03_B"
            ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, Preview:=False 'ActivePrinter:=
    End If
'etc. etc. etc. until SSP25
UserPrintSStim.Hide
Application.ScreenUpdating = True
End Sub
Now I know it's a bit messy, but for me right now it was easier than struggling through loops.

Anyways, I have run into a bit of a wall and cannot find the right code to cobble together like i usually do! Here is what the first thing I would like to do:
Logically: populate the listbox with an array of available printers. When highlighted, the active Printer is chosen and used for printing. If necessary, the "ActivePrinter:= " parameter is ready to go in the printout comands.

Also I need to have Application.ActivePrinter go back to being the system default printer after the print batch is done.

Any suggestions?:confused:
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Forum statistics

Threads
1,215,793
Messages
6,126,937
Members
449,349
Latest member
Omer Lutfu Neziroglu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top