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

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Forum statistics

Threads
1,214,943
Messages
6,122,376
Members
449,080
Latest member
Armadillos

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