Print Dialog Box

[Fr33dan]

I have a macro that opens and prints out multiple files. I was wonder if it was possible to display a print dialog box without printing anything so I could use the user's choices on the rest of the prints.

 

[Loomah]

Hi
You omitted to mention which application you are in but this is the code you'd need in Excel

Application.Dialogs(xlDialogPrint).Show

and for Word

Application.Dialogs(wdDialogFilePrint).Display

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[Fr33dan]

Thanks for your response. While this does display the dialog it also executes a print. I would like to just get the data and use it on a later print. I'm running Excel (97 if it matters)

 

[Bong]

How are you executing the print?

_________________
Bob Rashkin

 

[Loomah]

Version doesn't matter but I've just done it on xl97 and all it does is show the print dialog box as expected.

In addition to Bong's question, where does this code sit in your routine and what happens to the dialog after you're finished with it?

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[Fr33dan]

I'm the code that is running the print is:

Sub printTaged()
    Application.ScreenUpdating = False
    StartingPos = Range("Current_Aging_File").Value
    Range("Current_Aging_File").Value = 0
    For i = 1 To Range("No_Aging_Files").Value
        
        If (Range("Put_Path").Offset(i - 1, 2).Value) Then 'if it's been tagged
            SelectFile.Next_Unit                           'open the file
            ActiveWindow.SelectedSheets.PrintOut           'and print it out
        Else: Range("Current_Aging_File").Value = _
            Range("Current_Aging_File").Value + 1
        End If
    Next i
    Range("Current_Aging_File").Value = StartingPos - 1
    SelectFile.Next_Unit
End Sub

and as a test I put Application.Dialogs(xlDialogPrint).Show in it's own sub. When I ran the sub it displayed the dialog box I picked the settings and hit print and it printed the file.

 

[Bong]

Rather than use the Print Dialog box, I suggest you build a custom interface (a form, or a table, whatever) where the user can specify those properties you think it might be useful to expose. Then you can set each of the properties in code, such as:

PaperSize Property
See AlsoApplies ToExampleSpecificsReturns or sets the size of the paper. Read/write XlPaperSize.

XlPaperSize can be one of these XlPaperSize constants.
xlPaper11x17. 11 in. x 17 in.
xlPaperA4. A4 (210 mm x 297 mm)
xlPaperA5. A5 (148 mm x 210 mm)
xlPaperB5. A5 (148 mm x 210 mm)
xlPaperDsheet. D size sheet
xlPaperEnvelope11. Envelope #11 (4-1/2 in. x 10-3/8 in.)
xlPaperEnvelope14. Envelope #14 (5 in. x 11-1/2 in.)
xlPaperEnvelopeB4. Envelope B4 (250 mm x 353 mm)
xlPaperEnvelopeB6. Envelope B6 (176 mm x 125 mm)
xlPaperEnvelopeC4. Envelope C4 (229 mm x 324 mm)
xlPaperEnvelopeC6. Envelope C6 (114 mm x 162 mm)
xlPaperEnvelopeDL. Envelope DL (110 mm x 220 mm)
xlPaperEnvelopeMonarch. Envelope Monarch (3-7/8 in. x 7-1/2 in.)
xlPaperEsheet. E size sheet
xlPaperFanfoldLegalGerman. German Legal Fanfold (8-1/2 in. x 13 in.)
xlPaperFanfoldUS. U.S. Standard Fanfold (14-7/8 in. x 11 in.)
xlPaperLedger. Ledger (17 in. x 11 in.)
xlPaperLetter. Letter (8-1/2 in. x 11 in.)
xlPaperNote. Note (8-1/2 in. x 11 in.)
xlPaperStatement. Statement (5-1/2 in. x 8-1/2 in.)
xlPaperUser. User-defined
xlPaper10x14. 10 in. x 14 in.
xlPaperA3. A3 (297 mm x 420 mm)
xlPaperA4Small. A4 Small (210 mm x 297 mm)
xlPaperB4. B4 (250 mm x 354 mm)
xlPaperCsheet. C size sheet
xlPaperEnvelope10. Envelope #10 (4-1/8 in. x 9-1/2 in.)
xlPaperEnvelope12. Envelope #12 (4-1/2 in. x 11 in.)
xlPaperEnvelope9. Envelope #9 (3-7/8 in. x 8-7/8 in.)
xlPaperEnvelopeB5. Envelope B5 (176 mm x 250 mm)
xlPaperEnvelopeC3. Envelope C3 (324 mm x 458 mm)
xlPaperEnvelopeC5. Envelope C5 (162 mm x 229 mm)
xlPaperEnvelopeC65. Envelope C65 (114 mm x 229 mm)
xlPaperEnvelopeItaly. Envelope (110 mm x 230 mm)
xlPaperEnvelopePersonal. Envelope (3-5/8 in. x 6-1/2 in.)
xlPaperExecutive. Executive (7-1/2 in. x 10-1/2 in.)
xlPaperFanfoldStdGerman. German Legal Fanfold (8-1/2 in. x 13 in.)
xlPaperFolio. Folio (8-1/2 in. x 13 in.)
xlPaperLegal. Legal (8-1/2 in. x 14 in.)
xlPaperLetterSmall. Letter Small (8-1/2 in. x 11 in.)
xlPaperQuarto. Quarto (215 mm x 275 mm)
xlPaperTabloid. Tabloid (11 in. x 17 in.)
Note Some printers may not support all of these paper sizes.


expression.PaperSize
expression Required. An expression that returns one of the objects in the Applies To list.

Example
This example sets the paper size to legal for Sheet1.

Worksheets("Sheet1").PageSetup.PaperSize = xlPaperLegal

_________________
Bob Rashkin

 

[Fr33dan]

Thats an interesting idea but I wouldn't know how to get a list of the printers (which is my primary concern). I did however discover that if you use the print dialog and then to a .Printout without any arguments it uses the settings from the last print. So I just made it open the dialog the first time through the loop and then used printout on all the other runs through:

Sub printTaged()
    Application.ScreenUpdating = False
    first = True
    StartingPos = Range("Current_Aging_File").Value
    Range("Current_Aging_File").Value = 0
    For i = 1 To Range("No_Aging_Files").Value
        
        If (Range("Put_Path").Offset(i - 1, 2).Value) Then
            SelectFile.Next_Unit
            If (first) Then
                Application.Dialogs(xlDialogPrint).Show
                first = False
            Else
                ActiveWindow.SelectedSheets.PrintOut
            End If
        Else: Range("Current_Aging_File").Value = Range("Current_Aging_File").Value + 1
        End If
    Next i
    Range("Current_Aging_File").Value = StartingPos - 1
    SelectFile.Next_Unit
End Sub

Thanks for your help

 

[Loomah]

just as a little addition - if you're looking for how VBA 'reads' the printer names you can use the macro recorder and record yourself changing the printer from the print dialog the set your chosen printer using

application.activeprinter = "\\blahblah\printer name etc"

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[Bong]

Actually, the printer name is a parameter of the PrintOut function; no need to change the active printer. The question is, how to list all the possible printers. I suspect there's a way to do it in the shell, but I don't know how.

_________________
Bob Rashkin