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:

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

Test the code:

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

Sample File:

Print User Form.zip 15.49KB 

Approved by mdmackillop


This entry has been viewed 437 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2014 VBA Express