Select Sheets Dialog Sheet with added features

[Thwarted]

Hi
Had this in the wrong forum originally - sorry!

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]

Hi,

Clear All button. What does that mean to you? At lease ONE sheet must be active. Which sheet do you want active?

Here's how to solve the problem.

1. Pre-Select whatever sheets.

2. turn on your macro recorder.

3. Select the sheet you want active.

4. turn off the macro recorder.

observe your recorded code and incorporate. Post back with your recorded code if you need help customizing.

Skip,

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

 

[Thwarted]

Thanks Skip

I am ok activating the one sheet
Set CurrentSheet = Sheets("P2 - Summary")
CurrentSheet.Activate

I just dont know how to add a button or checkbox or something that the user can click to undo this:

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


They want both - for the form to pre-select the most commonly used sheets but in the off chance they dont want those sheets - they want to be able to click and have all checkboxes =xlOff. If one must remain checked the "P2 - Summary" is the one.

Sorry - this was a long post.

 

[SkipVought]

I would drive the sheet selection process from a LIST on a sheet, rather than have the sheet names hard-coded.

You can generate the list from code as well...

dim ws as worksheet, bHasSheet as boolean, lRow as long
bHasSheet = false
for each ws in worksheets
  if ws.name = "MyFactors" then bHasSheet = true
next
if not bHasSheet then
  with worksheets.add
    .name = "MyFactors"
  end with
end if
with Sheets("MyFactors")
  .cells(1,1).value = "ShtName"
  .cells(1,2).value = "DefaultSel"
  .cells(1,3).value = "ClrAllSel"
  lRow = .[A1].currentregion.rows.count + 1
  for each ws in worksheets
    .cells(lRow, 1).value = ws.name
    lRow = lRow + 1
  next
end with

You can use the autofilter to filter on the default or clr and then loop thru the shtname using the SpecialCells(xlCellTypeVisible)...

dim r as range
with worksheets("MyFactors")
  for each r in range(.[a2], .[a2].end(xldown)).specialcells(xlCellTypeVisible)

For Each cb In PrintDlg.CheckBoxes
  
  if cb.Text = r.value then 
    cb.Value = xlOn
  else
    cb.Value = xlOff
  end if
  Next cb


  next
end with

Skip,

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

 

[Thwarted]

Thanks Skip

I will give it a shot!