Print Macro

  • Thread starter Very Basic User
  • Start date
V

Very Basic User

Hello All, My code below is rather lenghty to just print several graphs, but
I still have a problem. This is used by many people within the plant. The
code only works on a few computers. I tried to record a simple print macro on
the ones that it does not work on and found what I think my problem is. (The
Ne04) used on my computer... I've since looked at many pc's and have found
ranged from Ne01 through Ne08. Is there a simple change that I can add that
all users will be able to run the macro successfully from any pc?

Thanks in advance,
John


Sub EditedPrintAll()
'
' EditedPrintAll Macro
'

'
Selection.ShapeRange.Item(1).Hyperlink.Follow NewWindow:=False,
AddHistory _
:=True
Range("A1").Select
Application.ActivePrinter = "\\mrafp1\MRA-ADMINCP1 on Ne04:"
ExecuteExcel4Macro _
"PRINT(1,,,1,,,,,,,,2,""\\mrafp1\MRA-ADMINCP1 on
Ne04:"",,TRUE,,FALSE)"
Sheets("Line 3 Graph").Select
Range("A1").Select
ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,2,,,TRUE,,FALSE)"
Sheets("Line 6 Graph").Select
Range("A1").Select
ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,2,,,TRUE,,FALSE)"
Sheets("Line 9 Graph").Select
Range("A1").Select
ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,2,,,TRUE,,FALSE)"
ActiveWindow.ScrollWorkbookTabs Sheets:=1
ActiveWindow.ScrollWorkbookTabs Sheets:=1
ActiveWindow.ScrollWorkbookTabs Sheets:=1
ActiveWindow.ScrollWorkbookTabs Sheets:=1
ActiveWindow.ScrollWorkbookTabs Sheets:=1
ActiveWindow.ScrollWorkbookTabs Sheets:=1
ActiveWindow.ScrollWorkbookTabs Sheets:=1
ActiveWindow.ScrollWorkbookTabs Sheets:=1
ActiveWindow.ScrollWorkbookTabs Sheets:=1
Sheets("Line 10 Graph").Select
Range("A1").Select
ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,2,,,TRUE,,FALSE)"
Sheets("Line 2 Graph").Select
Range("A1").Select
ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,2,,,TRUE,,FALSE)"
Sheets("Line 5 Graph ").Select
Range("A1").Select
ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,2,,,TRUE,,FALSE)"
Sheets("Line 7 Graph").Select
Range("A1").Select
ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,2,,,TRUE,,FALSE)"
Sheets("Line 8 Graph").Select
Range("A1").Select
ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,2,,,TRUE,,FALSE)"
Selection.ShapeRange.Item(1).Hyperlink.Follow NewWindow:=False,
AddHistory _
:=True
Range("A1").Select
End Sub
 
D

Dave Peterson

When I do this kind of thing, I'll let the user decide what printer to use.

Do you really want to force the users from all around the plant to use that
single printer?

If you do, you could just show a dialog and let them check/verify their printer
choices:

Application.Dialogs(xlDialogPrinterSetup).Show

But if you wanted to force the users to use that printer that you chose, you
could use:

Option Explicit
Sub testme()
Dim UseThisPrinter As String
Dim CurPrinter As String

'save their current printer
UseThisPrinter = GetPrinter(myPrinterName:="\\mrafp1\MRA-ADMINCP1 on Ne")

If UseThisPrinter = "" Then
MsgBox "Printer not found--what should happen"
Exit Sub '???
End If

'save the current printer
CurPrinter = Application.ActivePrinter

'and change (temporarily to the network printer you want)
Application.ActivePrinter = UseThisPrinter

'your code to print


'change their printer back to what they like
Application.ActivePrinter = CurPrinter

End Sub
Function GetPrinter(myPrinterName As String) As String

Dim iCtr As Long
Dim myStr As String
Dim FoundIt As Boolean
Dim CurPrinter As String

CurPrinter = Application.ActivePrinter

