Tidying up code - printing macro

[JayM]

I need help tidying up my code or making it easier to reference.

I have a few macros that do similar things each one sets a different paper
tray on the printer.
You will see that I have written it so that it checks the printers location
so it knows which printer tray to select.
Is there a way I can change it to references the printers once instead of in
each macro.
Many thanks for your help in advance

Code:Sub Print_LH()
sLocation = GetPrinterDetails.Location
'HP Printers
If InStr(sLocation, "TH20194") Or InStr(sLocation, "TH20485") Or _
InStr(sLocation, "TH30412") Or InStr(sLocation, "TH20192") Or _
InStr(sLocation, "TH20482") Or InStr(sLocation, "TH30413") Or _
InStr(sLocation, "TH20484") Or InStr(sLocation, "TH20193") Or _
InStr(sLocation, "TH20195") Or InStr(sLocation, "TH30142") Or _
InStr(sLocation, "TH20164") Or InStr(sLocation, "TH30041") Or _
InStr(sLocation, "TH20165") Then
With ActiveDocument.PageSetup
.FirstPageTray = 2
.OtherPagesTray = 2
End With
'Sharp MX4500 Printers
ElseIf InStr(sLocation, "TH30441") Or InStr(sLocation, "TH30449") Or _
InStr(sLocation, "TH30143") Or InStr(sLocation, "TH30461") Then
With ActiveDocument.PageSetup
.FirstPageTray = 259
.OtherPagesTray = 259
End With
'Sharp 450 Printers
ElseIf InStr(sLocation, "TH30141") Or InStr(sLocation, "TH30442") Or _
InStr(sLocation, "TH30143") Or InStr(sLocation, "TH30245") Then
With ActiveDocument.PageSetup
.FirstPageTray = 2
.OtherPagesTray = 2
End With
Else: MsgBox "The Printer you are trying to use is either not networked or
incorrectly set up." _
& vbCrLf & "In the first instance please use the File|Print Menu (Ctrl
P)." _
& vbCrLf & "In the second instance please contact IT on Ext 109. " &
sLocation, vbOKOnly
End If
Application.PrintOut filename:="", Range:=wdPrintAllDocument,
Item:=wdPrintDocumentContent, Copies:=1, Pages:="",
PageType:=wdPrintAllPages, Collate:=True, Background:=False,
PrintToFile:=False
End Sub

 

[Jonathan West]

You can produce a function PrinterType which does the location checking.
Depending on the value returned by PrinterType, you can then act
accordingly. By the way, I suspect that you may at some point want to change
or add locations as you move or install new printers, so it is a very good
idea to put all this code into a single routine, so it only needs to be
changed in one place. Also, there are other ways of making your code easier
to maintain. Try this:

Function PrinterType() As String
Dim sLocation as String
Dim vHPLocations as Variant
Dim vSharpMXLocations as Variant
Dim vSharp450Locations as Variant
Dim i As Long

vHPLocations = Array("TH20194", "|TH20485", "TH30412", "TH20192", _
"TH20482", "TH30413", "TH20484", "TH20193", "TH20195", "TH30142", _
"TH20164", "TH30041", "TH20165")
vSharpMXLocations = Array("TH30441", "TH30449", "TH30143", "TH30461")
vSharp450Locations = Array("TH30141", "TH30442", "TH30143", "TH30245")

sLocation = GetPrinterDetails.Location

For i = 0 to UBound(vHPLocations)
If Instr(sLocation, vHPLocations(i)) > 0 Then
FunctionPrinterType = "HP"
Exit Function
End If
Next i

For i = 0 to UBound(vSharpMXLocations)
If Instr(sLocation, vSharpMXLocations(i)) > 0 Then
FunctionPrinterType = "Sharp MX4500"
Exit Function
End If
Next i


For i = 0 to UBound(vSharp450Locations)
If Instr(sLocation, vSharp450Locations(i)) > 0 Then
FunctionPrinterType = "Sharp 450"
Exit Function
End If
Next i

End Function

The function returns an empty string if the location isn't recognised. The
function is easily edited - all you need do is change the elements listed in
the Array function as necessary.

Then, the Print_LH routine can look like this

Code:Sub Print_LH()
Dim sPrinterType as String

sPrinterType = PrinterType

Select Case sPrinterType

Case "HP"

With ActiveDocument.PageSetup
.FirstPageTray = 2
.OtherPagesTray = 2
End With

Case "Sharp MX4500"

With ActiveDocument.PageSetup
.FirstPageTray = 259
.OtherPagesTray = 259
End With


Case "Sharp 450"

With ActiveDocument.PageSetup
.FirstPageTray = 2
.OtherPagesTray = 2
End With

Case Else
MsgBox "The Printer you are trying to use is either not networked or
incorrectly set up." _
& vbCrLf & "In the first instance please use the File|Print Menu (Ctrl
P)." _
& vbCrLf & "In the second instance please contact IT on Ext 109. " &
sLocation, vbOKOnly
Exit Sub

End Select

Application.PrintOut filename:="", Range:=wdPrintAllDocument, _
Item:=wdPrintDocumentContent, Copies:=1, Pages:="", _
PageType:=wdPrintAllPages, Collate:=True, Background:=False, _
PrintToFile:=False
End Sub

 

[JayM]

Jonathan
Another superb reply. I am so grateful to you for your help with this
Many thanks
JayM