Home |
Search |
Today's Posts |
|
#1
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
I have this bit of code below that creates a dialog box listing all the sheet
names to allow the user to make multiple selections and then print the reports. This works great but what I need to do now is be able to count how many items have been selected by the user and then have the code print multiple copies of one specific Word document (located on the network) based on the number of selections made. For example, if the user selects 5 reports to print, then I need this Word document to print 5 times also. I don't really care if 1 copy of the Word document prints after each report or if all 5 copies print after the 5 reports print. Thanks so much! Sub PrintSheets() 'Display Print dialog box, allows user to select reports to print by rep name Const nPerColumn As Long = 35 'number of items per column Const nWidth As Long = 7 'width of each letter Const nHeight As Long = 14 'height of each row Const sID As String = "___SheetPrint" 'name of dialog sheet Const kCaption As String = "Select Reports To Print" 'dialog caption Dim i As Long Dim TopPos As Long Dim iBooks As Long Dim cCols As Long Dim cLetters As Long Dim cMaxLetters As Long Dim iLeft As Long Dim thisDlg As DialogSheet Dim CurrentSheet As Worksheet Dim cb As DropDown Application.ScreenUpdating = False If ActiveWorkbook.ProtectStructure Then MsgBox "Workbook is protected.", vbCritical Exit Sub End If On Error Resume Next Application.DisplayAlerts = False ActiveWorkbook.DialogSheets(sID).Delete Application.DisplayAlerts = True On Error GoTo 0 Set CurrentSheet = ActiveSheet Set thisDlg = ActiveWorkbook.DialogSheets.Add With thisDlg .Name = sID .Visible = xlSheetHidden iBooks = 0 .ListBoxes.Add 78, 40, 148, 120 .ListBoxes(1).Name = sID .ListBoxes(sID).MultiSelect = xlExtended For i = 1 To ActiveWorkbook.Worksheets.Count Set CurrentSheet = ActiveWorkbook.Worksheets(i) cLetters = Len(CurrentSheet.Name) If cLetters cMaxLetters Then cMaxLetters = cLetters End If iBooks = iBooks + 1 .ListBoxes(sID).AddItem _ ActiveWorkbook.Worksheets(iBooks).Name TopPos = TopPos + 13 Next i .Buttons.Left = 245 CurrentSheet.Activate With .DialogFrame .Height = 150 .Width = 250 .Caption = kCaption End With .Buttons("Button 2").BringToFront .Buttons("Button 3").BringToFront Application.ScreenUpdating = True If .Show Then For i = 1 To thisDlg.ListBoxes(1).ListCount If .ListBoxes(sID).Selected(i) Then ActiveWorkbook.Worksheets(.ListBoxes(sID).List(i)) .PrintOut End If Next i Else MsgBox "You have cancelled this action. No reports will be printed.", vbOKOnly, "Cancel Print" End If Application.DisplayAlerts = False .Delete End With End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
transfer data to fill a word document to print (automatically) | Excel Discussion (Misc queries) | |||
2 questions: Using solver in Macro AND converting to Word document to print A3 | Excel Discussion (Misc queries) | |||
Automating Word - Print one page not entire document! | Excel Programming | |||
VBA to print Word Document | Excel Programming | |||
Print merge an Excel document with three worksheets to WORD Docume | Excel Programming |