Hi
I'm using the select sheets to print code from:
(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
I'm using the select sheets to print code from:
(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