Excel Macro Printer Problem

[Targa 911 Carrera]

I have a Time Clock Excel spreadsheet that I wrote so employees can click an icon button to record their times as they clock in and out by via an Excel macro. At the end of the pay period the store manage needs to print the payroll sheet and he is not computer savvy at all, therefore I wrote a simple macro that selects the cells I want him to print, sets the printer to print in Landscape Orientation, select the correct printer, etc. again by clicking an icon button to run the PrintTimeSheetMacro. The problem I’m have is the IT department in the home office in another state keeps changing the network settings defaults on the only printer connected to the computer I can access. The code lines that I’ve created are:

Application.ActivePrinter = "\\CT4850\CashOffice on Ne02:"
Selection.PrintOut Copies:=1, ActivePrinter:="\\CT3511\CashOffice on Ne02:" _
, Collate:=True

The IT department keeps changing the “Ne02:” back and forth to “Ne07:” every few weeks and they change the default printer to "Scanner" not "CashOffice". So far I’ve been changing the 2 to a 7 or vice versa when the macro errors out and goes in to Debug mode. I can’t contact the IT department and request that they stop changing these setting, because I think they might not be happy with me writing Excel Macros on the company’s computers, but these macros make life a lot simpler at the store level.

I would like to know how I can pause the macro and have a dialog box display instructing the user to select the right printer.

 

[sykes]

HI

I poked around (because I thought you may be able to refer to the printer by either MAC address, or IP address, instead) and found this on Ozgrid.
Posts numbered 12 & 13 seem to pretty much solve your issue, without the need for the dialogbox - you'd just need to call the function from within your code - to return the portion of the address that your looking for - and pass it on to your existing code.

The post's here: https://www.ozgrid.com/forum/forum/...al/84036-vba-code-to-print-to-specfic-printer

Hope this helps...

 

[sykes]

Did a bit more digging on your problem, and found that it's probably not your IT dept changing the printer port, but Windows itself.

Also, to answer your original question:

application.Dialogs (xlDialogPrint).Show