FoundIt = False
For iCtr = 0 To 99
On Error Resume Next
myStr = myPrinterName & Format(iCtr, "00") & ":"
Application.ActivePrinter = myStr
If Err.Number = 0 Then
FoundIt = True
Exit For
Else
'keep looking
Err.Clear
End If
Next iCtr
On Error GoTo 0

Application.ActivePrinter = CurPrinter

If FoundIt = True Then
GetPrinter = myStr
Else
GetPrinter = ""
End If

End Function
 
V

Very Basic User

Hello Dave, the select printer option is a great fix! Thank you. There is one
error though that I can't figure out. The code is pasted again below. When I
run the macro, every thing works great if I select a printer and then print,
but if I select "cancel" on the printer selection diolog box, it still
prints. Is there a line of code missing to allow user to cancel?

Sub EditedPrintAll()
'
' EditedPrintAll Macro
'

'


Application.Dialogs(xlDialogPrinterSetup).Show
Sheets("Line 1 Graph ").Select
Range("A1").Select
ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,2,,,TRUE,,FALSE)"
Sheets("Line 3 Graph").Select
Range("A1").Select
ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,2,,,TRUE,,FALSE)"
Sheets("Line 6 Graph").Select
Range("A1").Select
ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,2,,,TRUE,,FALSE)"
Sheets("Line 9 Graph").Select
Range("A1").Select
ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,2,,,TRUE,,FALSE)"
ActiveWindow.ScrollWorkbookTabs Sheets:=1
Sheets("Line 10 Graph").Select
Range("A1").Select
ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,2,,,TRUE,,FALSE)"
Sheets("Line 2 Graph").Select
Range("A1").Select
ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,2,,,TRUE,,FALSE)"
Sheets("Line 5 Graph ").Select
Range("A1").Select
ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,2,,,TRUE,,FALSE)"
Sheets("Line 7 Graph").Select
Range("A1").Select
ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,2,,,TRUE,,FALSE)"
Sheets("Line 8 Graph").Select
Range("A1").Select
ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,2,,,TRUE,,FALSE)"
Range("A1").Select
Sheets("Navigation").Select
Range("A1").Select
End Sub

--
Thank you for your time!
John


Dave Peterson said:
When I do this kind of thing, I'll let the user decide what printer to use.

Do you really want to force the users from all around the plant to use that
single printer?

If you do, you could just show a dialog and let them check/verify their printer
choices:

Application.Dialogs(xlDialogPrinterSetup).Show

But if you wanted to force the users to use that printer that you chose, you
could use:

Option Explicit
Sub testme()
Dim UseThisPrinter As String
Dim CurPrinter As String

'save their current printer
UseThisPrinter = GetPrinter(myPrinterName:="\\mrafp1\MRA-ADMINCP1 on Ne")

If UseThisPrinter = "" Then
MsgBox "Printer not found--what should happen"
Exit Sub '???
End If

'save the current printer
CurPrinter = Application.ActivePrinter

'and change (temporarily to the network printer you want)
Application.ActivePrinter = UseThisPrinter

'your code to print


'change their printer back to what they like
Application.ActivePrinter = CurPrinter

End Sub
Function GetPrinter(myPrinterName As String) As String

Dim iCtr As Long
Dim myStr As String
Dim FoundIt As Boolean
Dim CurPrinter As String

CurPrinter = Application.ActivePrinter

FoundIt = False
For iCtr = 0 To 99
On Error Resume Next
myStr = myPrinterName & Format(iCtr, "00") & ":"
Application.ActivePrinter = myStr
If Err.Number = 0 Then
FoundIt = True
Exit For
Else
'keep looking
Err.Clear
End If
Next iCtr
On Error GoTo 0

Application.ActivePrinter = CurPrinter

If FoundIt = True Then
GetPrinter = myStr
Else
GetPrinter = ""
End If

End Function
Click to expand...
 
D

Dave Peterson

Cancel means that they don't want to change the current printer. If they're
already pointing to your favorite, you wouldn't want to cancel that, right?

