Printing Options

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

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

Code:

---

Application.Dialogs(xlDialogPrinterSetup).Show

---

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

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

Very cool. :)

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

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

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 :)

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

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

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?

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

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

Thanks Tommy,
From memory, Win 98 didn't need the "on xyz:" text, you could try changing this line

Code:

---

MyPrinters(I) = PName & " on " & MyKey

---

to

Code:

---

MyPrinters(I) = PName

---

Let me know if this works.
MD

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

Copy of updated UserForm

:type

Quote:

---

Originally Posted by mdmackillop

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

---

Code:

---

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

---

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

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

How about this one:

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

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

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.

What about Application.OperatingSystem ?

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


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

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:

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

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:

PS: Just looked thru the help file, msoModeModeless is the operative command for Office97, but this only applies to the balloon helper :bink:

Maybe Office97? There all userforms are modal, you can't give it a parameter there.

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!

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

Code:

---


' 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
Const PRINTER_ENUM_LOCAL = &H2

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.

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

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.