Select Sheets Dialog Box 1

[majopa]

I used the code from

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

and incorporated a couple of modifications that I also found online. All of this has left me with an issue that I am sure requires only a simple fix, but that I am unable to correct. The problem is the last worksheet prints whether or not it is selected. I have made several attempts to correct it, but I am not experienced enough determine the solution. In addition to this fix, I was also hoping to add to the selection dialog box the ability to 'Select All'. This is a nice to have and not critical. My main concern is printing sheets unnecessarily. Any help is greatly appreciated. Thank you.

Private Sub CommandButton1_Click()

' Display "Printer Setup" dialog box
Application.Dialogs(xlDialogPrinterSetup).Show

' 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
Dim Numcop As Long
Application.ScreenUpdating = False

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

' Add a temporary dialog sheet
Set CurrentSheet = ActiveSheet
x = CurrentSheet.Name
Set PrintDlg = ActiveWorkbook.DialogSheets.Add
SheetCount = 0

' Add the checkboxes
TopPos = 40
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

' Get the number of print copies for each report
Numcop = Application.InputBox("Enter number of copies to print:", _
"How Many Copies?", 1, Type:=1)
If Numcop = 0 Then
ElseIf Len(Numcop) > 0 Then
End If

' 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 'Activate
End If
Next cb
ActiveWindow.SelectedSheets.PrintOut copies:=Numcop
'ActiveSheet.PrintPreview 'for debugging
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
Sheets(x).Select

End Sub

 

[SkipVought]

Hi,

comment out this statement...

'   Display the dialog box[b]
'    CurrentSheet.Activate[/b]
    Application.ScreenUpdating = True
    If SheetCount <> 0 Then
        If PrintDlg.Show Then
...

Skip,

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

 

[majopa]

Thank you Skip. Although this prevented the last sheet from being printed, it was replaced with another sheet. It also shows a 'Form' design page worksheet that shows the 'Select sheets to print' dialog box - this is the extra sheet that is printed. Any thoughts?
Thanks again.

 

[SkipVought]

The way this is coded, WHATEVER sheet has been previously selected, will be included in the list. The loop APPENDS to selected sheets.

Skip,

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

 

[majopa]

Before I commented this line out the last worksheet, which I never selected, printed with the selected sheets. Now, although the last worksheet no longer prints out. Instead, a new 'Dialog' with a number (e.g. 'Dialog 18') worksheet prints out. This worksheet is nothing more than a design worksheet of the dialog box. This does not happen when the line is uncommented out. Any thoughts? Are there other corrections that can be made? Again, I am in no way a programmer. This is a culmination of code I found on the internet.
Thank you again.

 

[SkipVought]

Try this replacement block of code. Highlighted showing where I changed stuff.

What is does is declare a cnt counter that incriments each time a box has been checked. When the cnt is ZERO, it JUST SELECTS the sheet, the FIRST sheet to be selected. For each one following , it APPENDS the selection.

'   Display the dialog box
'    CurrentSheet.Activate  [b]
    Dim cnt As Integer      '[/b]
    
    Application.ScreenUpdating = True
    If SheetCount <> 0 Then
        If PrintDlg.Show Then
            For Each cb In PrintDlg.CheckBoxes
                If cb.Value = xlOn Then '[b]
                    If cnt = 0 Then
                        Worksheets(cb.Caption).Select
                    Else
                        Worksheets(cb.Caption).Select Replace:=False 'Activate
                    End If
                    cnt = cnt + 1       '[/b]
                End If
            Next cb
            ActiveWindow.SelectedSheets.PrintOut copies:=Numcop
            'ActiveSheet.PrintPreview 'for debugging
        End If
    Else
        MsgBox "All worksheets are empty."
    End If

Skip,

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

 

[majopa]

Skip - That is much better. The only other issue that remains is when you are selecting the sheets to print is the sheet with the design of the dialog box is showing. Is there anyway to remove that as it may be confusing to the user. The other 'nice to have' would be an option to 'Select All' in the dialog box. If not of that is possible, that is fine.

I really appreciate your help and your quick responses.

 

[SkipVought]

You can really uncomment that statement that I previously had commented out. It does not matter to the selection logic, that the CurrentSheet is active or not.

Skip,

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

 

[majopa]

Skip - That is perfect.

Any way to add a 'Select All'?

Thanks

 

[SkipVought]

I'd put an OptionButton to select all or not. Manually add a dialog sheet and add two option buttons. Macro record to see what code is generated. It can be coded similar to the adding of CheckBoxes, except you'll have just 2.

IF the select all, then just print the workbook. You can turn on your macro recorder and record printing the workbook to see what code is generated.

Post back with your recorded code if you need help.

Skip,

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

 

[majopa]

Skip - You have been such a huge help. Unfortunately, I am far from a programmer. I tried to do as you said and I have been unsuccessful. I am typically not the type to give up, but since I am so inexperienced, I feel I must. I thank you for your help on this and I will have to live with it the way it is.

Thanks again.

 

[SkipVought]

Please post the code that you added that is not working. You'll get some help!

Skip,

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

 

[majopa]

I couldn't even get that far so I don't have anything to post.

It is not a big deal and I am willing to live with it as is.

Thank you again.

 

[majopa]

I was hoping that someone may be able to help me add a 'Select All' button to the dialog box that allows the user to select the sheets to print.

Thank you in advance.

 

[SkipVought]

Just do a Workbook Print. Turn on your macro recorder and record printing the WORKBOOK.

Skip,

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

 

[majopa]

Thanks. Now that I have the code I have two questions. Where do I put the code and how does the user access it?

 

[SkipVought]

The code can be in any module.

I thot you wanted a button to run it?

Skip,

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

 

[majopa]

I did. I was hoping for a button on the dialog box where the user selected the sheets and pressed 'OK' or 'Cancel'.

 

[SkipVought]

What's stopping you?

Skip,

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

 

[majopa]

Lack of knowledge/experience.