VBA Printing Preferences Help

bamaisgreat

Well-known Member
Joined
Jan 23, 2012
Messages
831
Office Version
  1. 365
Platform
  1. Windows
I have the code below in a userform that I need a little help with. I need the code to have like black and white, 11 x 17, zoom 90, landscape, print range and also I need it to go to a specific printer. Im not really sure how to get any of that information. Thanks as always for the help.

Code:
Private Sub CommandButton1_Click()
If Me.CheckBox1 = True Then
    ThisWorkbook.Sheets("SHT#1").PrintOut Copies:=1
End If


If Me.CheckBox2 = True Then
    ThisWorkbook.Sheets("SHT#2").PrintOut Copies:=1
End If


If Me.CheckBox3 = True Then
    ThisWorkbook.Sheets("SHT#3").PrintOut Copies:=1
End If


If Me.CheckBox4 = True Then
    ThisWorkbook.Sheets("SHT#4").PrintOut Copies:=1
End If


If Me.CheckBox5 = True Then
    ThisWorkbook.Sheets("SHT#5").PrintOut Copies:=1
End If


If Me.CheckBox6 = True Then
    ThisWorkbook.Sheets("SHT#6").PrintOut Copies:=1
End If


If Me.CheckBox7 = True Then
    ThisWorkbook.Sheets("SHT#7").PrintOut Copies:=1
End If


If Me.CheckBox8 = True Then
    ThisWorkbook.Sheets("SHT#8").PrintOut Copies:=1
End If


If Me.CheckBox9 = True Then
    ThisWorkbook.Sheets("SHT#9").PrintOut Copies:=1
End If


If Me.CheckBox10 = True Then
    ThisWorkbook.Sheets("SHT#10").PrintOut Copies:=1
End If


If Me.CheckBox11 = True Then
    ThisWorkbook.Sheets("SHT#11").PrintOut Copies:=1
End If


If Me.CheckBox12 = True Then
    ThisWorkbook.Sheets("SHT#12").PrintOut Copies:=1
End If


If Me.CheckBox13 = True Then
    ThisWorkbook.Sheets("SHT#13").PrintOut Copies:=1
End If


If Me.CheckBox14 = True Then
    ThisWorkbook.Sheets("SHT#14").PrintOut Copies:=1
End If


If Me.CheckBox15 = True Then
    ThisWorkbook.Sheets("SHT#15").PrintOut Copies:=1
End If


If Me.CheckBox16 = True Then
    ThisWorkbook.Sheets("SHT#16").PrintOut Copies:=1
End If


If Me.CheckBox17 = True Then
    ThisWorkbook.Sheets("SHT#17").PrintOut Copies:=1
End If


If Me.CheckBox18 = True Then
    ThisWorkbook.Sheets("SHT#18").PrintOut Copies:=1
End If


If Me.CheckBox19 = True Then
    ThisWorkbook.Sheets("SHT#19").PrintOut Copies:=1
End If


If Me.CheckBox20 = True Then
    ThisWorkbook.Sheets("SHT#20").PrintOut Copies:=1
End If


If Me.CheckBox21 = True Then
    ThisWorkbook.Sheets("SHT#21").PrintOut Copies:=1
End If


If Me.CheckBox22 = True Then
    ThisWorkbook.Sheets("SHT#22").PrintOut Copies:=1
End If


If Me.CheckBox23 = True Then
    ThisWorkbook.Sheets("SHT#23").PrintOut Copies:=1
End If


If Me.CheckBox24 = True Then
    ThisWorkbook.Sheets("SHT#24").PrintOut Copies:=1
End If


If Me.CheckBox25 = True Then
    ThisWorkbook.Sheets("SHT#25").PrintOut Copies:=1
End If


If Me.CheckBox26 = True Then
    ThisWorkbook.Sheets("SHT#26").PrintOut Copies:=1
End If


End Sub
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Creating the printout parameters is straight forward normally, start the macro recorder than add ALL the actions you need, paper, orientation, zoom. That will give 3 sets of outputs (use the last one), printer determination and range will take more work. record that and paste back here
 
Upvote 0
usage:
SetDefaultPrinter "myPrinter"

