PDA

View Full Version : Sleeper: Print VBA Userfrom for a Excel Workbook???



mini12
08-12-2005, 04:25 AM
Using a Excel Vba Userform...

Is there a way using a combo box that i can choose any active printers and network printers. After that be able to choose from two option buttons, one to print all autofilter sheets using this macro: and...



Sub Printallsheets()
ActiveSheet.Unprotect ("Password")
Selection.AutoFilter Field:=1, Criteria1:="2"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Selection.AutoFilter Field:=1, Criteria1:="3"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Selection.AutoFilter Field:=1, Criteria1:="4"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Selection.AutoFilter Field:=1, Criteria1:="5"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Selection.AutoFilter Field:=1, Criteria1:="6"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Selection.AutoFilter Field:=1, Criteria1:="8"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Selection.AutoFilter Field:=1, Criteria1:="9"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Selection.AutoFilter Field:=1, Criteria1:="10"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Selection.AutoFilter Field:=1, Criteria1:="40"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Selection.AutoFilter Field:=1, Criteria1:="50"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Sheets("Day Handover").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Sheets("Activities Pick Sheet").Select
Selection.AutoFilter Field:=1
ActiveSheet.Protect "Password", DrawingObjects:=True, Contents:=True, Scenarios:=True, _
AllowInsertingRows:=True, AllowDeletingRows:=True, AllowSorting:=True, AllowFiltering:=True
End Sub

Two, select 'multi' autofilter sheets from this macro (except Activities Handover Sheet) from a listbox and print. The listbox must have two headings, department and BA (Business Area) and each auto filter sheet except Activities Handover Sheet has a BA Information from a worksheet called, 'Print Userform Information' that will come up in that listbox. For example, autofilter sheet 1 is BA 1, Sofas, Sofa Beds and Chairs so that will come as that in the listbox.

Also when either option button is selected the 'Activities Handover Sheet' will be printed first without any decision made. This means it can't be displayed in the listbox.

Also if the 'all sheets' option button is selected, can the 'Selected Sheets' option button and its listbox be deactivated and vice versa. So it doesn't cause any confusion.

I have enclosed a example attachment, with the print userfrom in it and the print userform listbox information worksheet.

Thanks to anyone that helps me

Mini12

Jacob Hilderbrand
08-12-2005, 07:37 AM
You can select a printer with this:



Application.Dialogs(xlDialogPrinterSetup).Show