View Full Version : Solved: Printing Options

07-26-2004, 03:18 PM
Hi,:help wanted:
Before progressing with this further, I could do with some feedback. Any problems running the attached, which should list available printers complete with registry "key", and set selected printer as active printer.

Jacob Hilderbrand
07-26-2004, 03:47 PM
Works fine with 2002. Also VBA has a built in way to show the printers, though your way will provide more information.


07-27-2004, 09:55 AM
I'm using xl2000, and it does work (mostly). I did notice a couple things though:

-Clicking on the printer changed the Active Printer, without having to hit the Make Active button
-Just a suggestion, but the userform could use a "close" button (rather than the tiny x)
-I clicked directly beneath my last printer, and it selected a blank line. That gave me a 1004 error, Method 'ActivePrinter' of object '_Application' failed, as it tried to make that my active printer

Other than that it's cool, I like that a lot

Zack Barresse
07-27-2004, 10:16 AM
Also tested on 2002, agree with Mike's points.

Very cool. :)

07-27-2004, 12:06 PM
Thanks all,
Especially Matt for finding the bugs: hopefully resolved by "ReDim MyPrinters(nEntries - 1)". A "Close" button and further options will follow. Suggestions welcome!

07-27-2004, 12:14 PM
Hey MD,
I'm not sure what you're going to be using the userform for, but maybe you could include radio buttons for landscape/portrait/margins?. Maybe even possibly a print button so a user can print to a printer that's not their active printer, without having to change their active printer.
By the way, that avatar cracks me up

07-27-2004, 12:19 PM
Also, post the final project, it'll be real useful around my office (each of us have a minimum of 2 printers we print to regularly).
Is there a way to set a nickname for the printers? I could even just add that in myself for the people around here, so they would only have to choose between "Big printer" and "Small printer" as is the common reference around here :)

tommy bak
07-27-2004, 12:33 PM
Tested on 2002. Does not work.
It's not possible to click on a printer without having an error 1004 in this line:

Application.ActivePrinter = ListBox1

debugging tell me the Listbox1 value is "HPlaserjet 6L PLC on"
I'm not connected to the printer via network (using local printer) so perhaps the little word "on" is the caurse of the error
No, it's not that. Problem is in Function EnumeratePrinters.
MyKey returns "" and should return LPT1:

Tommy Bak

07-27-2004, 12:37 PM
Hi Matt.
The rational behind this is that at work our network printer has 5 different papers, with options for Page 1 on Tray x, Rest on tray Y, watermark etc. options as well. The only workable solution I've found for this (in Excel) is to set up multiple versions of the printer, each with their own settings.
The form is a shortcut to all these options. My own PC, as the originator, has a notional 12 printers set up, although I only print to 2 physical machines.
The Key bit is required, because XP appears to allocate these alphabetically, so each PC may be different, if they have access to another printer.

BTW, The well dressed dog is a Rhodesian Ridgehound called Mille

07-27-2004, 12:48 PM
Hi Tommy,
Thanks for the feedback. I would have expected "HPlaserjet 6L PLC on LPT1:" What is the path to your printer?
This is the location in the registry I'm looking at to get the result:
HKey_Current_User\Software\Microsoft\Windows NT\CurrentVersion\Devices
Do you have a printer setting here?

Anyone else found this?

tommy bak
07-27-2004, 12:59 PM
mdmackillop -> Windows NT. Maybe that's the reason. I'm still using win98, so your key doesn't exits here.
When I get back to work, I'll try your solution on danish win2000 and excel2000.
Looks promising, and can be used at work.


07-27-2004, 01:00 PM
re the printer names, I think you can change these in the printer properties accessed from Control Panel or wherever. No doubt an "alias" could be set up, but I think it would complicate things with no advantage.

07-27-2004, 01:10 PM
Thanks Tommy,
From memory, Win 98 didn't need the "on xyz:" text, you could try changing this line
MyPrinters(I) = PName & " on " & MyKey
MyPrinters(I) = PName
Let me know if this works.

Anyone have a code line to return the Windows version?

07-27-2004, 03:05 PM
Copy of updated UserForm


Zack Barresse
07-27-2004, 03:17 PM
Anyone have a code line to return the Windows version?

hIcon As Long
iIcon As Long
dwAttributes As Long
szDisplayName As String * MAX_PATH
szTypeName As String * 80
End Type

Public Const SHGFI_EXETYPE = &H2000