or to change the default ,print, then change it back:
Code:
sub btnPrint_click()
Dim sPtr As String

sPtr = GetDefaultPrinter()
SetDefaultPrinter "MyPrinter"
Me.PrintForm
SetDefaultPrinter sPtr
end sub

paste this code into a module.

Code:
Attribute VB_Name = "modPrinterSet"
''Option Compare Database
Option Explicit

'************************
' Printer setup module
' Set/retrieves the default printer - originaly for VB6
' Works for A97/a2000
' This is minimal code.
' Albert D.Kallal - 01/13/2002
' Rev history:       Date           Who                   notes
'                    01/13/2002     Albert D. kallal
'
' I wrote this after looking at some the code on the net. Some of the routines
' to change a printer were approaching 500 + of lines of code. Just the printer
' constant defs was over 100 lines of code! Yikes!
' I use only TWO API's (the 3rd one is optional). There is a total of only 4 functions!
' KISS is the word. Keep it simple stupid. I don't care about device drivers, or the
' port number. All these routines just work with the simple printer name. If you do
' actually care about the device driver and port stuff..then use the one of many
' examples available on the net. Those other examples also deal with margins, orientation
' etc.
'
' You can paste this code into a module..and away you go
'
'************************
' How to use
' To get the default printer
'        debug.print   GetDefaultPrinter
' To set the default printer
'        debug.print SetDefaultPrinter("HP Laser JET")
'  above returns true if success.
' To get a list of printers suitable for a listbox, or combo
'        debug.print GetPrinters
'
' that is all there folks!
'
' Thus, when printing a report, you can:
'
'       1) save the default printer into a string
'              strCurrentPtr = GetDefaultPrinter
'       2) switch to your report printer
'              SetDefaultPrinter strReportsPtr
'       3) print report
'       4) switch back to the default printer
'              SetDefaultPrinter strCurrentPtr
'


Private Const HWND_BROADCAST As Long = &HFFFF&
Private Const WM_WININICHANGE As Long = &H1A


' The following code allows one to read, and write to the WIN.INI files
' In win 2000 the printer settings are actually in the registry. However, windows
' handles this correctly
'
Private Declare Function GetProfileString Lib "kernel32" _
   Alias "GetProfileStringA" _
  (ByVal lpAppName As String, _
   ByVal lpKeyName As String, _
   ByVal lpDefault As String, _
   ByVal lpReturnedString As String, _
   ByVal nSize As Long) As Long


Private Declare Function WriteProfileString Lib "kernel32" _
   Alias "WriteProfileStringA" _
  (ByVal lpszSection As String, _
   ByVal lpszKeyName As String, _
   ByVal lpszString As String) As Long


Private Declare Function SendMessage Lib "user32" _
   Alias "SendMessageA" _
  (ByVal hwnd As Long, _
   ByVal wMsg As Long, _
   ByVal wParam As Long, _
   lparam As Any) As Long
   
         


Private Function fstrDField(mytext As String, delim As String, groupnum As Integer) As String


   ' this is a standard delimiter routine that every developer I know has.
   ' This routine has a million uses. This routine is great for splitting up
   ' data fields, or sending multiple parms to a openargs of a form
   '
   '  Parms are
   '        mytext   - a delimited string
   '        delim    - our delimiter (usually a , or / or a space)
   '        groupnum - which of the delimited values to return
   '
   
Dim startpos As Integer, endpos As Integer
Dim groupptr As Integer, chptr As Integer


chptr = 1
startpos = 0
 For groupptr = 1 To groupnum - 1
    chptr = InStr(chptr, mytext, delim)
    If chptr = 0 Then
       fstrDField = ""
       Exit Function
    Else
       chptr = chptr + 1
    End If
 Next groupptr
startpos = chptr
endpos = InStr(startpos + 1, mytext, delim)
If endpos = 0 Then
   endpos = Len(mytext) + 1
End If


fstrDField = Mid$(mytext, startpos, endpos - startpos)


End Function


