Solved: Printing Options

[mdmackillop]

Hi, 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.
MD

[Jacob Hilderbrand]

Works fine with 2002. Also VBA has a built in way to show the printers, though your way will provide more information.

[vba]
Application.Dialogs(xlDialogPrinterSetup).Show
[/vba]

[mvidas]

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]

Also tested on 2002, agree with Mike's points.

Very cool.

[mdmackillop]

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!
MD

[mvidas]

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

[mvidas]

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]

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
[Edited]
No, it's not that. Problem is in Function EnumeratePrinters.
MyKey returns "" and should return LPT1:


BR
Tommy Bak

[mdmackillop]

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.
MD

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

[mdmackillop]

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?
MD

Anyone else found this?

[tommy bak]

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.

BR
Tommy

[mdmackillop]

Matt,
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.
MD

[mdmackillop]

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

Anyone have a code line to return the Windows version?
MD

[mdmackillop]

Copy of updated UserForm

[Zack Barresse]

Anyone have a code line to return the Windows version?
MD

[vba]
Type SHFILEINFO
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 _
Long [/vba]

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.

[vba]
dw = SHGetFileInfo(exename, 0, sfi, Len(sfi), SHGFI_EXETYPE)
hiWord = dw \ &H10000
[/vba]

Resource

[mdmackillop]

Thanks Zack,
Unfortunately I couldn't make anything of this (and it's described as intermediate!)
MD

[Zack Barresse]

How about this one:

http://support.microsoft.com/default...NoWebContent=1

[mdmackillop]

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!

MD

[Zack Barresse]

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.

[TonyJollans]

What about Application.OperatingSystem ?