Public Declare Function SHGetFileInfo Lib "shell32.dll" Alias "SHGetFileInfoA" _
( ByVal pszPath As String, ByVal dwFileAttributes As Long, _
psfi As SHFILEINFO, ByVal cbFileInfo As Long, ByVal uFlags As Long ) As _

The high word contains the major and the minor number of the required Windows version. It is 400 for Windows 9x/NT 4.0, 500 for Windows 2000, 30A for Windows 3.x.

dw = SHGetFileInfo(exename, 0, sfi, Len(sfi), SHGFI_EXETYPE)
hiWord = dw \ &H10000

Resource (http://www.devx.com/vb2themax/Tip/18302)

07-27-2004, 03:47 PM
Thanks Zack,
Unfortunately I couldn't make anything of this (and it's described as intermediate!)

Zack Barresse
07-27-2004, 04:06 PM
How about this one:


07-27-2004, 04:18 PM
Thanks Zack,
That works. I found a VB.Net version in the KB, but missed that one.

All this to remove "on " from a piece of code!


Zack Barresse
07-27-2004, 04:28 PM
LOL! It'd almost be easier to check to those characters and handle it right there. Geesh! I know there was code to check for Mac versions also, but I haven't looked too hard yet; I used to have it saved, but can't find it.

07-27-2004, 05:30 PM
What about Application.OperatingSystem ?

07-28-2004, 01:39 PM
Hi Tony,
I looked at that, but could not determine the possible replies, making the code manipulation difficult. Fingers crossed that the other solution works.

I can't test this well at home, so any feedback welcome, particularly older windows versions

07-28-2004, 09:32 PM
This worx fine on Win98SE.

Only found one major bug - a "false" that had crept in from somewhere after "userform1.show" - deleting the 'false' allowed it to work

Perhaps an initial form prompting you to select the print area first before clicking the main button would be helpful? Otherwise....COOL! :bink:

07-29-2004, 12:25 AM
Hi John,
The False (on 2000 anyway) allows you to access the worksheet while the form is open. It makes the form "non-modal". Can you make print area selections etc with the form on screen?

07-29-2004, 12:39 AM
The "false" gave an error message on Win98SE and went into debug mode, and no it's modal - I need to either exit the print dialog or make the selection prior to opening it. That's why my comment about some sort of instructions re making selections before going to the print option. Never-the-less it's really quite good, ok for me to keep a copy for future use? Saves me a lot of work heh-heh-heh :bink:

07-29-2004, 01:07 AM
PS: Just looked thru the help file, msoModeModeless is the operative command for Office97, but this only applies to the balloon helper :bink:

07-29-2004, 02:32 AM
Maybe Office97? There all userforms are modal, you can't give it a parameter there.

07-29-2004, 06:12 AM
I know I said that the previous one was cool, but this one is definately cool!

Something I noticed (xl2000). If a single cell is selected on the worksheet and I click the Select/Clear Print Area checkbox, it brings up an warning box telling me I have a single cell selected, and if I meant that click OK otherwise click cancel. When I click cancel, I got a runtime 1004: Unable to set the PrintArea property of the PageSetup class. I see you have "To be fixed" in the code, I just wanted to make sure you knew about that. Maybe you could put your own error handling msgbox saying the same thing, and on cancel uncheck the box and exit sub?

Again, nice job though!

07-29-2004, 07:46 AM
Hi Ya'll,

I found some code for getting the printers without th on .... etc.
As you can tell it is from ALL-API.net