You could as them if they're sure before proceding:

dim Resp as long
....a bunch of code...


resp = Msgbox(Prompt:="Do you really, really want to print?", _
buttons:=vbyesno)

if resp = vbno then
exit sub
end if

do the rest ...

===============

But if you want to test to see if they canceled that print dialog, you could use
(I wouldn't!):

Dim resp As Boolean
.... a bunch of code...
resp = Application.Dialogs(xlDialogPrinterSetup).Show
If resp = False Then
Exit Sub
End If

But if my printer were already correct, I'd just hit cancel.
Hello Dave, the select printer option is a great fix! Thank you. There is one
error though that I can't figure out. The code is pasted again below. When I
run the macro, every thing works great if I select a printer and then print,
but if I select "cancel" on the printer selection diolog box, it still
prints. Is there a line of code missing to allow user to cancel?

Sub EditedPrintAll()
'
' EditedPrintAll Macro
'

'

Application.Dialogs(xlDialogPrinterSetup).Show
Sheets("Line 1 Graph ").Select
Range("A1").Select
ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,2,,,TRUE,,FALSE)"
Sheets("Line 3 Graph").Select
Range("A1").Select
ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,2,,,TRUE,,FALSE)"
Sheets("Line 6 Graph").Select
Range("A1").Select
ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,2,,,TRUE,,FALSE)"
Sheets("Line 9 Graph").Select
Range("A1").Select
ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,2,,,TRUE,,FALSE)"
ActiveWindow.ScrollWorkbookTabs Sheets:=1
Sheets("Line 10 Graph").Select
Range("A1").Select
ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,2,,,TRUE,,FALSE)"
Sheets("Line 2 Graph").Select
Range("A1").Select
ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,2,,,TRUE,,FALSE)"
Sheets("Line 5 Graph ").Select
Range("A1").Select
ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,2,,,TRUE,,FALSE)"
Sheets("Line 7 Graph").Select
Range("A1").Select
ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,2,,,TRUE,,FALSE)"
Sheets("Line 8 Graph").Select
Range("A1").Select
ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,2,,,TRUE,,FALSE)"
Range("A1").Select
Sheets("Navigation").Select
Range("A1").Select
End Sub
Click to expand...
 
V

Very Basic User

I think you know more about what I want than I do. This is perfect! I thought
the cancel option would cancel the print job entirely and didn't realize that
is just said cancel selecting a different printer. I did want the user to
have the option to say "No I don't want to print at this time" so I added the
message box with the are you sure yes or no and it does exactly what I need.
( I can't thank you enough!)
--
Thank you for your time!
John


Dave Peterson said:
Cancel means that they don't want to change the current printer. If they're
already pointing to your favorite, you wouldn't want to cancel that, right?

You could as them if they're sure before proceding:

dim Resp as long
....a bunch of code...


resp = Msgbox(Prompt:="Do you really, really want to print?", _
buttons:=vbyesno)

if resp = vbno then
exit sub
end if

do the rest ...

===============

But if you want to test to see if they canceled that print dialog, you could use
(I wouldn't!):

Dim resp As Boolean
.... a bunch of code...
resp = Application.Dialogs(xlDialogPrinterSetup).Show
If resp = False Then
Exit Sub
End If

But if my printer were already correct, I'd just hit cancel.
Click to expand...
 
D

Dave Peterson

Glad you got it working.
I think you know more about what I want than I do. This is perfect! I thought
the cancel option would cancel the print job entirely and didn't realize that
is just said cancel selecting a different printer. I did want the user to
have the option to say "No I don't want to print at this time" so I added the
message box with the are you sure yes or no and it does exactly what I need.
( I can't thank you enough!)
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Print code 4
VBA for Dependents shortcut menu 0
Printing Simplex on a duplex printer. 0
Macro to Select text from a point given to a specific word 2
Word macro that can call cells out of Excel for a find and replace 0
Macro 3
Creating Pivot Table with Macro using Multiple Sheets 0
Saving a range for later reference 0

Top