Need Macro to Print Selected Wkshts

[J_F_K_F_C]

picture this... there is a column of checkboxes. each box is labelle
with a worksheet name. the user checks the names they want to prin
and hits "submit" and only those sheets print (without the prin
dialog). what code would the "Submit" button need? thank you i
advance

 

[BrianB]

1. Are checkboxes in a UserForm or worksheet ?
2. If worksheet did you use Forms or Controls toolbox ?
3. Do you mean that the checkbox label contains the worksheet name

 

[Tom Ogilvy]

That's some tight code Jon. How long did it take you to write that?

 

[Tom Ogilvy]

Guess I was just surprised it was posted without attribution since it
probably originated from John Walkenbach's site or perhaps from one of his
books: (but you explained why).

http://j-walk.com/ss/excel/tips/tip48.htm

Dave Phillips has also posted an altered version in the past (with
attribution), so that may have been the source - I didn't do a line by line
compare.

to pick a printer

application.Dialogs(xlDialogPrinterSetup).show

or to show the print dialog
application.Dialogs(xlDialogPrint).show (this includes the number of
copies box).

However, you can't harvest the users choice for copies from the dialog, and
these dialogs don't return values, they perform the action intended, so you
might have to build your own with a userform.

some resources:
http://support.microsoft.com/?ID=166008
ACC: Enumerating Local and Network Printers

Enumerating Windows' Available Ports
http://www.mvps.org/vbnet/code/enums/enumports.htm
=======================

Posting by KeepItcool

Option Explicit

Private Declare Function GetProfileString Lib "kernel32" _
Alias "GetProfileStringA" _
(ByVal lpAppName As String, ByVal lpKeyName As String, _
ByVal lpDefault As String, ByVal lpReturnedString As String, _
ByVal nSize As Long) As Long

Sub showlist()
MsgBox Join(PrinterList, vbNewLine)
End Sub


Function PrinterList()
Dim lRet As Long
Dim sBuffer As String
Dim lSize As Long
Dim avTmp As Variant
Dim aPrn() As String
Dim n%, sPrn$, sConn$, sPort$

'Get localized Connection string
avTmp = Split(Excel.ActivePrinter)
sConn = " " & avTmp(UBound(avTmp) - 1) & " "
'Get Printers
lSize = 1024
sBuffer = Space(lSize)
lRet = GetProfileString("devices", vbNullString, vbNullString, _
sBuffer, lSize)
sBuffer = Left(sBuffer, lRet)
avTmp = Split(sBuffer, Chr(0))

ReDim Preserve avTmp(UBound(avTmp) - 1)
For n = 0 To UBound(avTmp)
lSize = 128
sBuffer = Space(lSize)
lRet = GetProfileString("devices", avTmp(n), vbNullString, _
sBuffer, lSize)
sPort = Mid(sBuffer, InStr(sBuffer, ",") + 1, _
lRet - InStr(sBuffer, ","))
avTmp(n) = avTmp(n) & sConn & sPort
Next
PrinterList = avTmp
End Function




================================

This posting by Jim Rech may be useful as well - certainly simpler:

From: "Jim Rech" <[email protected]>
Subject: Re: Setting active printers will Excel 97 VBA
Date: Thu, 19 Oct 2000 14:04:56 -0400
Lines: 9
Newsgroups: microsoft.public.excel.programming

This macro enumerates printers and their connections. Parsing it you may be
able to construct the syntax ActivePrinter wants:

Sub a()
Set WshNetwork = CreateObject("WScript.Network")
Set oDrives = WshNetwork.EnumNetworkDrives
Set oPrinters = WshNetwork.EnumPrinterConnections
For i = 0 To oPrinters.Count - 1 Step 2
Debug.Print "Port " & oPrinters.Item(i) & " = " & _
oPrinters.Item(i + 1)
Next
End Sub


--
Jim Rech
Excel MVP
--------------------------


Code posted by Steven Kelder:

Option Explicit
Public WshNetwork As Object
Public oPrinters As Variant
Public oDrives As Variant

Sub ShowConnections()
Dim I As Integer
Set WshNetwork = CreateObject("WScript.Network")
Set oDrives = WshNetwork.EnumNetworkDrives
Set oPrinters = WshNetwork.EnumPrinterConnections

MsgBox "Printers:"
For I = 0 To oPrinters.Count - 1 Step 2
MsgBox "Port " & oPrinters.Item(I) & " = " & oPrinters.Item(I + 1)
Next

MsgBox "Drives:"
For I = 0 To oDrives.Count - 1 Step 2
MsgBox "Drive " & oDrives.Item(I) & " = " & oDrives.Item(I + 1)
Next

End Sub



In Windows NT:
when setting ActivePrinter, you have to
lookup in the registry (
HKEY_CURRENT_USER\Software\Microsoft\Windows NT\CurrentVersion\PrinterPorts
) to get the "port name" that Excel
expects (which has nothing to do with the actual port name of the
printer!).

--
Regards,
Tom Ogilvy

Tom, this is one of those instances where shameless self-promotion is in
direct conflict with brutal honesty. Somebody else sent me this code, which
came from a long line of somebody-elses who needed this particular function,
so I don't know the original author. I am a rank beginner with this VB
stuff, but I'm willing to experiment to make my spreadsheets sit up and
behave.

I'm making a few modifications to it right now. I'm trying to figure out
how to put a printer select function and a number of copies box into the
dialog. I can do it with separate dialog boxes, but would like to put these
functions all together in one box. Any ideas?