Defining action on "cancel" when using DialogSheets

[borting]

Hi,

I was playing around with the macro i found on this post, with the code also pasted below. It creates a temporary DialogSheet to select sheets for printing. I was working on adding additional options (like formatting, exporting, etc) for the selected sheets.

The problem I see is that when the user hits the cancel button or esc key in the dialog, I think the macro continues running through the end, probably so that it will still delete the temporary DialogSheet. This is a moot issue with the printing, because it looks like it just selects no sheets, and therefore prints no sheets.

Am I right? If so, whatever other code is added after the dialog results may still continue running, causing problems. So is there a way to define the action on cancel? I think this should be a "GoTo" for a subsequent section of code so you could continue with deleting the temporary DialogSheet but skip over any other parts.


Thanks!
Andrew

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
    Dim cnt As Integer
    Application.ScreenUpdating = True
    If SheetCount <> 0 Then
        If PrintDlg.Show Then
            For Each cb In PrintDlg.CheckBoxes
                If cb.Value = xlOn Then
                    If cnt = 0 Then
                    Worksheets(cb.Caption).Select ' Replace:=False 'Activate
                Else
                    Worksheets(cb.Caption).Select Replace:=False 'Activate
                End If
                    cnt = cnt + 1
                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

 

[borgem]

You can use GoTo x, where x is replaced by a specific line number where you want the code to pick back up. This kb link explains it in greater detail.

 

[borting]

Thanks...

With your reply, I guess my specific question now is how (and where) to incorporate the GoTo... I understand On Error, but does hitting cancel constitute an error? Is there another trigger specific to hitting cancel or escape?


-A

 

[borgem]

I believe this is where they hit cancel, correct?

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<code style="white-space:nowrap"><code>

</code></code>

Is there omitted code that you have after "Then". How I handled a similar situation was that I set the initial value to 0 and then if the value was still zero after input was requested, "exit sub" was executed...I would suggest maybe something like this?

Numcop = Application.InputBox("Enter number of copies to print:", _
    How Many Copies?",0,Type:=1)
If Numcop = 0 Then Goto ____
ElseIf Len(Numcop) > 0 Then
End If<code style="white-space:nowrap"><code>

</code></code><code style="white-space:nowrap"><code>
</code></code>

 

[borting]

Ah.... I understand your concept and how that works in the "Numcop" section, but that is a separate dialog box.


The one i was questioning is called PrintDlg:

<code style="white-space: nowrap;"><code> </code></code>

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

The dialog is built with OK and cancel buttons, but unlike a MsgBox, I can't figure out how to tell it what to do upon cancel.

Maybe if we used your other idea and counted how many checkboxes were checked (xlOn), if that result is zero we could use GoTo?

 

[borgem]

That sounds like it should work, although I am not all too familiar with dialog boxes myself. This page talking about making the cancel button cause an error looks like it should apply here.

http://www.developerfusion.com/article/11/common-dialog-control/3/

or maybe this one.

The first link is a bit more in depth. I hope this helps.