Hello, i have query i have this userform that is coded to only print out certian pages from a auto filter. First i select a network or local printer from the combo box and then press the print button. Remmeber the code only prints out selected pages from the auto filter automatically so i cannot use the xlprinterdialog. But the problem is that want the "Printer Information" shown when i select a printer from the combo box. In the code it shows a part where it says "Get Printer Information", but i do not understand how to get that information into a label and to work, Can you help me???
Here's the Code (i have shown what i mean in the code):
Thanks
Here's the Code (i have shown what i mean in the code):
Code:
Private Type OSVERSIONINFO
dwOSVersionInfoSize As Long
dwMajorVersion As Long
dwMinorVersion As Long
dwBuildNumber As Long
dwPlatformId As Long
szCSDVersion As String * 128
End Type
'Private Const VER_PLATFORM_WIN32s = 0 '//Change here in case - Win32s
'Private Const VER_PLATFORM_WIN32_WINDOWS = 1 '//Change here in case - Windows 95/98(?)
Private Const VER_PLATFORM_WIN32_NT = 2 '//Change here in case - Windows NT
Private Declare Function GetVersionEx Lib "kernel32" _
Alias "GetVersionExA" _
(lpVersionInformation As OSVERSIONINFO) As Long
'Heres the Printer Information part but i don't understand how to get this code to work in labels when a printer in selected from the Combo Box
Private Type PRINTER_INFO_1 '//Get Printer Information
flags As Long
pPDescription As Long
pName As Long
pComment As Long
End Type
Private Const PRINTER_ENUM_LOCAL = &H2
Private Const PRINTER_ENUM_CONNECTIONS = &H4 '
Private Declare Function EnumPrinters Lib "WINSPOOL.DRV" Alias "EnumPrintersA" _
(ByVal flags As Long, _
ByVal Name As String, _
ByVal Level As Long, _
pPrinterEnum As Any, _
ByVal cdBuf As Long, _
pcbNeeded As Long, _
pcReturned As Long) As Long
Private Declare Sub CoTaskMemFree Lib "ole32" (ByVal pv As Long)
Private Declare Sub MoveMemory Lib "kernel32" Alias "RtlMoveMemory" (Dest As Any, Source As Any, ByVal length&)
Public Function EnumPrinter() As Variant
Dim asPrinter() As String
enumPrinter_Engine PRINTER_ENUM_LOCAL, asPrinter
If isWindowsNT Then enumPrinter_Engine PRINTER_ENUM_CONNECTIONS, asPrinter
EnumPrinter = asPrinter
End Function
Public Sub enumPrinter_Engine(ByVal iiEnumType As Long, ByRef ioasPrinter() As String)
Dim abEnumBuffer() As Byte, cBufferSize As Long
Dim uPrinterInfo As PRINTER_INFO_1, cStructSize As Long
Dim lngPrinters As Long
Dim i As Long, lngStart As Long
Const lngLevel As Long = 1
Const lngPrinterMax As Long = 64
Call EnumPrinters(iiEnumType, vbNullString, lngLevel, ByVal 0&, 0, cBufferSize, lngPrinters)
If cBufferSize = 0 Then Exit Sub
ReDim abEnumBuffer(0 To cBufferSize - 1)
Call EnumPrinters(iiEnumType, vbNullString, lngLevel, abEnumBuffer(0), cBufferSize, cBufferSize, lngPrinters)
If lngPrinters = 0 Then Exit Sub
If CntArr(ioasPrinter()) = 0 Then
lngStart = 0
ReDim ioasPrinter(0 To lngPrinters - 1)
Else
lngStart = UBound(ioasPrinter) + 1
ReDim Preserve ioasPrinter(0 To lngStart + lngPrinters - 1)
End If
cStructSize = Len(uPrinterInfo)
For i = 0 To lngPrinters - 1
Call MoveMemory(uPrinterInfo, abEnumBuffer(cStructSize * i), cStructSize)
ioasPrinter(lngStart + i) = GetPrinterStrings(uPrinterInfo.pName, lngPrinterMax)
Next i
End Sub
Private Function GetPrinterStrings(ByVal ipString As Long, inBytes As Long) As String
ReDim abBuffer(0 To inBytes) As Byte
Call MoveMemory(abBuffer(0), ByVal ipString, inBytes)
GetPrinterStrings = StrConv(abBuffer(), vbUnicode)
GetPrinterStrings = Left$(GetPrinterStrings, InStr(GetPrinterStrings, vbNullChar) - 1)
End Function
Private Property Get isWindowsNT() As Boolean
Dim OsVers As OSVERSIONINFO
OsVers.dwOSVersionInfoSize = Len(OsVers)
Call GetVersionEx(OsVers)
isWindowsNT = (OsVers.dwPlatformId = VER_PLATFORM_WIN32_NT)
End Property
Public Function CntArr(ByVal vntArr As Variant, Optional ByVal lngDimention As Long = 1) As Long
On Error Goto Terminate
CntArr = 0
CntArr = UBound(vntArr, lngDimention) - LBound(vntArr, lngDimention) + 1
Exit Function
Terminate:
Exit Function
End Function
'Heres the Selected Pages from auto filter to be printed out automatically
Private Sub cmdPrint_Click()
ActiveSheet.Unprotect ("IKEA1")
Sheets("Day Handover").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Sheets("Activities Pick Sheet").Select
Selection.AutoFilter Field:=1, Criteria1:="3"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Selection.AutoFilter Field:=1, Criteria1:="4"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Selection.AutoFilter Field:=1, Criteria1:="6"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Selection.AutoFilter Field:=1, Criteria1:="8"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Selection.AutoFilter Field:=1, Criteria1:="9"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Selection.AutoFilter Field:=1, Criteria1:="10"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Selection.AutoFilter Field:=1, Criteria1:="40"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Selection.AutoFilter Field:=1, Criteria1:="50"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Selection.AutoFilter Field:=1, Criteria1:="F/S"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Selection.AutoFilter Field:=1, Criteria1:="S/S"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Selection.AutoFilter Field:=1
ActiveSheet.Protect "IKEA1", DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowInsertingRows:=True, AllowDeletingRows:=True, AllowSorting:=True, _
AllowFiltering:=True
Unload Me
End Sub
'Heres the Combo Box code to show the network printers
Private Sub UserForm_Initialize()
Dim strsPrinterName As Variant
Me.Caption = "Please select printer"
With Combobox1
For Each strsPrinterName In EnumPrinter()
.AddItem strsPrinterName
Next
End With
Dim strspComment As Variant
Me.Caption = "Please select printer"
With lblCom
For Each strspComment In EnumPrinter()
.Caption = PRINTER_INFO_1
Next
End With
End Sub
Private Sub cmdClose_Click()
Unload Me
End Sub
Thanks