| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Print Macro
Glad you got it working.
Very Basic User wrote: 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" wrote: 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. Very Basic User wrote: 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" wrote: 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 Very Basic User wrote: 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 -- Thank you for your time! John -- Dave Peterson . -- Dave Peterson . -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| print button macro & specific tabs to print | Excel Worksheet Functions | |||
| need a print macro to print only a certain number of pages | Excel Discussion (Misc queries) | |||
| Pivot Table macro to set print area and print details of drill down data | Excel Discussion (Misc queries) | |||
| Macro to open print window and set to print entire workbook | Excel Discussion (Misc queries) | |||
| Create a print macro that would automatically select print area? | Excel Worksheet Functions | |||
Hybrid Mode