' Get information about all of the local printers using structure 1. Note how
' the elements of the array are loaded into an array of data structures manually. Also
' note how the following special declares must be used to allow numeric string pointers
' to be used in place of strings:
Private Declare Function lstrcpy Lib "kernel32.dll" Alias "lstrcpyA" (ByVal lpString1 As String, ByVal lpString2 As Long) As Long
Private Declare Function lstrlen Lib "kernel32.dll" Alias "lstrlenA" (ByVal lpString As Long) As Long
Private Declare Function EnumPrinters Lib "winspool.drv" Alias "EnumPrintersA" (ByVal flags As Long, ByVal name As String, ByVal Level As Long, pPrinterEnum As Long, ByVal cdBuf As Long, pcbNeeded As Long, pcReturned As Long) As Long
Private Type PRINTER_INFO_1
flags As Long
pDescription As String
pName As String
pComment As String
End Type
Sub UserForm_Initialize()
'KPD-Team 1999
'URL: http://www.allapi.net/
'E-Mail: KPDTeam@Allapi.net
Dim longbuffer() As Long ' resizable array receives information from the function
Dim printinfo() As PRINTER_INFO_1 ' values inside longbuffer() will be put into here
Dim numbytes As Long ' size in bytes of longbuffer()
Dim numneeded As Long ' receives number of bytes necessary if longbuffer() is too small
Dim numprinters As Long ' receives number of printers found
Dim c As Integer, retval As Long ' counter variable & return value
'Me.AutoRedraw = True 'Set current graphic mode to persistent
' Get information about the local printers
numbytes = 3076 ' should be sufficiently big, but it may not be
ReDim longbuffer(0 To numbytes / 4) As Long ' resize array -- note how 1 Long = 4 bytes
retval = EnumPrinters(PRINTER_ENUM_LOCAL, "", 1, longbuffer(0), numbytes, numneeded, numprinters)
If retval = 0 Then ' try enlarging longbuffer() to receive all necessary information
numbytes = numneeded
ReDim longbuffer(0 To numbytes / 4) As Long ' make it large enough
retval = EnumPrinters(PRINTER_ENUM_LOCAL, "", 1, longbuffer(0), numbytes, numneeded, numprinters)
If retval = 0 Then ' failed again!
Debug.Print "Could not successfully enumerate the printes."
End ' abort program
End If
End If
' Convert longbuffer() data into printinfo()
If numprinters <> 0 Then ReDim printinfo(0 To numprinters - 1) As PRINTER_INFO_1 ' room for each printer
For c = 0 To numprinters - 1 ' loop, putting each set of information into each element
' longbuffer(4 * c) = .flags, longbuffer(4 * c + 1) = .pDescription, etc.
' For each string, the string is first buffered to provide enough room, and then the string is copied.
printinfo(c).flags = longbuffer(4 * c)
printinfo(c).pDescription = Space(lstrlen(longbuffer(4 * c + 1)))
retval = lstrcpy(printinfo(c).pDescription, longbuffer(4 * c + 1))
printinfo(c).pName = Space(lstrlen(longbuffer(4 * c + 2)))
retval = lstrcpy(printinfo(c).pName, longbuffer(4 * c + 2))
printinfo(c).pComment = Space(lstrlen(longbuffer(4 * c + 3)))
retval = lstrcpy(printinfo(c).pComment, longbuffer(4 * c + 3))
Next c
' Display name of each printer
For c = 0 To numprinters - 1
Debug.Print "Name of printer"; c + 1; " is: "; printinfo(c).pName
Next c
End Sub

This code could be modified for this project.

07-29-2004, 04:30 PM
Thanks Tommy,
Isn't it amazing that the less code you want, the longer the code you need! Anyway, tonight three lovely pints (454ml) of Guinness have taken priority, so I'll look at your response tomorrow hopefully!

07-30-2004, 06:07 AM
Glad you have your priorities right:)

I tested on Win98SE w/ Office 2K, it didn't gve the "printer name on" so I thought it would help. I'll test on Win2K w/ Office 2003 and let you know.

06-21-2007, 03:11 AM
:chat:Today pad printing has reached a technical advanced state and the range is quite diversified. Compared to the method described in the historical section not much has changed. The basic procedure has remained the same.

Since the late 1960's an old printing method,which had been particularly well established in the watch-making industry, has been an unexpected boom. Pad printing was discovered for newer, broader applications and with the help of silicone pads and new machine constructions it truly blossomed. Pad printing machine manufacturers sprung up like mushrooms and satisfied the real market need for printing and decorating parts in a simple and inexpensive way. Pad printing allows new designing possibilities for engineers and designers as a result, the products are becoming more attractive and functional.

If you are interested to know more about Pad Printing then please visit

Best in Quality, Best in service, Best in complete solutions.

We specialize in Machines & Consumables for Surface Decoration-Printing-Marking,Glass Decoration also Pads Plates Cliches inks for pad printing.

At AIM we are committed to providing printing solutions & machines for printing, marking & surface decoration of finished products & components. We have machines & solutions for various products & industries.

AIM brings to you pad printing & customized pad printing solutions from the European Leader, microPrint- Switzerland. Swiss Machines are world renowned for their precision, accuracy & craftsmanship.

The fantastically accurate pad printing machines for printing 1-8 colors & many more from microPrint are now available in India. With our more than 20 years experience in printing industry our experts are best placed to guide you in the choice of right Pad Printer for your application

"Your Product's are valueless, If your brand or logo is not printed clearly on your gift's and promotional articles. Your brand is your asset. Value it."

Pad printing machines, Printing Machine, Printing inks, Pad printing supplies, Glass decoration