Selecting specific sheets to print

[The NZ Roar]

Hi I have the following code that creates a dialogue box with check box's of sheets in the workbook skips sheets that are hidden or have no data.
Can someone help me in modifying the data so that it brings up a dialogue box of specific sheets in the workbook as some of the sheets I don't want to hide but don't want to print either.
For instance sheets 12 through to 21.

Sub SelectSheets()
Dim i As Integer
Dim TopPos As Integer
Dim SheetCount As Integer
Dim PrintDlg As DialogSheet
Dim CurrentSheet As Worksheet
Dim cb As CheckBox
Application.ScreenUpdating = False
' Add a permenant dialog sheet
Set CurrentSheet = ActiveSheet
Set PrintDlg = ActiveWorkbook.DialogSheets.Add
SheetCount = 0
' Add the checkboxes
TopPos = 40
For i = 1 To ActiveWorkbook.Worksheets.Count
' Skip empty sheets and hidden sheets
Set CurrentSheet = ActiveWorkbook.Worksheets(i)
If Application.CountA(CurrentSheet.Cells) <> 0 And _
CurrentSheet.Visible Then
SheetCount = SheetCount + 1
PrintDlg.CheckBoxes.Add 78, TopPos, 150, 16.5
PrintDlg.CheckBoxes(SheetCount).Text = _
CurrentSheet.Name
TopPos = TopPos + 13
End If
Next i
' Move the OK and Cancel buttons
PrintDlg.Buttons.Left = 245
' Set dialog height, width, and caption
With PrintDlg.DialogFrame
.Height = Application.Max _
(68, PrintDlg.DialogFrame.Top + TopPos - 34)
.width = 235
.Caption = "Select sheets to print"
End With
' Change tab order of OK and Cancel buttons
' so the 1st option button will have the focus
PrintDlg.Buttons("Button 2").BringToFront
PrintDlg.Buttons("Button 3").BringToFront
' Display the dialog box
CurrentSheet.Activate
Application.ScreenUpdating = True
If SheetCount <> 0 Then
If PrintDlg.Show Then
For Each cb In PrintDlg.CheckBoxes
If cb.Value = xlOn Then
Worksheets(cb.Caption).Select Replace:=False
End If
Next cb
ActiveWindow.SelectedSheets.PrintOut copies:=1
ActiveSheet.Select
End If
Else
MsgBox "All worksheets are empty."
End If
' Delete temporary dialog sheet (without a warning)
Application.DisplayAlerts = False
PrintDlg.Delete
' Reactivate original sheet
Sheet1.Activate
Sheet1.Select
End Sub

[Bob Phillips]

That code looke very familiar.

Bur surely, you just check the ones you want to print?

[figment]

i just ran the code, it only prints the check sheets, so i am not sure what modifications you want.

[The NZ Roar]

It brings up every sheet in the workbook right?
I'd like it to only bring up a certain selection of sheets, not every sheet.

[figment]

as the code is writen it ignores any hidden or empty sheets. also remember if you dont check the box next to the sheet name, then it dosn't print. I realize that can leave a lot of sheets in the list, and a lot of boxes to check. so what other requirments would you like it to check befor adding a sheet to the list.

[Bob Phillips]

If you only want it to bring up certain sheets, you are using the wrong code. That code is written specifically to bring up every sheet that does/does not have a certain property (not hidden and not empty here) and allow a user selectable second criteria (the checked boxes).

Ther is ABSOLUTELY no point in filtering the sheets earlier and providing checkboxes when you expect the user to check every one.

How do you decide which are to be included and which are not?

[The NZ Roar]

as the code is writen it ignores any hidden or empty sheets. also remember if you dont check the box next to the sheet name, then it dosn't print. I realize that can leave a lot of sheets in the list, and a lot of boxes to check. so what other requirments would you like it to check befor adding a sheet to the list.

Thanks figment,
So I've hidden all the sheets that had formulas on them (don't ask why they're not all on one sheet......)
The other sheets that I don't want to come up in the check box the check are the raw data input sheets. The data from these sheets then go into a formated report and each page of the report has a seperate sheet.
These sheets that I don't want to print have colour on them if that counts? Are you able to filter out sheets with colour?

[Reafidy]

Not to through a spanner in the works but I think I have already done what you are trying to do.

See attachment or the code below:

Userform to select from visible sheets for printing or print preview.
Allows the user to change printers/print settings etc.
Select/Deselect all checkbox.


