print sheets dialog box - with an added feature

[Thwarted]

Hi

I'm using the select sheets to print code from:

http://j-walk.com/ss/excel/tips/tip48.htm

(code pasted below)

it is working well. I have also added a bit more code to pre-select some of the sheets, however, I need to add in another option to clear all for the off chance they do not need the sheet i have pre-selected for them.

I know it isnt ideal but coding isnt my strong point - i really just needed something that worked. This did work great up until they asked for a clear all button.

Do you know if it is possible to add such a button to the existing dialog sheet code or will i have to go with a vb form instead?

Can anyone help?

Thanks!

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

' Check for protected workbook
If ActiveWorkbook.ProtectStructure Then
MsgBox "Workbook is protected.", vbCritical
Exit Sub
End If

'Hide Invoice Summary and Scorecard sheets

Sheets(Array("Master", "MasterRates", "SS Worksheet", "Dist Worksheet", _
"Option Worksheet", "Resident Contacts", "Invoice Summary", _
"Summary Page", "Timeline Worksheet", "Invoice Worksheet")).Select

ActiveWindow.SelectedSheets.Visible = False

' Add a temporary dialog sheet
Set CurrentSheet = Sheets("P2 - Summary")
CurrentSheet.Activate
Set PrintDlg = ActiveWorkbook.DialogSheets.Add

SheetCount = 0

'TopPos1 = 40
'PrintDlg.CommandButton.Add 78, TopPos1, 150, 16.5
'PrintDlg.CommandButton(1).Text = "Clear All"


' Add the checkboxes

TopPos = 65
For i = 1 To ActiveWorkbook.Worksheets.Count
Set CurrentSheet = ActiveWorkbook.Worksheets(i)
' Skip empty sheets and hidden sheets
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 = 240

' Set dialog height, width, and caption
With PrintDlg.DialogFrame
.Height = Application.Max _
(68, PrintDlg.DialogFrame.Top + TopPos - 34)
.Width = 230
.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
Set CurrentSheet = Sheets("P2 - Summary")
CurrentSheet.Activate
Application.ScreenUpdating = True

For Each cb In PrintDlg.CheckBoxes

Select Case cb.Text

Case "P1 - Cover page ", "2B Ratesheet", "P2 - Summary", "Meetings", "EPZ", "PM_Stds", "Sites", "Public", "Govt", "AreaUser", "Dist"
cb.Value = xlOn
Case Else
cb.Value = xlOff
End Select
Next cb


If SheetCount <> 0 Then

If PrintDlg.Show Then
For Each cb In PrintDlg.CheckBoxes
If cb.Value = xlOn And cb.Caption <> "Clear All" Then
Worksheets(cb.Caption).Select Replace:=False
End If
Next cb
ActiveWindow.SelectedSheets.PrintPreview
ActiveSheet.Select
End If
Else
MsgBox "All worksheets are empty."
End If




' Delete temporary dialog sheet (without a warning)
Application.DisplayAlerts = False
PrintDlg.Delete

'Show Invoice Summary and Scorecard sheets

Sheets("Master").Visible = True
Sheets("MasterRates").Visible = True
Sheets("SS Worksheet").Visible = True
Sheets("Dist Worksheet").Visible = True
Sheets("Option Worksheet").Visible = True
Sheets("Resident Contacts").Visible = True
Sheets("Invoice Summary").Visible = True
Sheets("Summary Page").Visible = True
Sheets("Timeline Worksheet").Visible = True
Sheets("Invoice Worksheet").Visible = True



End Sub

 

[SkipVought]

Please post VBA Code questions in forum707.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}