Print Word document based on Excel dialog selections

K

Kevin R

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top