Printable View
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
This code could be modified for this project.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
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.