VBA Express : Excel - Print Manager on a Userform

Excel

Print Manager on a Userform

Ease of Use

Intermediate

Version tested with

2002

Submitted by:

Jacob Hilderbrand

Description:

The UserForm will list all worksheets in the active workbook. Then the user can select any of those worksheets to be printed.

Discussion:

You have many worksheets in a workbook. Some may even be hidden. This will allow you to easily view all the worksheets in a listbox and print any of those as needed.

Code:

instructions for use

			<<<In a User Form>>> 
 
Option Explicit 
 
Private Sub CommandButton1_Click() 
     
    Dim Lst             As String 
    Dim x               As Long 
     
    For x = 0 To ListBox2.ListCount - 1 
        Lst = ListBox2.List(x) 
        Select Case Sheets(Lst).Visible 
             
        Case Is = xlSheetVisible 
            Sheets(Lst).PrintOut 
        Case Is = xlSheetHidden 
            Sheets(Lst).Visible = True 
            Sheets(Lst).PrintOut 
            Sheets(Lst).Visible = xlSheetHidden 
        Case Is = xlSheetVeryHidden 
            Sheets(Lst).Visible = True 
            Sheets(Lst).PrintOut 
            Sheets(Lst).Visible = xlSheetVeryHidden 
        End Select 
         
    Next 
     
    Unload Me 
     
End Sub 
 
Private Sub CommandButton2_Click() 
     
    Unload Me 
     
End Sub 
 
Private Sub CommandButton3_Click() 
     
    Application.Dialogs(xlDialogPrinterSetup).Show 
     
End Sub 
 
Private Sub ListBox2_BeforeDragOver(ByVal Cancel As MSForms.ReturnBoolean, _ 
    ByVal Data As MSForms.DataObject, _ 
    ByVal x As Single, _ 
    ByVal Y As Single, _ 
    ByVal DragState As Long, _ 
    ByVal Effect As MSForms.ReturnEffect, _ 
    ByVal Shift As Integer) 
     
    Cancel = True 
    Effect = 1 
     
End Sub 
 
Private Sub ListBox2_BeforeDropOrPaste(ByVal Cancel As MSForms.ReturnBoolean, _ 
    ByVal Action As Long, _ 
    ByVal Data As MSForms.DataObject, _ 
    ByVal x As Single, _ 
    ByVal Y As Single, _ 
    ByVal Effect As MSForms.ReturnEffect, _ 
    ByVal Shift As Integer) 
     
    Cancel = True 
    Effect = 1 
    ListBox2.AddItem Data.GetText 
     
End Sub 
 
Private Sub ListBox1_MouseMove(ByVal Button As Integer, _ 
    ByVal Shift As Integer, _ 
    ByVal x As Single, _ 
    ByVal Y As Single) 
     
    Dim MyDataObject    As DataObject 
    Dim Effect          As Integer 
     
    If Button = 1 Then 
        Set MyDataObject = New DataObject 
        MyDataObject.SetText ListBox1.Value 
        Effect = MyDataObject.StartDrag 
    End If 
     
End Sub 
 
Private Sub UserForm_Initialize() 
     
    Dim WS              As Worksheet 
     
    For Each WS In ActiveWorkbook.Worksheets 
        ListBox1.AddItem WS.Name 
    Next 
     
End Sub 
 
<In Thisworkbook> 
 
Option Explicit 
 
Private Sub Workbook_BeforePrint(Cancel As Boolean) 
     
    Application.EnableEvents = False 
     
    Cancel = True 
    UserForm1.Show 
     
    Application.EnableEvents = True 
     
End Sub

How to use:

Copy the code above.
Open Excel.
Hit Alt+F11 to open the Visual Basic Editor.
Insert a User Form.
Add three comman buttons and two list boxes to the User Form from the Control Toolbox.
Paste the above code marked <<<In a User Form>>> in the User Form code section.
Paste the above code marked <In Thisworkbook> in the Thisworkbook code section.
Close the VBE (Alt + Q or press the X in the top-right corner).

Test the code:

Follow the instructions in the "How to use" section.
Print.

Sample File:

Print User Form.zip 15.49KB

Approved by mdmackillop

This entry has been viewed 487 times.