PrintOut & xlDialogPrint skipped

ChgsAllAround

New Member
Joined
Apr 11, 2008
Messages
36
I have a workbook that by default shows 1 sheet where the user makes selections from a listbox. Once the selections are made, new sheets are created for each of them in this workbook. I have created a button that should both create and print by clicking on it. The code for this is:

Code:
Sub Picture26_Click() 'print
    Worksheets.Add().Name = "Sample"
    Application.Dialogs(xlDialogPrint).Show
End Sub
This works fine until I want to add something for "after" printing. As soon as I create a sub for Workbook_BeforePrint, anything I put in it related to printing is treated as though it doesn't exist, ie, doesn't do that line, but does everything else in the sub. Here is a sample of code for that:

Code:
Private Sub Workbook_BeforePrint(Cancel As Boolean)
    Application.EnableEvents = False
    Cancel = True
    'select all visible worksheets
    For Each ws In Worksheets
        If ws.Visible = True Then ws.Select (False)
    Next
    ActiveWindow.SelectedSheets.PrintOut Copies:=1
    MsgBox ("should have printed")
    Application.EnableEvents = True
End Sub
Nothing prints. If I change the PrintOut statement to show the dialog box, I don't get the dialog box.

Why?
Thanks!
Mari
 

ChgsAllAround

New Member
Joined
Apr 11, 2008
Messages
36
Thanks, Gary.
Removing Cancel works for the actual button, but if you select File > Print, it prints, THEN brings up the print dialog box. I think I need another solution or an addition.
 

Forum statistics

Threads
1,081,929
Messages
5,362,182
Members
400,671
Latest member
Tommy00836

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top