Option Explicit
Option Base 1
Private Sub CbxSelectAll_Click()
Dim iLoop As Integer
For iLoop = 1 To ListBox1.ListCount
Me.ListBox1.Selected(iLoop - 1) = Me.CbxSelectAll.Value
Next
End Sub
Private Sub CbnCancel_Click()
Unload Me
End Sub
Private Sub CbnPrint_Click()
Dim iLoop As Integer, iCnt As Integer
Dim vArray() As Variant
On Error GoTo Errhandler
Application.ScreenUpdating = False
For iLoop = 1 To Me.ListBox1.ListCount
If Me.ListBox1.Selected(iLoop - 1) = True Then
iCnt = iCnt + 1
ReDim Preserve vArray(iCnt)
vArray(iCnt) = Worksheets(ListBox1.List(iLoop - 1)).Name
End If
Next
Worksheets(vArray).Select
Me.Hide
Application.ScreenUpdating = True
Application.Dialogs(xlDialogPrint).Show
Me.Show
Errhandler:
Application.ScreenUpdating = True
Unload Me
End Sub
Private Sub UserForm_Initialize()
Dim wsLoop As Worksheet
For Each wsLoop In ThisWorkbook.Worksheets
If wsLoop.Visible = True Then
ListBox1.AddItem wsLoop.Name
End If
Next wsLoop
Me.CbxSelectAll.Value = True
End Sub
Private Sub UserForm_Terminate()
'You need to have a sheet here so the sheets will be ungrouped
Worksheets("Sheet1").Select
End Sub


Oh I also see you only want to show shts containing data so change this line:
If wsLoop.Visible = True Then

If wsLoop.Visible = True And WorksheetFunction.CountA(wsLoop.Cells) > 0 Then

[The NZ Roar]

I fixed the problem myself by coding in to skip certain sheets as per below:

Option Explicit
Sub SelectSheets()
Dim i As Integer
Dim TopPos As Integer
Dim SheetCount As Integer
Dim PrintDlg As DialogSheet
Dim CurrentSheet As Worksheet
Dim cb As CheckBox
Application.ScreenUpdating = False
' Add a temporary dialog sheet
Set CurrentSheet = ActiveSheet

Set PrintDlg = ActiveWorkbook.DialogSheets.Add
SheetCount = 0
' Add the checkboxes
TopPos = 40
For i = 1 To ActiveWorkbook.Worksheets.Count
' Skip empty sheets and hidden sheets
Set CurrentSheet = ActiveWorkbook.Worksheets(i)
If Application.CountA(CurrentSheet.Cells) <> 0 And _
CurrentSheet.Visible Then
' Skip other sheets that arn't needed
If CurrentSheet.Name = "FARM PACKAGE CALCULATION SHEET" Or _
CurrentSheet.Name = "Cover" Or _
CurrentSheet.Name = "Sheet4" Or _
CurrentSheet.Name = "Sheet5" Or _
CurrentSheet.Name = "report" Or _
CurrentSheet.Name = "Sheet1" _
Then
' Otherwise display other sheets
Else
SheetCount = SheetCount + 1
PrintDlg.CheckBoxes.Add 78, TopPos, 150, 16.5
PrintDlg.CheckBoxes(SheetCount).Text = _
CurrentSheet.Name
TopPos = TopPos + 13
End If
End If
Next i
' Move the OK and Cancel buttons
PrintDlg.Buttons.Left = 245
' Set dialog height, width, and caption
With PrintDlg.DialogFrame
.Height = Application.Max _
(68, PrintDlg.DialogFrame.Top + TopPos - 34)
.width = 235
.Caption = "Select sheets to print"
End With
' Change tab order of OK and Cancel buttons
' so the 1st option button will have the focus
PrintDlg.Buttons("Button 2").BringToFront
PrintDlg.Buttons("Button 3").BringToFront

' Display the dialog box
CurrentSheet.Activate
Application.ScreenUpdating = True
If SheetCount <> 0 Then
If PrintDlg.Show Then
For Each cb In PrintDlg.CheckBoxes
If cb.Value = xlOn Then
Worksheets(cb.Caption).Select Replace:=False
End If
Next cb
ActiveWindow.SelectedSheets.PrintOut Copies:=1
ActiveSheet.Select
End If
Else
MsgBox "All worksheets are empty."
End If
' Delete temporary dialog sheet (without a warning)
Application.DisplayAlerts = False
PrintDlg.Delete
' Reactivate original sheet
Sheet1.Activate
Sheet1.Select
End Sub

[The NZ Roar]

Now for the next stage....
Is anyone able to modify the code so that using the same check boxes it will create a table of contents with page numbers?

[Bob Phillips]

I still don't get this. Why are you bothering with checkboxes?

[lucas]

The kb is full of code to create a table of contents:

http://vbaexpress.com/kb/getarticle.php?kb_id=16

http://vbaexpress.com/kb/getarticle.php?kb_id=120

http://vbaexpress.com/kb/getarticle.php?kb_id=831

[The NZ Roar]

Hi Lucas, thanks for that.
What I'm trying to do is actually insert a code after this code
CurrentSheet.Activate
Application.ScreenUpdating = True
If SheetCount <> 0 Then
If PrintDlg.Show Then
For Each cb In PrintDlg.CheckBoxes
If cb.Value = xlOn Then
Worksheets(cb.Caption).Select Replace:=False
End If
Next cb
That creates a table of contents in the TOC sheet tab of those selected in the check boxes, thus creating a new TOC page each time. It would need to be a temp sheet that deletes itself once printed. Because it will be printed out, it's not hyperlinks I'm looking for but page numbers.

Cheers