'-------------------
Function SetDefaultPrinter(strPrinterName As String) As Boolean
'-------------------


   Dim strDeviceLine As String
   Dim strBuffer     As String
   Dim lngbuf        As Long
    
  ' get the full device string
  '
   strBuffer = Space(1024)
   lngbuf = GetProfileString("PrinterPorts", strPrinterName, "", strBuffer, Len(strBuffer))
  
  'Write out this new printer information in
  ' WIN.INI file for DEVICE item
  If lngbuf > 0 Then
     
     strDeviceLine = strPrinterName & "," & _
                     fstrDField(strBuffer, Chr(0), 1) & "," & _
                     fstrDField(strBuffer, Chr(0), 2)
                     
     Call WriteProfileString("windows", "Device", strDeviceLine)
     SetDefaultPrinter = True
     
     ' Below is optional, and should be done. It updates the existing windows
     ' so the "default" printer icon changes. If you don't do the below..then
     ' you will often see more than one printer as the default! The reason *not*
     ' to do the SendMessage is that many open applications will now sense the change
     ' in printer. I vote to leave it in..but your case you might not want this.
     '
     
     'Call SendMessage(HWND_BROADCAST, WM_WININICHANGE, 0, ByVal "windows")
    
  Else
     SetDefaultPrinter = False
  End If
       
End Function


'-------------------
Function GetDefaultPrinter() As String
'-------------------


   Dim strDefault    As String
   Dim lngbuf        As Long


   strDefault = String(255, Chr(0))
   lngbuf = GetProfileString("Windows", "Device", "", strDefault, Len(strDefault))
   If lngbuf > 0 Then
      GetDefaultPrinter = fstrDField(strDefault, ",", 1)
   Else
      GetDefaultPrinter = ""
   End If


End Function


Public Sub ListPrinters()


   Debug.Print GetDefaultPrinter
   Debug.Print "------------"
   Debug.Print GetPrinters
   
End Sub


'-------------------
Function GetPrinters() As String
'-------------------
   
   ' this routine returns a list of printers, separated by
   ' a ";", and thus the results are suitable for stuffing into a combo box
   
   Dim strBuffer  As String
   Dim strOnePtr  As String
   Dim intPos     As Integer
   Dim lngChars   As Long
   
   strBuffer = Space(2048)
   lngChars = GetProfileString("PrinterPorts", vbNullString, "", strBuffer, Len(strBuffer))
   
   If lngChars > 0 Then
      intPos = InStr(strBuffer, Chr(0))
     Do While intPos > 1
        strOnePtr = Left(strBuffer, intPos - 1)
        strBuffer = Mid(strBuffer, intPos + 1)
        If GetPrinters <> "" Then GetPrinters = GetPrinters & vbCrLf
        'Debug.Print strOnePtr
        GetPrinters = GetPrinters & strOnePtr
        intPos = InStr(strBuffer, Chr(0))
        
     Loop
   Else
      GetPrinters = ""
   End If
   
 End Function


'-------------------
Public Sub PrinterSides(ByVal pyNum As Byte)
'-------------------
   'acPRDPSimplex = 1
   'acPRDPHorizontal =  2
   'acPRDPVertical = 3
   Select Case pyNum
    Case "": 'nothing to do
    Case 1
       rpt.Printer.Duplex = acPRDPSimplex
    Case 2
       rpt.Printer.Duplex = acPRDPHorizontal
    Case 3
       rpt.Printer.Duplex = acPRDPVertical
   End Select
   Printer.Duplex = pyNum
End Sub


Public Sub PrinterOrient()


'If CommonDialog1.Orientation = cdlLandscape Then
'       Printer.Orientation = cdlLandscape  '2
'Else
'       Printer.Orientation = cdlPortrait  '1
'End If
End Sub
 
Last edited:
Upvote 0
Ok,I have the properties for my settings but how does it go in the code above? Does it have to go at each sheet?
Code:
    ActiveSheet.PageSetup.PrintArea = ""
    With ActiveSheet.PageSetup
        .Orientation = xlLandscape
        .PaperSize = xlPaper11x17
        .BlackAndWhite = True
    End With
 
Upvote 0

Forum statistics

Threads
1,223,901
Messages
6,175,277
Members
452,629
Latest member
SahilPolekar

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