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
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