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

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Forum statistics

Threads
1,195,838
Messages
6,011,891
Members
441,652
Latest member
FuelGuy

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
Top