Excel Macro Printer Problem

Targa 911 Carrera

New Member
Joined
Jun 7, 2019
Messages
1
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.
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

sykes

Well-known Member
Joined
May 1, 2002
Messages
1,785
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
May 1, 2002
Messages
1,785
Office Version
  1. 365
Platform
  1. Windows
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:
Code:
application.Dialogs (xlDialogPrint).Show
 

Watch MrExcel Video

Forum statistics

Threads
1,130,418
Messages
5,642,009
Members
417,250
Latest member
spr1nger

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
Top